how to calculate sales for a week [message #322531] |
Sat, 24 May 2008 02:46  |
melvinRav
Messages: 43 Registered: April 2008 Location: India
|
Member |
|
|
Hi all
, I have a table from where i have to select the total sales for an item for a week and run it on Friday ( by scheduling it )
the table has entries by day
my table is daily_sales
has the following fields
itm_code is varchar2
date_sales - is date field
amt_sales - is number field
my week calculations starts from Saturday to Thursday (both inclusive )
how to select the date period for the week
For example
for example the month of April
following week periods
01/04/2008-03/04/2008
05/04/2008 -10/04/2008
12/04/2008-17/04/2008
19/04/2008-24/04/2008
25/04/2008-30/04/2008
-------
currently i am manually entering the date
SELECT SUM(Amt_Sales),
Itm_Code
FROM Daily_Sales
WHERE Invoice_Date BETWEEN '01-apr-08'
AND '04-apr-08'
GROUP BY Itm_Code
please do help out
|
|
|
Re: how to calculate sales for a week [message #322534 is a reply to message #322531] |
Sat, 24 May 2008 03:50   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
1/ '01-apr-08' is not a date it is just a string:
SQL> select to_date('01-apr-08') from dual;
select to_date('01-apr-08') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
2/ TO_CHAR can give you the week day
3/ NEXT_DAY can give you the next Thursday or Saturday
4/ A week is always 7 days
5/ A row generator (search for this) can give you all the weeks of the month
Regards
Michel
[Updated on: Sat, 24 May 2008 08:53] Report message to a moderator
|
|
|
Re: how to calculate sales for a week [message #322541 is a reply to message #322534] |
Sat, 24 May 2008 05:10   |
melvinRav
Messages: 43 Registered: April 2008 Location: India
|
Member |
|
|
hi Michel, thanks for your reply i tried the following code for date '01-apr-08' and got the output as
SQL> select to_date('01-apr-08') as testdate from dual;
TESTDATE
---------
01-APR-08
what i want is to run the select clause for the period Saturday to Thursday
every week , on Friday
i was asking on how to select Saturday to Thursday
i was showing how the selection would be when the start and the end of the month comes in between this two days
i am lost on how to select the date
|
|
|
|
|
Re: how to calculate sales for a week [message #322557 is a reply to message #322553] |
Sat, 24 May 2008 08:35   |
melvinRav
Messages: 43 Registered: April 2008 Location: India
|
Member |
|
|
the first two links don't work, i tried accessing it but its not going anywhere .
i guess i am going no where with this post of mine ,
i was asking on how to select Saturday to Thursday
i was showing how the selection would be when the start and the end of the month comes in between this two days
but again i cant find out how to select the period
|
|
|
|
Re: how to calculate sales for a week [message #322560 is a reply to message #322557] |
Sat, 24 May 2008 08:54   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | the first two links don't work, i tried accessing it but its not going anywhere .
|
Sorry I took local documentation instead of online (it is now fixed) one but you could easily find them in SQL Reference.
Useless to repeat the question over the topic, we understood it, now try to solve it with the elements we gave.
Regards
Michel
[Updated on: Sat, 24 May 2008 09:19] Report message to a moderator
|
|
|
|
Re: how to calculate sales for a week [message #322570 is a reply to message #322558] |
Sat, 24 May 2008 09:19   |
melvinRav
Messages: 43 Registered: April 2008 Location: India
|
Member |
|
|
Anacedent
I am sorry if i have offended you in any way , i was answering to the point, yeah Google and search function on this forum work immaculately....
and Mr Michel
thanks for your heads up, on the links ..., if i had some idea on how to select i would have done it , but i guess ,i am back to square one
|
|
|
|
|
|
|
|
|
|
Re: how to calculate sales for a week [message #322582 is a reply to message #322580] |
Sat, 24 May 2008 10:45   |
melvinRav
Messages: 43 Registered: April 2008 Location: India
|
Member |
|
|
thank you very much Mr Zoltan , for the query
i tried the query it holds good for the values taken for the current month .
For the 01.may.08 which is a Thursday ,if i set the the current date to 02.may.08 which is Friday , i would get the value of thursday only , which is correct.
now how can i get the values of the last period of April which is from 26.apr.08 to 30.apr.08 , if where run it on the 2nd of Friday May
|
|
|
Re: how to calculate sales for a week [message #322583 is a reply to message #322582] |
Sat, 24 May 2008 10:48   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
melvinRav wrote on Sat, 24 May 2008 17:45 | thank you very much Mr Zoltan , for the query
i tried the query it holds good for the values taken for the current month .
For the 01.may.08 which is a Thursday ,if i set the the current date to 02.may.08 which is Friday , i would get the value of thursday only , which is correct.
now how can i get the values of the last period of April which is from 26.apr.08 to 30.apr.08 , if where run it on the 2nd of Friday May
|
You change the terms of the problem, so the solution is different.
Are 26.apr.08 to 30.apr.08 part of 02.may.08 or not? If not, which report it is part of?
Regards
Michel
|
|
|
Re: how to calculate sales for a week [message #322584 is a reply to message #322583] |
Sat, 24 May 2008 11:03   |
melvinRav
Messages: 43 Registered: April 2008 Location: India
|
Member |
|
|
No they are not part of of 02.may.08 report , what i wanted to indicate was that when i ran the query for which Zoltan had put up on 2 nd may , it works . it just show Thursday result , which is what i want
Now at end of april that is from 26.apr.08 to 30.apr.08 which is from saturday to wednesday , how can i get the values for this period , using the above query
|
|
|
|
|
|
|
|
|
|
|
|
Re: how to calculate sales for a week [message #322897 is a reply to message #322801] |
Mon, 26 May 2008 14:33   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
zoltanp wrote on Mon, 26 May 2008 11:55 | hi!
Here are one example:
the meaning of "to_date('01-apr-08')" is 1st April 2008 00:00am
|
Always use an explicit dateformat and a four-digit year when doing a string-to-date conversion
|
|
|
Re: how to calculate sales for a week [message #322898 is a reply to message #322891] |
Mon, 26 May 2008 14:34   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
melvinRav wrote on Mon, 26 May 2008 18:53 | hi zoltan
i thought trunc of date removes the time factor
|
But using trunc (or any function) on the column would render any indexes on that column unusable.
|
|
|
|
|
Re: how to calculate sales for a week [message #323157 is a reply to message #322801] |
Tue, 27 May 2008 08:42   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
zoltanp wrote on Mon, 26 May 2008 05:55 | hi!
Here are one example:
the meaning of "to_date('01-apr-08')" is 1st April 2008 00:00am.
|
orcl10g SCOTT>l
1* select to_char(to_date('01-apr-08'),'mm/dd/yyyy') from dual
orcl10g SCOTT>/
TO_CHAR(TO
----------
04/01/0008
|
|
|
|
|
|