Home » SQL & PL/SQL » SQL & PL/SQL » find total sales for the whole last week (Oracle 10g and windows 2003)
find total sales for the whole last week [message #446291] Sun, 07 March 2010 09:49 Go to next message
aijaz786
Messages: 91
Registered: February 2010
Member
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. I will appreciate for all your cooperation.

Thanks
Re: find total sales for the whole last week [message #446292 is a reply to message #446291] Sun, 07 March 2010 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select trunc(sysdate,'IW') cur_wk, 
  2         trunc(sysdate,'IW')-7 start_prev_wk,
  3         trunc(sysdate,'IW')-1 end_prev_wk,
  4         to_char(sysdate,'IW') cur_wk,
  5         to_char(sysdate-7,'IW') prev_wk
  6  from dual
  7  /
CUR_WK     START_PREV END_PREV_W CU PR
---------- ---------- ---------- -- --
01/03/2010 22/02/2010 28/02/2010 09 08

1 row selected.

Regards
Michel

[Updated on: Sun, 07 March 2010 10:01]

Report message to a moderator

Re: find total sales for the whole last week [message #446327 is a reply to message #446291] Sun, 07 March 2010 22:22 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Thanks, Michael.

Similar to this, If I need to find total sales for the entire previous month then I am using the following query but it is not giving right results.

i.e. right March is in progress, I need to find total sales for Feb 2010. When I will be in April, I will need total sales for March 2010.

select trunc(sysdate,'MM') curr_month,
trunc(sysdate,'MM')-30 start_prev_month,
trunc(sysdate,'MM')-1 end_prev_month
from dual

Please advise.

Thanks.
Re: find total sales for the whole last week [message #446333 is a reply to message #446327] Sun, 07 March 2010 22:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
  1  select trunc(sysdate,'MM') curr_month_start,
  2  trunc(add_months(sysdate,-1),'MM') start_prev_month,
  3  trunc(sysdate,'MM')-1 end_prev_month
  4* from dual
SQL> /

CURR_MONTH_START    START_PREV_MONTH	END_PREV_MONTH
------------------- ------------------- -------------------
2010-03-01 00:00:00 2010-02-01 00:00:00 2010-02-28 00:00:00
Re: find total sales for the whole last week [message #446336 is a reply to message #446327] Sun, 07 March 2010 22:43 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Just google the 'Date function in oracle' and go through those functions.

regards,
Delna
Previous Topic: Select Query Help
Next Topic: date problem
Goto Forum:
  


Current Time: Sun Dec 04 08:24:48 CST 2016

Total time taken to generate the page: 0.07202 seconds