May 29, 2020 at 12:22 pm
What I have missed from this code?
I'm trying to show the DateTimeRaised field as a week ending Sunday and week ending Thursday. The FkIssueGroupID isn't unique but each row should have the same date. The MAX is being used to bring back a single row.
Whilst the week ending value for Thursday is correct the week days contained within it don't line up i.e.
Week ending Thursday will have a date of the 21/05/20 (Thursday) but the date values covered are 18/05 to 24/05 (Mon-Sund). I want the date range covered to be 15/05 to 21/05
Thanks
Select distinct w.fkIssueGroupID
,[FormDateTimeRaised] =MAX ([DateTimeRaised])
,[WEnd Raised -Sund] = max (CONVERT (date, DATEADD(dd, 8 - (DATEPART(dw, [DateTimeRaised])), [DateTimeRaised]) ))
,[WEnd Raised - Thur]= max (CONVERT (date, DATEADD(dd, 5 - (DATEPART(dw, [DateTimeRaised])), [DateTimeRaised]) ))
....
May 29, 2020 at 5:39 pm
When I run your query with the date set to 21/05/20, the WEend Raised - Sund is the 24th and the Thursday one is the 21st. Does this not match your requirements?
The problem I see coming up while doing some testing is that if I change the date to the 22nd, I expect the THURSDAY call to be the 28th, not the 21st again, right? If so, that is because your DATEPART portion is coming back larger than 5 and as such, it is subtracting to get the Thursday of that particular week. If you want to get the following Thursday, you are going to need a CASE statement to ensure you don't go negative with the DATEPART. So something like this for your Thursday calculation:
,[WEnd Raised - Thur] = MAX((CONVERT(date, DATEADD(dd, (CASE WHEN DATEPART(dw,[DateTimeRaised]) > 5 THEN 12 ELSE 5 END) - DATEPART(dw,[DateTimeRaised]), [DateTimeRaised])
What your date conversions are doing IF the number minus (8 for Sunday and 5 for Thursday) is less than 8 is grabbing the day of the week for the current week. So the 5 is grabbing the THURSDAY of the week that starts with SUNDAY.
NOTE - the above assumes your weeks are starting on SUNDAY. If you adjust it so your weeks start on a different day, the calculations need to be shifted.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply