June 13, 2014 at 8:09 pm
1234, 10/1/14, 42.75, 84.00
1234, 9/2/14, 11.25, 12.00
1111, 10/1/14, 10.50, 23.00
June 13, 2014 at 8:26 pm
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!
June 13, 2014 at 8:32 pm
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!
June 14, 2014 at 3:22 am
Tallboy (6/13/2014)
1234, 10/1/14, 42.75, 84.001234, 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
June 15, 2014 at 6:22 pm
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
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply