Grouping Dates in a Table by 4 weeks at a time

Question

Post reply

Grouping Dates in a Table by 4 weeks at a time

Viewing 5 posts - 16 through 19 (of 19 total)

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

Tallboy SSCrazy Points: 2455 More actions · Answer 1

1234, 10/1/14, 42.75, 84.00

1234, 9/2/14, 11.25, 12.00

1111, 10/1/14, 10.50, 23.00

pietlinden SSC Guru Points: 63503 More actions · Answer 2

Sean,

I had to cheat and add an extra record, because I needed to check the grouping... (should have mentioned that).

To the OP...

If you're asking for help, please help us to help you. You are getting free help, so it's up to you to provide enough detail and enough records for us to be able to test our solutions to your problem. If you don't, you're just wasting people's time, and it's very likely you won't get a verifiable correct answer. Okay ...</rant>

Feel free to munge the data you're sharing... that's perfectly okay. We all understand that some data is confidential, but please give enough so we can test our results.

Thanks!

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Sean,

I had to cheat and add an extra record, because I needed to check the grouping... (should have mentioned that).

To the OP...

If you're asking for help, please help us to help you. You are getting free help, so it's up to you to provide enough detail and enough records for us to be able to test our solutions to your problem. If you don't, you're just wasting people's time, and it's very likely you won't get a verifiable correct answer. Okay ...</rant>

Feel free to munge the data you're sharing... that's perfectly okay. We all understand that some data is confidential, but please give enough so we can test our results.

Thanks!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

Tallboy (6/13/2014)
1234, 10/1/14, 42.75, 84.00
1234, 9/2/14, 11.25, 12.00
1111, 10/1/14, 10.50, 23.00

maybe I misread your requirements...but I thought that the week end date was a Sunday?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

pietlinden SSC Guru Points: 63503 More actions · Answer 5

If you are breaking up the year into 4-week "groups", then from what date are you starting the count? The first day of the year? If not, how do you determine the date to start from? Could you post a simple example we can test against, and the expected result? I know several people have asked, so I apologize for beating a dead horse, but without a few answers, nobody can really help you. So please make helping you easier by providing enough information to answer the question.

If you are grouping into 4-week groups, then you could do a DateDiff() to get the number of weeks from a given date (in your subquery) and then do the grouping in the outer query (by the week grouping). But without some business rules and a little bit of explanation, nobody can help you. Your query could look something like this:

SELECT WeekOfYear, COUNT(*) AS... -- Aggregate expression goes here

FROM

(SELECT [SomeDate], DATEDIFF(wk,[StartDate],[SomeDate]) AS WeekOfYear

FROM MyTable

WHERE... ) x

If your StartDate is the first of the year, you can create an expression to return the first day of the year. (Somewhere around here is Lynn Pettis' examples of date functions... dig around for it... consider it homework!)

Okay, found Lynn's Date Routines[/url] post here

Here's the newer version of the query...

SELECT ID

, PersonnelNo

, WeekEnding

, dateadd(yy, datediff(yy, 0, WeekEnding), 0) AS FirstOfYear -- Beginning of this year

, DATEDIFF(wk, dateadd(yy, datediff(yy, 0, WeekEnding), 0), WeekEnding) AS WeekNo

, ROUND(DATEDIFF(wk, dateadd(yy, datediff(yy, 0, WeekEnding), 0), WeekEnding)/4,0) AS WeekGroup

FROM #WorkHours;

Then you can group on the WeekGroup column