Need to get 1st day of week from isowk

Question

Post reply

Need to get 1st day of week from isowk

pete-675551

SSChasing Mays

Points: 652
More actions
December 1, 2014 at 3:48 pm

#299012

Hello
I have a query that counts number of applicants per week and displays week number and applicants.
What I need is a date for the start of that week. So basically I need to know the what date is Monday for the week number returned by isowk.
Here is the query:
SELECT COUNT(applicantid) AS ApplicantCount,
DATEPART(isowk,dtmRegistered) AS WeekNo
FROM UserApplication
GROUP BY DATEPART(isowk,dtmRegistered)
As you can see this returns the number of applicants per week something like this:
WEEK APPLICANTS
10 555
12 1374
20 109
I need it like this:
WEEK FIRSTDAYOFWEEK APPLICANTS
10 03/03/2014 555
12 03/17/2014 1374
20 ...... etc
Where the first day of week is Monday.
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I use the following formula to calculate the previous monday. Just change GETDATE() for any date you need to use.

DATEADD(WK, DATEDIFF(WK, 0, GETDATE() - 2), 0)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 2

Luis Cazares (12/1/2014)
I use the following formula to calculate the previous monday. Just change GETDATE() for any date you need to use.
DATEADD(WK, DATEDIFF(WK, 0, GETDATE() - 2), 0)

Oh, be careful now... the WK DATEPART relies on the DATEFIRST option and can change from installation to installation and, sometimes, session to session even with just a language change.

SET LANGUAGE Italian;

GO

SELECT @@DATEFIRST;

GO

SET LANGUAGE us_english;

GO

SELECT @@DATEFIRST;

If you want to calculate Mondays, start with a known Monday (like Jan 1st, 1900 {day "0"} or Jan 1st, 1753) and calculate the number of weeks using some integer math rather than using the WEEK/WK DATEPART. Like this...

SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,0)

Just like the similar DATEADD/DATEDIFF trick without the "7's" works to strip off the time element, this works to strip off the "day of the week" element.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pete-675551 SSChasing Mays Points: 652 More actions · Answer 3

Ill try it .

I have the week number there should be a way to get the Monday for that week.

Thanks

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 4

Not sure (didn't test the code) because you didn't provide any readily consumable data but I believe this should be pretty close to what you're looking for...

SELECT WeekNo = DATEPART(isowk,dtmRegistered)

,FirstDayOfWeek = DATEADD(dd,DATEDIFF(dd,0,dtmRegistered)/7*7,0)

,ApplicantCount = COUNT(applicantid)

FROM dbo.UserApplication

GROUP BY DATEPART(isowk,dtmRegistered)

,DATEDIFF(dd,0,dtmRegistered)/7

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pete-675551 SSChasing Mays Points: 652 More actions · Answer 5

Jeff Moden (12/1/2014)
Not sure (didn't test the code) because you didn't provide any readily consumable data but I believe this should be pretty close to what you're looking for...
SELECT WeekNo = DATEPART(isowk,dtmRegistered)
,FirstDayOfWeek = DATEADD(dd,DATEDIFF(dd,0,dtmRegistered)/7*7,0)
,ApplicantCount = COUNT(applicantid)
FROM dbo.UserApplication
GROUP BY DATEPART(isowk,dtmRegistered)
,DATEDIFF(dd,0,dtmRegistered)/7
;

That works. Thanks!