Week Ending Thursday, can some one tweek my code?

  • bark01

    SSC Veteran

    Points: 217

    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




    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]) ))

    • This topic was modified 1 month, 2 weeks ago by  bark01.
    • This topic was modified 1 month, 2 weeks ago by  bark01.
    • This topic was modified 1 month, 2 weeks ago by  bark01.
  • Mr. Brian Gale


    Points: 22954

    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.

Viewing 2 posts - 1 through 2 (of 2 total)

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