Required to generate report to total sale dollar amount by combining information from T1 and T2 (matching with item_no). Rules are a)Formula is Sales_Number*Sale_price b)Choose the earliest sale record to latest by Sales_Date with the sum of Sales_Number is less or equal the Total_Sales_Number from T1. c)If the sum of Sales_Number is less than the Total_Sales_Number from T1, then the use a fixed price for all items, say $10 d)If a item is not in T2 then use $10 for the price
For example, item_no 1: Total sale dollar amount = 20*$15+30*$15+(60-20-30)*$10
item_no 2: Total sale dollar amount = 40*$16+(50-40)*$18 item_no 3: Total sale dollar amount = 5*$20 item_no 4: Total sale dollar amount = 20*$10
The current logic is done by using cursor. Can this be done without cursor using such as analytical statements? The Oracle DB is 9i.
I would like to run a query that counts by case_manager, number of distinct app_id's that have a status ='AC' in a select number of programs. All of the fields are in the same table.I want it to look like this:
Case MgrA DYYOY Jane13420 John3452 Alice1233
Fields are case_manager, status, applicant, and program table reg...I can do the count command to find the total of all active people for each region code. What I want it for the breakdown by program the people are in.
My query for that is: SELECT case_manager, Count (*) from reg where status='AC' Group by case_manager order by case_manager and I get this: Case ManagerCount * Jane46 John 14 Alice9
I am trying to find the space occupied on disk by the tablespaces of the database that contain tables, some (and not all) of whose columns are encrypted. My query is like this:
select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB from dba_data_files a, dba_tables b, (select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c where a.tablespace_name = b.tablespace_name and b.owner = c.owner and b.table_name = c.table_name order by a.tablespace_name;
The output of the query is as shown in the attached file:
Since the output (under the heading Total Size of the tablespace) is probably the sum of all the datafiles returned by the query and is obviously incorrect, I have not given the rest of it. I also tried the following:
select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB, sum (bytes/(1024*1024*1024))over (partition by a.tablespace_name order by file_name) "Total Size of the tablespace" from dba_data_files a, dba_tables b, (select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c where a.tablespace_name = b.tablespace_name and b.owner = c.owner and b.table_name = c.table_name order by a.tablespace_name ; [code]...
Here, the fig. under the heading "Total Size of the tablespace" are probably the sum of all the records returned by the query if distinct is not used i.e all the data file sizes returned by the query.
tune my query and get the desired results? I think this can be achieved by group by with rollup, cube, order by and grouping functions, but am not sure how to proceed. I know that I can get the results by using Enterprise Mgr. Console in 2 mins., but would still like to get the results with the queries.
I brought in the Northwind database 2003 in to SQL Developer. I was looking at the view 'Summary of Sales by Quarter' But this report brings in everything. It doesent give me summary of sales for Qtr1, Qtr4 etc
These are the columns:
ShippedDate OrderId Subtotal CREATE OR REPLACE FORCE VIEW "NORTHWIND"."SUMMARY_OF_SALES_BY_QUARTER" ("SHIPPEDDATE", "ORDERID", "SUBTOTAL") AS
[code]....
How can i recreate this view to show me summary data for qtr1, qtr2, qtr3 and qtr 4 for the year 1997??
AREA (area_code, area_name) MONTHLY_SALES (area_code, isbn, book_name, qty, sales_amount)
I want to display the output of top 3 sales columnwise as below.
AREA_NAME Top 1st (ISBN) Top 1st (BOOK_NAME) Top 1st (QTY) Top 2nd (ISBN) Top 2nd (BOOK_NAME) Top 2nd (QTY) Top 3rd (ISBN) Top 3rd (BOOK_NAME) Top 3rd (QTY)
Thing is how can I join those tables to get all the sales ID's that have at least two products one of which has got any number of messages and the other one hasn't got ANY any messages. So another words one sale entity that has both products with messages and at lease one with no message. Something like this
Sales 1 ID 123 Product1 ID 1234 for sale ID 123 Product2 ID 4321 for sale ID 123 Sales_message ID 098 for product ID 1234 'blabla' Sales_message ID 876 for product ID 1234 'albalb' AND NO MESSAGES FOR PRODUCT 4321
how to write below query in pl/sql cursor. The help table has two associated tables, help_txt and help_id, which will have strings of data concatenated into one sales contact record. There are multiple lines of text per comment and multiple lines of resolution text at 40 characters per line. The key to the help_text table (id, date,seqno) is the main key to the help_txt table and help_id t table with a sequence added to each table
The formatted string will contain some text and variables with the comment lines (1-10 or more) concatenated first, followed by the resolution lines (1-10 or more). There will be multiple comment and multiple resolution lines. The Cust_Cmnt_Txt lines and the Resolved_Desc lines should be concatenated and formatted in the following string (% marks the variable string) :
'help taken ' %help.Taken_Dte 'received from the following source: ' %help.id. 'Remark Text: ' %help_text (where help_txt_Seq = 1) %help_text (where help_text_seq = 2-10 or more) 'Resolution: ' %help_id_Res_Txt.Resolved_Desc (where help_ID_Txt_Seq = 1) %help_ID_Res_Txt.Resolved_Desc (where help_id_Txt_Seq = 2-10 or more)
Customer sales order lines are fulfilled by IR-ISO. When line progresses, it is set to have Internal Req Requested status. A scheduled program runs amd IR are created. However, sales order line status does not change to Internal Req Open. It remains in Internal Req Requested status.
As a work around, workflow is rewound to 'Check ATR Qty' which is customized activity and then status changes to Internal Req Open. What could be the reason that workflow is not setting the status to Internal Req Open?
As my requirement is that to find week for particular date according to Arabic calender.As per Arabic calender first day for year starts from saturday.
so when i execute query :
select to_char(to_date('05/26/2013','MM/DD/RRRR'),'WW') from dual;
Then it gives 21,but as per arabic calender it should show 22nd week.
getting result according to Arabic calender as it should return 22,because weak starts from saturday according to Arabic calender.
I had to get a query to give me all the Mondays of a IW week in a year. I think as per the IW weeks there are around 52.1 weeks in a year. So basically, I want the starting Monday of every week. I would then store those values in a variable and use them in APEX as column headers. Online I saw some samples not related with this, but where looping was done using the all_objects. Not sure if that is the right approach.
The problem is since this is not stored in any table, how can I loop like 52/53 times such that the output shows all the Mondays (date format) for a given year. Basically, the output would be 52/53 rows with a date(Monday of the week).
I am running 10g on Linux, and last week someone messed up a database and it needs to be restored to 27 December. I am not a DBA, out regular DBA is on vacation till mid January,
I know we have archivelog on (I can see .arc files for the database), and I also have a backup of the actual data files and control files from when the database was first created. So in other words, I have all archive files and the initial data files. What i do not know is how to set the restore till December 27.
I have a create a report for our dept with certain criteria. So far what I need is a report that shows last weeks numbers or sign ups. I can't get the date search or between to work.
What I need is one for month end and one for last 7 days.
here's what I have so far.
select ap.name
to_char(ap.opendate,'MM-DD-YY') "Open Date"
from [databasname]
where ap.opendate between databasename-7 and databasename-1
I really don't want to change the date myself I want the system to know when the 7 days or month is.
1. Year's first week starts with first Sunday of the year. (6th Jan 2013 will be the starting week (week 1) in Year 2013) 2. 2nd Jan 2013 will be the last week of the previous year i.e. 2012 (either 52th or 53rd week)
At many place I found the below solution:
select to_char(to_date('01-JAN-2008','DD-MON-YYYY')+1,'IW') week_number from dual;
But its not working for the given dates (2nd Jan 2013, which should fall in the last week of 2012, and 6th Jan 2013, which should be the starting week of 2013).
I have a table with a count of customers for about a year, but I only want to select Wednesday and Thursday of each week, starting at the beginning of dates. Table is simple and has two columns. Each row is Distinct to a Date, there are no Date duplicates, it's counted for each day of the year.
column 1: count of customers, count(customer_id) column 2: Date
Not even sure if this is possible to select a date using the day name ?
Basically I want to select each Wednesday and Thursday from each week and compare the counts week over week, over week for all week to see if the counts are going up or down, to get trends .
I hava an requirement to get week number from particular date as per indian financial year(ie apr-01 to mar-31).I have tried with to_char(the_date_field,'w') and to_char(the_date_field,'iw') formats but I know its only shows the iso standard.
But I want the weeknumber 1 has to be started from april-01 not from jan-01.For more explanation see below,
Date week_no apr 01 to 07 1 apr 08 to 14 2 apr 15 to 21 3 . . . . . Next year mar - 31 n