Week number within the month - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Week number within the month

Author

Topic

jdoering
Starting Member

32 Posts

Posted - 2002-10-08 : 17:14:51

Hello!

I have a brain teaser that I am trying to figure out. I am writing a performance report for the Sales Team and they wish to post their performance in weekly buckets. Example: For the month of October, they wish to see their results by Week 1, Week 2, Week 3, Week 4, etc. then it starts over again in November 1st on Week 1. Now I realize that SQL server has a function datename using the wk parameter, but I cannot figure out how to logically fit this in here. Any ideas would be greatly appreciated.

Thanks,
Julie

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-08 : 18:44:23

A small modification of this will work

DECLARE @Date As DATETIME

SET @Date = '10/13/2002'

SELECT *
FROM
(
SELECT
DATEPART(m,@Date) As [Month],
CASE
WHEN @Date BETWEEN DATEADD(d,-DATEPART(d,@Date) + 1,@Date) AND DATEADD(d,7*1-DATEPART(dw,DATEADD(d,-DATEPART(d,@Date) + 1,@Date)),DATEADD(d,-DATEPART(d,@Date) + 1,@Date)) THEN 1
WHEN @Date BETWEEN DATEADD(d,7*1-DATEPART(dw,DATEADD(d,-DATEPART(d,@Date) + 1,@Date)),DATEADD(d,-DATEPART(d,@Date) + 1,@Date)) AND DATEADD(d,7*2-DATEPART(dw,DATEADD(d,-DATEPART(d,@Date) + 1,@Date)),DATEADD(d,-DATEPART(d,@Date) + 1,@Date)) THEN 2
WHEN @Date BETWEEN DATEADD(d,7*2-DATEPART(dw,DATEADD(d,-DATEPART(d,@Date) + 1,@Date)),DATEADD(d,-DATEPART(d,@Date) + 1,@Date)) AND DATEADD(d,7*3-DATEPART(dw,DATEADD(d,-DATEPART(d,@Date) + 1,@Date)),DATEADD(d,-DATEPART(d,@Date) + 1,@Date)) THEN 3
WHEN @Date BETWEEN DATEADD(d,7*3-DATEPART(dw,DATEADD(d,-DATEPART(d,@Date) + 1,@Date)),DATEADD(d,-DATEPART(d,@Date) + 1,@Date)) AND DATEADD(d,7*4-DATEPART(dw,DATEADD(d,-DATEPART(d,@Date) + 1,@Date)),DATEADD(d,-DATEPART(d,@Date) + 1,@Date)) THEN 4
ELSE 5
END As [Week]
) As A
GROUP BY A.[Month], A.[Week]

ALL YOU NEED TO DO IS ADD A FROM CLAUSE THE INNER SELECT
AND CHANGE @Date to a field

Edited by - ValterBorges on 10/08/2002 19:03:20

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-08 : 19:10:06

Damn! You pretty much came up with the same thing I did:

SELECT DatePart(month, DT) AS Month,
DatePart(wk, DT)-DatePart(wk, DateAdd(d, 1-DatePart(d, DT), DT))+1 AS Week
FROM myTable
GROUP BY DatePart(month, DT), DatePart(wk, DT)-DatePart(wk, DateAdd(d, 1-DatePart(d, DT), DT))+1

I originally had this in a subquery as well, but only because I was generating date values. It should work on the sales table directly (change DT to match name of date column, blah, blah, you know the drill)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-08 : 19:15:50

Naw! I like yours better.
You worked with weeks instead of days

DATE_WEEK_NUMBER - WEEK_NUMBER_1st_OF_DATE_MONTH + 1

Nice formula.

Edited by - ValterBorges on 10/08/2002 19:28:31

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-08 : 19:21:06

But mine doesn't do the neat underline-email-address thingie that yours does! Yours also looks like it actually DOES something, mine is just...a formula.

jdoering
Starting Member

32 Posts

Posted - 2002-10-09 : 09:46:21

Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources