3/05/2014

SQL Query to Calculate Start & End Dates of the Week

Working with a client, we were given the directive to calculate the Start and End Dates of the Week.

However, their week starts on Tuesdays, ending on Monday's.

And the First Day of the Month always begins a new week.

And the Last Day of the Month always ends the week.

So I wrote a query to calculate based on that logic.

And then did an Update statement, as follows:







UPDATE [dbo].[DimDate]
SET WeightValuationWeekStartDate = jb.StartDate
,WeightValuationWeekEndDate = jb.EndDate
FROM (
SELECT
      date_date
      ,WeekOfMonth
      ,AnalysisDayOfWeek
      ,dayname
      ,CASE
            WHEN WeekOfMonth = 1
                  THEN CASE
                              WHEN DAY = 1
                                    THEN FirstDayOfMonth
                              WHEN DayName = 'Tuesday'
                                    THEN date_date
                              WHEN DayName = 'Wednesday'
                                    AND day <= 7
                                    THEN FirstDayOfMonth
                              WHEN DayName = 'Thursday'
                                    AND day <= 7
                                    THEN FirstDayOfMonth
                              WHEN DayName = 'Friday'
                                    AND day <= 7
                                    THEN FirstDayOfMonth
                              WHEN DayName = 'Saturday'
                                    AND day <= 7
                                    THEN FirstDayOfMonth
                              WHEN DayName = 'Sunday'
                                    AND day <= 7
                                    THEN FirstDayOfMonth
                              WHEN DayName = 'Monday'
                                    AND day <= 7
                                    THEN FirstDayOfMonth
                              WHEN DayName = 'Wednesday'
                                    THEN DateAdd(d, - 1, date_date)
                              WHEN DayName = 'Thursday'
                                    THEN DateAdd(d, - 2, date_date)
                              WHEN DayName = 'Friday'
                                    THEN DateAdd(d, - 3, date_date)
                              WHEN DayName = 'Saturday'
                                    THEN DateAdd(d, - 4, date_date)
                              WHEN DayName = 'Sunday'
                                    THEN DateAdd(d, - 5, date_date)
                              WHEN DayName = 'Monday'
                                    THEN DateAdd(d, - 6, date_date)
                              END
            WHEN WeekOfMonth = 2
                  THEN CASE
                              WHEN DayName = 'Tuesday'
                                    THEN date_date
                              WHEN DayName = 'Wednesday'
                                    THEN DateAdd(d, - 1, date_date)
                              WHEN DayName = 'Thursday'
                                    THEN DateAdd(d, - 2, date_date)
                              WHEN DayName = 'Friday'
                                    THEN DateAdd(d, - 3, date_date)
                              WHEN DayName = 'Saturday'
                                    THEN DateAdd(d, - 4, date_date)
                              WHEN DayName = 'Sunday'
                                    THEN FirstDayOfMonth
                              WHEN DayName = 'Monday'
                                    THEN FirstDayOfMonth
                              END
            WHEN WeekOfMonth = 3
                  THEN CASE
                              WHEN DayName = 'Tuesday'
                                    THEN date_date
                              WHEN DayName = 'Wednesday'
                                    THEN DateAdd(d, - 1, date_date)
                              WHEN DayName = 'Thursday'
                                    THEN DateAdd(d, - 2, date_date)
                              WHEN DayName = 'Friday'
                                    THEN DateAdd(d, - 3, date_date)
                              WHEN DayName = 'Saturday'
                                    THEN DateAdd(d, - 4, date_date)
                              WHEN DayName = 'Sunday'
                                    THEN DateAdd(d, - 5, date_date)
                              WHEN DayName = 'Monday'
                                    THEN DateAdd(d, - 6, date_date)
                              END
            WHEN WeekOfMonth = 4
                  THEN CASE
                              WHEN DayName = 'Tuesday'
                                    THEN date_date
                              WHEN DayName = 'Wednesday'
                                    THEN DateAdd(d, - 1, date_date)
                              WHEN DayName = 'Thursday'
                                    THEN DateAdd(d, - 2, date_date)
                              WHEN DayName = 'Friday'
                                    THEN DateAdd(d, - 3, date_date)
                              WHEN DayName = 'Saturday'
                                    THEN DateAdd(d, - 4, date_date)
                              WHEN DayName = 'Sunday'
                                    THEN DateAdd(d, - 5, date_date)
                              WHEN DayName = 'Monday'
                                    THEN DateAdd(d, - 6, date_date)
                              END
            WHEN WeekOfMonth = 5
                  THEN CASE
                              WHEN DayName = 'Tuesday'
                                    THEN date_date
                              WHEN DayName = 'Wednesday'
                                    THEN DateAdd(d, - 1, date_date)
                              WHEN DayName = 'Thursday'
                                    THEN DateAdd(d, - 2, date_date)
                              WHEN DayName = 'Friday'
                                    THEN DateAdd(d, - 3, date_date)
                              WHEN DayName = 'Saturday'
                                    THEN DateAdd(d, - 4, date_date)
                              WHEN DayName = 'Sunday'
                                    THEN DateAdd(d, - 5, date_date)
                              WHEN DayName = 'Monday'
                                    THEN DateAdd(d, - 6, date_date)
                              END
            WHEN WeekOfMonth = 6
                  THEN CASE
                              WHEN DayName = 'Tuesday'
                                    THEN date_date
                              WHEN DayName = 'Wednesday'
                                    THEN DateAdd(d, - 1, date_date)
                              WHEN DayName = 'Thursday'
                                    THEN DateAdd(d, - 2, date_date)
                              WHEN DayName = 'Friday'
                                    THEN DateAdd(d, - 3, date_date)
                              WHEN DayName = 'Saturday'
                                    THEN DateAdd(d, - 4, date_date)
                              WHEN DayName = 'Sunday'
                                    THEN DateAdd(d, - 5, date_date)
                              WHEN DayName = 'Monday'
                                    THEN DateAdd(d, - 6, date_date)
                              END
            END StartDate
      ,CASE
            WHEN LastDayOfMonth = date_date
                  THEN LastDayOfMonth
            WHEN WeekOfMonth = 1
                  THEN CASE
                              WHEN DayName = 'Monday'
                                    THEN date_date
                              WHEN DayName = 'Tuesday'
                                    THEN DateAdd(d, 6, date_date)
                              WHEN DayName = 'Wednesday'
                                    THEN DateAdd(d, 5, date_date)
                              WHEN DayName = 'Thursday'
                                    THEN DateAdd(d, 4, date_date)
                              WHEN DayName = 'Friday'
                                    THEN DateAdd(d, 3, date_date)
                              WHEN DayName = 'Saturday'
                                    THEN DateAdd(d, 2, date_date)
                              WHEN DayName = 'Sunday'
                                    THEN DateAdd(d, 1, date_date)
                              END
            WHEN WeekOfMonth = 2
                  THEN CASE
                              WHEN DayName = 'Monday'
                                    THEN date_date
                              WHEN DayName = 'Tuesday'
                                    THEN DateAdd(d, 6, date_date)
                              WHEN DayName = 'Wednesday'
                                    THEN DateAdd(d, 5, date_date)
                              WHEN DayName = 'Thursday'
                                    THEN DateAdd(d, 4, date_date)
                              WHEN DayName = 'Friday'
                                    THEN DateAdd(d, 3, date_date)
                              WHEN DayName = 'Saturday'
                                    THEN DateAdd(d, 2, date_date)
                              WHEN DayName = 'Sunday'
                                    THEN DateAdd(d, 1, date_date)
                              END
            WHEN WeekOfMonth = 3
                  THEN CASE
                              WHEN DayName = 'Monday'
                                    THEN date_date
                              WHEN DayName = 'Tuesday'
                                    THEN DateAdd(d, 6, date_date)
                              WHEN DayName = 'Wednesday'
                                    THEN DateAdd(d, 5, date_date)
                              WHEN DayName = 'Thursday'
                                    THEN DateAdd(d, 4, date_date)
                              WHEN DayName = 'Friday'
                                    THEN DateAdd(d, 3, date_date)
                              WHEN DayName = 'Saturday'
                                    THEN DateAdd(d, 2, date_date)
                              WHEN DayName = 'Sunday'
                                    THEN DateAdd(d, 1, date_date)
                              END
            WHEN WeekOfMonth = 4
                  THEN CASE
                              WHEN DayName = 'Monday'
                                    THEN date_date
                              WHEN DayName = 'Tuesday'
                                    THEN DateAdd(d, 6, date_date)
                              WHEN DayName = 'Wednesday'
                                    THEN DateAdd(d, 5, date_date)
                              WHEN DayName = 'Thursday'
                                    THEN DateAdd(d, 4, date_date)
                              WHEN DayName = 'Friday'
                                    THEN DateAdd(d, 3, date_date)
                              WHEN DayName = 'Saturday'
                                    THEN DateAdd(d, 2, date_date)
                              WHEN DayName = 'Sunday'
                                    THEN DateAdd(d, 1, date_date)
                              END
            WHEN WeekOfMonth = 5
                  THEN CASE
                              WHEN DayName = 'Monday'
                                    THEN date_date
                              WHEN DayName = 'Tuesday'
                                    AND DateAdd(d, 6, date_date) > LastDayOfMonth
                                    THEN LastDayOfMonth
                              WHEN DayName = 'Wednesday'
                                    AND DateAdd(d, 5, date_date) > LastDayOfMonth
                                    THEN LastDayOfMonth
                              WHEN DayName = 'Thursday'
                                    AND DateAdd(d, 4, date_date) > LastDayOfMonth
                                    THEN LastDayOfMonth
                              WHEN DayName = 'Friday'
                                    AND DateAdd(d, 3, date_date) > LastDayOfMonth
                                    THEN LastDayOfMonth
                              WHEN DayName = 'Saturday'
                                    AND DateAdd(d, 2, date_date) > LastDayOfMonth
                                    THEN LastDayOfMonth
                              WHEN DayName = 'Sunday'
                                    AND DateAdd(d, 1, date_date) > LastDayOfMonth
                                    THEN LastDayOfMonth
                              WHEN DayName = 'Tuesday'
                                    THEN DateAdd(d, 6, date_date)
                              WHEN DayName = 'Wednesday'
                                    THEN DateAdd(d, 5, date_date)
                              WHEN DayName = 'Thursday'
                                    THEN DateAdd(d, 4, date_date)
                              WHEN DayName = 'Friday'
                                    THEN DateAdd(d, 3, date_date)
                              WHEN DayName = 'Saturday'
                                    THEN DateAdd(d, 2, date_date)
                              WHEN DayName = 'Sunday'
                                    THEN DateAdd(d, 1, date_date)
                              END
            WHEN WeekOfMonth = 6
                  THEN CASE
                              WHEN Month(DateAdd(d, 6, date_date)) > Month(date_date)
                                    THEN LastDayOfMonth
                              WHEN DayName = 'Tuesday'
                                    AND DateAdd(d, 6, date_date) > LastDayOfMonth
                                    THEN LastDayOfMonth
                              WHEN DayName = 'Wednesday'
                                    AND DateAdd(d, 5, date_date) > LastDayOfMonth
                                    THEN LastDayOfMonth
                              WHEN DayName = 'Thursday'
                                    AND DateAdd(d, 4, date_date) > LastDayOfMonth
                                    THEN LastDayOfMonth
                              WHEN DayName = 'Friday'
                                    AND DateAdd(d, 3, date_date) > LastDayOfMonth
                                    THEN LastDayOfMonth
                              WHEN DayName = 'Saturday'
                                    AND DateAdd(d, 2, date_date) > LastDayOfMonth
                                    THEN LastDayOfMonth
                              WHEN DayName = 'Sunday'
                                    AND DateAdd(d, 1, date_date) > LastDayOfMonth
                                    THEN LastDayOfMonth
                              WHEN DayName = 'Monday'
                                    THEN date_date
                              WHEN DayName = 'Tuesday'
                                    THEN DateAdd(d, 6, date_date)
                              WHEN DayName = 'Wednesday'
                                    THEN DateAdd(d, 5, date_date)
                              WHEN DayName = 'Thursday'
                                    THEN DateAdd(d, 4, date_date)
                              WHEN DayName = 'Friday'
                                    THEN DateAdd(d, 3, date_date)
                              WHEN DayName = 'Saturday'
                                    THEN DateAdd(d, 2, date_date)
                              WHEN DayName = 'Sunday'
                                    THEN DateAdd(d, 1, date_date)
                              END
            END EndDate
FROM [dbo].[DimDate] d1
) jb
where jb.Date_Date = [dbo].[DimDate].Date_Date


select
Date_Date
,WeightValuationWeekStartDate
,WeightValuationWeekEndDate
from  [dbo].[DimDate]


And here's the results:



No comments:

Post a Comment

Babalon