SQL & PL/SQL :: Find Total Sales For The Whole Last Week

Mar 7, 2010

I have two columns in table

sales_date
sales_amount

I need to find total sales for the whole last week.

Today is current week i.e. 1 (March 1, 2010 and March 7, 2010)

I need to find total_sales for the last week (i.e. Feb 22, 2010 to Feb 28, 2010)

I am unable to create logic for the same.

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Get Total Sales Dollar Amount From Two Tables Without Cursor?

Aug 28, 2013

I got this intertesting request from one of my client some time ago and is still trying to find an answer.

Getting total sales dollar amount from two tables.

T1
Item_no(number)Total_Sales_Number(number)
160
250
35
420

T2
Item_no(number)Sales_Date(date)Sales_Number(number)Sale_Price(number)
12012-01-012015
12012-02-153012
22012-01-104016
22012-03-053018
32012-02-011020

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.

View 22 Replies View Related

SQL & PL/SQL :: Print WHEN 30 Then Sales After That It Prints WHEN 50 Then Sales As Invalid

Aug 23, 2013

The below Decode query is

SELECT CASE deptno
WHEN 10 THEN 'ACCOUNTING'
WHEN 20 THEN 'RESEARCH'
WHEN 50 THEN 'SALES'
WHEN 30 THEN 'SALES'
ELSE 'Invalid'
END
FROM dept;

But my question is that, it prints WHEN 30 THEN 'SALES' after that it prints WHEN 50 THEN 'SALES' as 'Invalid'.

View 5 Replies View Related

SQL & PL/SQL :: Find Start And End Date Of Week?

Feb 5, 2011

I am creating report for this i want to make one query..

Query like this

My input is YEAR, MONTH AND WEEK and i want to find out start date and end date for this week...

Scenario like this
-----------------
I/P - Year = 2011, WEEK = 2 , Month - FEB
i will get o/p like this '06-feb-2010' and '12-feb-2010'...

View 10 Replies View Related

SQL & PL/SQL :: How To Find Cumulative Total

Jul 3, 2010

how to find cumulative total means i need total amount between april to sysdate.

View 1 Replies View Related

Find Total Of All Active People For Each Region Code

Jun 18, 2012

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

View 1 Replies View Related

SQL & PL/SQL :: Find Total Space Occupied On Disk By Tablespaces Of Database

Nov 3, 2010

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:

TABLESPACE_NAMEFILE_NAMEFILE_SIZE_IN_GB
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0044.DBF29.296875
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0045.DBF29.296875
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0051.DBF29.296875
DMS_DATAN:ORACLEORADATASPOPRODDMS_DATA_0012.DBF19.53125
[code]...

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.

View 11 Replies View Related

Reports & Discoverer :: How To Find Page Number And Total Number Of Pages

Jan 26, 2010

i am using oracle developer 6i report builder i required this type of query

example

if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;

return (TRUE);
end;

but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.

View 34 Replies View Related

Database - Summary Of Sales By Quarter?

Nov 30, 2010

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??

View 1 Replies View Related

SQL & PL/SQL :: Compare The Sales With The Same Month Last Year

Jul 6, 2012

I wanted to compare the sales with the same month last year. But the issue is only one value on each customers.

File Attached Table as JPG file..
-----------------------------------------------------------------------
SELECT WORDERS.BP_ORDER,
(SELECT SUM (WINVITEMS.ITEM_VAL)
FROM WINVITEMS
INNER JOIN WORDERS ON WORDERS.ORD_NO = WINVITEMS.ORD_NO
WHERE WORDERS.DATE_ORDER >= TRUNC(ADD_MONTHS(SYSDATE, -3),'MM')

[Code]....

View 7 Replies View Related

PL/SQL :: Display Top 3 Sales Column Wise

Dec 21, 2012

I have two tables as below

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)

View 5 Replies View Related

SQL & PL/SQL :: How To Join Tables To Get All Sales IDs That Have At Least Two Products

Nov 18, 2010

Trick is that I've got 4 tables

Sale , sale_details, sales_message, message_text

Table:Sales
Sales_id

Table: Sales_product
Sales_id, sales_product_id

Table: Sales_message
Sales_id, sales_message_id, message_text_id

Table:Message_text,
Message_text_id, message_text

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

This is what I want to get from DB.

View 3 Replies View Related

SQL & PL/SQL :: Create A Query Which Will Show Sales Of Products By Month

Jul 12, 2004

I'm trying to create a query which will show sales of products by month, something like this: -

JanFebMarApr
Prod 11020511
Prod 200510
Prod 31921015

The data is held on 3 tables: -

TB_ARTICLES
ID
CODE
DESCRIPTION

TB_TRANSACTIONS
ID
BOOKKEEPING_DATE
TRANS_DATE

[code]....

Running a query for one month is no problem at all, I just don't know how to create a cross tab style one.

View 12 Replies View Related

SQL & PL/SQL :: Cursor Implement / Strings Of Data Concatenated Into One Sales Contact Record

Sep 23, 2013

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)

View 8 Replies View Related

Workflow :: External Sales Order Lines Remain In Internal Req Requested Status

Feb 13, 2013

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?

View 1 Replies View Related

PL/SQL :: Total And Grand Total

Mar 26, 2013

I'm running a query like the below but now i would like to make the last line actually say Grand Total. Instead of just total.

SELECT   decode (grouping (farinva_invh_Code),0,null,'Total') farinva_invh_Code,
         --decode ( grouping (amt),0,null,'GrantTotal')Grant_Total,
         farinva_invh_Code,
         spriden_id,
         --spriden_last_name "last Name"

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Count Of Updates By Week

Feb 19, 2013

I have a table of records with a date_modified column. I would like to simply report the count of updates made each week. For example, given:

Record Date_Modified
155291141/23/2013
157277201/24/2013
152619321/25/2013
142263211/28/2013
140043421/28/2013
150050431/28/2013
148315761/29/2013
154364281/31/2013
148066382/1/2013

[Code]...

I would like to return:

Week Updates
43
57
69
77
84

Although in place of the week number, providing either the value for the last day of the week or the first day of the week would be just as good.

how to go about this task.

View 7 Replies View Related

SQL & PL/SQL :: Week On Particular Day According To Arabic Calendar

May 26, 2013

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.

View 6 Replies View Related

PL/SQL :: How To Get Monday Of Every IW Week In A Year

Jun 29, 2012

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).

View 10 Replies View Related

PL/SQL :: Change Monday Like First Day Of Week

Oct 18, 2012

I need to change Monday like first day of week and not Sunday.

select to_char(to_date('01-01-2012'),'w') from dual

Result: 1

select to_char(to_date('02-01-2012'),'w') from dual

Result:1

and the idea is that 02-01-2012 must be the second week

View 3 Replies View Related

To Restore Database To Last Week

Dec 31, 2012

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.

View 8 Replies View Related

Date Search By Last Week / Month?

Nov 2, 2011

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.

View 4 Replies View Related

SQL & PL/SQL :: Get Date From Year / Week And Weekday

Apr 5, 2011

How to get a date using Year (say 2009), Week (35) and Weekday (5). I have read only permission to database, so I can not create a function.

View 8 Replies View Related

SQL & PL/SQL :: Display 1st Day Of Week Within Date Range

Jul 14, 2010

How to display date every monday within date range

DEsc start date end date
XXXX 1/2/2010 31/10/2010

output
date every monday

View 25 Replies View Related

SQL & PL/SQL :: Get First Week Starting With First SUNDAY Of Year

Nov 9, 2012

I want to get the week of the year.

Conditions are:

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).

View 6 Replies View Related

PL/SQL :: Selecting Certain Day Of Week From Range Of Dates

Nov 21, 2012

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 .

View 4 Replies View Related

SQL & PL/SQL :: Change Week Startday To Tuesday

Jan 16, 2012

select trunc(to_date('1201090000', 'yymmddHH24mi'),'DAY') from dual

will give output

08-jan-2012 00:00:00

which is sunday as per AMERICA nls_territory setting.

For my requirement i want to group from tue-mon for a week instead of sun-sat. modify the query to use the week start day as Tuesday.

View 2 Replies View Related

SQL & PL/SQL :: Show Week Days Without Friday And Saturday

Oct 25, 2011

how can create function or sql statement to show week of days without Friday and Saturday

View 13 Replies View Related

SQL & PL/SQL :: Display Week Number As Per Financial Year (Apr - Mar)

Dec 9, 2012

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

how to do this am waiting for your reply.

View 4 Replies View Related

Reports & Discoverer :: Week Day Starts From Monday

Feb 20, 2012

I have m display financial year calender(April-2011 to March 2012) and i want to display monday as a first week day.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved