Current Week Filter - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Current Week Filter

Author

Topic

ncurran217
Starting Member

23 Posts

Posted - 2012-05-18 : 09:19:20

I want to be able to get the count of the current weeks sales from my table of sales. I have current month, but that will not be run as much as current week. Here is what I have in my query for current month:


SELECT        salesman, COUNT(salesman) AS Deals
FROM            Sales
WHERE        (MONTH(purchdate) = MONTH(CURRENT_TIMESTAMP)) AND (CancelDate IS NULL)
GROUP BY salesman
ORDER BY Deals DESC

Thanks for the help in advance. I am new to SQL and have learned a lot so far by googling for simple answers but I think I am getting into things I need to take a class or something on. Again thanks again!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-18 : 09:30:16

quote:
WHERE (MONTH(purchdate) = MONTH(CURRENT_TIMESTAMP))

This does not give you current month. But rather all the months regardless of year. That means you are counting sales for 2011, 2010 and whatever year. Is this what you want ?

for the current week, please defined your week. Sun to Sat ? Mon to Sun ?

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

ncurran217
Starting Member

23 Posts

Posted - 2012-05-18 : 09:38:04

Yea, But the data I am working with is 2012 only. I know I can put in an AND statement and do the same thing for year as I did month and get the year in their only if I go more into past years worth of data.

As for your question Mon through Sun.

Thanks!

ncurran217
Starting Member

23 Posts

Posted - 2012-05-18 : 09:44:28

Also, no I do not want current week for all years, just this year, if you can include that. Sorry just re-read your question and I left that part of the answer out.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-18 : 09:55:07

current week


SELECT      salesman, COUNT(salesman) AS Deals
FROM         Sales
WHERE       purchdate >= dateadd(week, datediff(week, 0, getdate()), 0)
AND         purchdate <  dateadd(week, datediff(week, 0, getdate()) + 1, 0)
AND         (CancelDate IS NULL)
GROUP BY salesman
ORDER BY Deals DESC

for your current month, it is advisable to use


SELECT      salesman, COUNT(salesman) AS Deals
FROM         Sales
WHERE       purchdate >= dateadd(month, datediff(month, 0, CURRENT_TIMESTAMP), 0)
AND         purchdate <  dateadd(month, datediff(month, 0, CURRENT_TIMESTAMP) + 1, 0)
AND         (CancelDate IS NULL)
GROUP BY salesman
ORDER BY Deals DESC

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

ncurran217
Starting Member

23 Posts

Posted - 2012-05-18 : 10:05:55

Thank you so much!

ncurran217
Starting Member

23 Posts

Posted - 2012-05-18 : 10:14:21

So does this do just this actual week and not include previous years data?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-18 : 10:18:58