Sales by dept daily and monthly.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sales by dept daily and monthly.

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-18 : 10:16:21

Hi,
I have data after joining tables as :- (3 column)
date dept sale

1 march alcohol 45$
1 march alcohol 38$
1 march grocery 12$
1 marcc grocery 120$
1 march non food item 300$
.
,.
...
2 march alchol 29$
.....

similarily for all days example 1 march, 2 march, 3 march...31 march

I want 2 thigns, firstly
sale in each dept daily...
for example
1 march --alochol (45+38)
1 march --grocery (12+120)

The next thing i want is
sale in each dept monthly...
for example...
march month---alcohol 2000$( adding all the alcohol sales in whole month)
march month-- grocery 7000$( adding all groceries sold in hte whole month)
Similarliy all dept and their sales as of monthly(march)..

Any help is appreciated..

Regards,
Sushant
DBA
West Indies

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-18 : 10:21:56

"firstly sale in each dept daily..."


select date, dept, sum(sales)
from   yourtable
group by date, dept

"The next thing i want is sale in each dept monthly..."


select dateadd(month, datediff(month, 0, date), 0), dept, sum(sales)
from   yourtable
group by dateadd(month, datediff(month, 0, date), 0), dept

KH
[spoiler]Time is always against us[/spoiler]

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-18 : 12:15:46

@ khtan...
The sale by daily is perfect , i got it..

About the sale in each dept, monthly, i did get the results:-
2011-03-01 alcohol 10000$
2011-03-01 groceris 45678$
....18 results for march 1st
BUT i dont know if those all march 1st records are sales from 1 feb-28 feb or 1 march-18 march
Also,I wanted to see just 1 month sales (feb or march)so i tried to specify the clause HAVING date ='2011-02-01' ( feb month)
but it is giving errors.

Regards,
Sushant
DBA
West Indies

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-18 : 13:18:53

OH,
I used where clause and all was right now.
Thanks a lot.

Regards,
Sushant
DBA
West Indies

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-18 : 23:27:23

quote:
BUT i dont know if those all march 1st records are sales from 1 feb-28 feb or 1 march-18 march

the GROUP BY dateadd(month, datediff(month, 0, date), 0) means all the dates of the same month will be group under 1st of the month.
So all the sales from 1st Feb to 28th Feb 2011 will be under 1st Feb

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources