SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Sum items by Day of Week
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vergy39
Starting Member

USA
15 Posts

Posted - 09/06/2013 :  12:36:36  Show Profile  Reply with Quote
I have an import that runs daily into a sql database. I would like to sum the records by the day of the week. For example, how many were imported on Sunday, Monday, Tuesday, etc. I can get the count by Import date, but I want to start summing all the Sundays, Mondays, Tuesdays, Wednesdays, Thursdays, Fridays, and Saturdays in a month. I have seen a few threads on summing by Week ending, but having trouble using that info for my situation. Any assistance is greatly appreciated.

Thanks
David V

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/06/2013 :  12:54:23  Show Profile  Reply with Quote
One of the two below. In the first, 0 corresponds to Monday, 1 to Tuesday and so on and 6 corresponds to Sunday
SELECT
	DATEDIFF(DAY,0,ImportDate)%7,
	SUM(ImportedQuantity)
FROM
	YourTable
GROUP BY
	DATEDIFF(DAY,0,ImportDate)%7;
	
SELECT
	DATENAME(weekday,ImportDate) AS Weekday,
	SUM(ImportedQuantity)
FROM
	YourTable
GROUP BY
	DATENAME(weekday,ImportDate)
Go to Top of Page

Vergy39
Starting Member

USA
15 Posts

Posted - 09/06/2013 :  15:28:57  Show Profile  Reply with Quote
Thanks James. This helped alot. I am wondering though if I could get the results to show for each Sunday in a month. For example, I have 4 Sundays in August, I would want to return the number imported for each Sunday, ie:
Date NumImported
8/4/2013 30265
8/11/2013 42569
8/18/2013 15623
8/25/2013 35263

Something like that. I can get this by just entering the dates in a where claus, but would like to get more dates by using between in a where clause. Here is what you gave me,

SELECT DATENAME(weekday, MyDate) AS Weekday, Count(MyDate) AS NumImport
FROM MyTable
Where MyDate Between '2013-08-01 00:01' AND '2013-09-05 23:59'
GROUP BY DATENAME(weekday, MyDate)
Order By DATENAME(weekday, MyDate)

It returns:
MyDate NumImport
Friday 109392
Monday 106300
Saturday 56233
Sunday 30934
Thursday 136542
Tuesday 113273
Wednesday 102435

Thanks for the help.

David V
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/06/2013 :  15:55:55  Show Profile  Reply with Quote
Do you mean that you want to see only data imported on Sundays, or do you want to aggregate all the data imported upto each Sunday (thus aggregating the data for the 7 days) into one group?

If you just want to see the Sundays, add another condition to the where clause as in:
...
AND datediff(day,0,MyDate)%7 = 6
Go to Top of Page

Vergy39
Starting Member

USA
15 Posts

Posted - 09/06/2013 :  16:16:33  Show Profile  Reply with Quote
Thanks again for the quick reply, however, that Where claus just returns the sum from all the Sunday's that would be in the Between dates listed in the Where clause. I want it to return the individual Sundays within the between dates to compare the totals. IE:
Date
Sunday, 8/4/2013 6126
Sunday, 8/11/2013 6257
Sunday, 8/18/2013 5844
Sunday, 8/25/2013 6550
Sunday, 9/1/2013 6157
Total for All Sundays 30934

Again, I certainly appreciate your assistance.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/06/2013 :  17:06:30  Show Profile  Reply with Quote
I didn't quite follow what you want to do; can you give this a try:
SELECT  DATENAME(weekday, MyDate) AS Weekday ,
        COUNT(MyDate) AS NumImport
FROM    MyTable
WHERE   MyDate BETWEEN '2013-08-01 00:01'
               AND     '2013-09-05 23:59'
        AND DATEDIFF(DAY,0,MyDate)%7 = 6
GROUP BY GROUPING SETS (DATENAME(weekday, MyDate),())
ORDER BY DATENAME(weekday, MyDate)
Go to Top of Page

Vergy39
Starting Member

USA
15 Posts

Posted - 09/06/2013 :  18:01:04  Show Profile  Reply with Quote
I tried what you sent, but I got an error that stated "Server: Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near 'SETS'." Looks like I put this thread in the worng forum. I have sql 2000. Sorry about that. However, I do appreciate your time.

Thanks
David V
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000