Home » SQL & PL/SQL » SQL & PL/SQL » how to calculate sales for a week (oracle 8i)
how to calculate sales for a week [message #322531] Sat, 24 May 2008 02:46 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #322553 is a reply to message #322541] Sat, 24 May 2008 08:21 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Read the links Michel posted.
Also, to_date has two parameters, you should provide a format mask as well. Finally: a year contains four digits. Remember the Y2k "problem"?
Re: how to calculate sales for a week [message #322556 is a reply to message #322531] Sat, 24 May 2008 08:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: how to calculate sales for a week [message #322557 is a reply to message #322553] Sat, 24 May 2008 08:35 Go to previous messageGo to next message
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 #322558 is a reply to message #322531] Sat, 24 May 2008 08:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>the first two links don't work, i tried accessing it but its not going anywhere .

I am sorry to see that both the Search function on this forum & GOOGLE are broken for you.

Please continue to wait when repairs are done.
Re: how to calculate sales for a week [message #322560 is a reply to message #322557] Sat, 24 May 2008 08:54 Go to previous messageGo to next message
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 #322567 is a reply to message #322531] Sat, 24 May 2008 09:12 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
If you run it on Friday, this will be the solution:

SELECT   SUM(Amt_Sales),
         Itm_Code
FROM     Daily_Sales
WHERE    trunc(Invoice_Date) BETWEEN trunc(sysdate-6)
                              AND trunc(sysdate-1)
GROUP BY Itm_Code


There is a default date format in the database.
It's the reason for to_date('01-apr-08') works in
your database.


Best Regards
Zoltán Patalenszki
Re: how to calculate sales for a week [message #322570 is a reply to message #322558] Sat, 24 May 2008 09:19 Go to previous messageGo to next message
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 #322571 is a reply to message #322567] Sat, 24 May 2008 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
@zoltanp

Your statement don't work for Friday April 4th.
And it also don't work if there is a row on Thursday 4PM, for instance.
But it is a good starting point.

Regards
Michel

[Updated on: Sat, 24 May 2008 09:22]

Report message to a moderator

Re: how to calculate sales for a week [message #322574 is a reply to message #322571] Sat, 24 May 2008 09:29 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
Michel , I agree regarding the 4th Friday of the month ,
but i didn't understand it wont work if there is a row on Thursday 4 pm part
Re: how to calculate sales for a week [message #322576 is a reply to message #322574] Sat, 24 May 2008 09:36 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
Sorry. There is my new attempt:

SELECT   SUM(Amt_Sales),
         Itm_Code
FROM     Daily_Sales
WHERE    trunc(Invoice_Date) 
BETWEEN greatest(trunc(sysdate-6),trunc(sysdate,'MONTH'))
        AND least(trunc(sysdate-1),last_day(trunc(sysdate)))
GROUP BY Itm_Code


The previous worked on Thursday 4PM too.

Regads
Zoltán Patalenszki
Re: how to calculate sales for a week [message #322577 is a reply to message #322574] Sat, 24 May 2008 09:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
@melvinRav

Sorry, I didn't see there is a trunc on the original date column, it is out of my scope to imagine someone could remove the possibility to use a natural index adding a function. Wink

Regards
Michel

[Updated on: Sat, 24 May 2008 09:39]

Report message to a moderator

Re: how to calculate sales for a week [message #322578 is a reply to message #322576] Sat, 24 May 2008 09:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
@zoltanp,

This seems a good one now but for the use of index on Invoice_Date.

Regards
Michel

[Updated on: Sat, 24 May 2008 09:42]

Report message to a moderator

Re: how to calculate sales for a week [message #322580 is a reply to message #322577] Sat, 24 May 2008 09:47 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
If your Invoice_Date is not contain time
you can use it in following mode(and may use natural index):

SELECT   SUM(Amt_Sales),
         Itm_Code
FROM     Daily_Sales
WHERE    Invoice_Date 
BETWEEN greatest(sysdate-6,trunc(sysdate,'MONTH'))
        AND least(sysdate-1,last_day(sysdate))
GROUP BY Itm_Code


If there is time in it, your original select statement is bad too.

regards
Zoltán Patalenszki
Re: how to calculate sales for a week [message #322581 is a reply to message #322580] Sat, 24 May 2008 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Assuming you start your report on Friday 12PM.
You won't get a row on Saturday 8AM and a row on Thursday 4PM.

Regards
Michel
Re: how to calculate sales for a week [message #322582 is a reply to message #322580] Sat, 24 May 2008 10:45 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #322585 is a reply to message #322584] Sat, 24 May 2008 11:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
When do you want them?
They are part of which report?
Not the 02.may.08 one, you said, of course neither 09.may.08 nor 25.apr.08 ones, so which one?

Regards
Michel
Re: how to calculate sales for a week [message #322592 is a reply to message #322585] Sat, 24 May 2008 13:24 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
I think that i understand the problem.

So:

On 2nd May you have to make 2 reports(26.04.08-30.04.08 and 01.05.08-01.05.08)

Is it your problem?

Zoltán Patalenszki
Re: how to calculate sales for a week [message #322593 is a reply to message #322592] Sat, 24 May 2008 13:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Is it your problem?

I have no problem.
You have to define your requirements. Do you want 2 reports?
But now you have one query, it is not difficult to build the second one.

By the way, remember the query posted is wrong. Reread the exceptions I posted.

Regards
Michel
Re: how to calculate sales for a week [message #322594 is a reply to message #322593] Sat, 24 May 2008 13:37 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
Sorry, Michael.


I wanted to answer the post number 322584.


Zoltán Patalenszki
Re: how to calculate sales for a week [message #322691 is a reply to message #322594] Sun, 25 May 2008 23:19 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
thank you Zoltan for the query , i am not that familiar with sql , but i couldn't understand about th exception part
that Michel was talking about .
Re: how to calculate sales for a week [message #322699 is a reply to message #322691] Mon, 26 May 2008 00:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
i am not that familiar with sql , but i couldn't understand about th exception part that Michel was talking about .

Try to understand the query.
The exception part is:
Quote:
Assuming you start your report on Friday 12PM.
You won't get a row on Saturday 8AM and a row on Thursday 4PM.

Regards
Michel
Re: how to calculate sales for a week [message #322801 is a reply to message #322691] Mon, 26 May 2008 04:55 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
hi!


Here are one example:

the meaning of "to_date('01-apr-08')" is 1st April 2008 00:00am.
If you have 1st April 2008 14:00am in the record,
the "invoice date between to_date('01-apr-01') and
to_date('01-apr-08')" will not return it.

Best Regards

Zoltán Patalenszki
Re: how to calculate sales for a week [message #322891 is a reply to message #322801] Mon, 26 May 2008 11:53 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
hi zoltan

i thought trunc of date removes the time factor
Re: how to calculate sales for a week [message #322895 is a reply to message #322891] Mon, 26 May 2008 12:30 Go to previous messageGo to next message
linlasj
Messages: 98
Registered: August 2005
Location: Linköping
Member
Yes,
It does.
Re: how to calculate sales for a week [message #322897 is a reply to message #322801] Mon, 26 May 2008 14:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
icon9.gif  Re: how to calculate sales for a week [message #322909 is a reply to message #322531] Mon, 26 May 2008 16:34 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
I gave up.

Everybody write but nobody read this forum.
Re: how to calculate sales for a week [message #322960 is a reply to message #322909] Mon, 26 May 2008 23:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
zoltanp wrote on Mon, 26 May 2008 23:34
I gave up.

Everybody write but nobody read this forum.

What do you mean?

Regards
Michel

Re: how to calculate sales for a week [message #323157 is a reply to message #322801] Tue, 27 May 2008 08:42 Go to previous messageGo to next message
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

Re: how to calculate sales for a week [message #323174 is a reply to message #323157] Tue, 27 May 2008 10:07 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
try it:

select to_char(to_date('20080401','yyyymmdd'),'mm/dd/yyyy hh24:mi') from dual

Best regards,
Zoltán Patalenszki
Re: how to calculate sales for a week [message #323176 is a reply to message #322960] Tue, 27 May 2008 10:11 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
Quote:
What do you mean?

I mean that we explained the same thing about five times.

Sorry, I wrote forum instead of topic.

Zoltán Patalenszki
Re: how to calculate sales for a week [message #323182 is a reply to message #323176] Tue, 27 May 2008 10:42 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
zoltanp wrote on Tue, 27 May 2008 17:11

I mean that we explained the same thing about five times.

What will you say in 10 years when you will have explained it more than 1000 times (and trust me it is a real number and not just words). Smile

Regards
Michel

Previous Topic: Order by in cursor
Next Topic: Performant Locking of Multiple Rows with FOR UPDATE
Goto Forum:
  


Current Time: Thu Feb 13 10:11:55 CST 2025