Eight days a week

I was recently tasked with reworking a system responsible for something I loath to do in SQL: dates. It always seemed like there was a huge hole in terms of built-in functionality for dates on SQL server. It took until 2012 to get DATEFROMPARTS. How is that not like, the second function you write for the date class?

The problem


The system I was reworking is responsible for creating a a series rows to represents a continuous sequence of weeks between date A and date B. The returned rows must be whole weeks, even if the week extends outside of the date range. (So, no half weeks if the week started before Date A). The start/end of the week is configurable for different clients. In addition, the weeks are associated with a month so that data can be summarize by weeks or ‘months’ as defined by those weeks. Where this gets interesting is on weeks that span two months. In that situation there were 4 possible options that we had that are configurable to client preference.

  • Associate the week with whichever month it has more days in
  • Associate the week with the month it starts/ends in
  • Associate the week with whichever month a certain day of the week is in
  • Break the weeks into two separate records for each month

Someone set us up the data


The solution I came up with is fairly straight forward but uses some decently clever math to avoid most of the actual finagling with the date class.

First, the creation of a temporary variable to hold the records and setting up the date records. I pull every date between date A and date B that is on the day of the week that is configured in the system. It happens to denote the day of the week of the end of the week, so I subtract 7 days from that date to get the corresponding beginning of the week. This will give records that represent a continuous series of weeks.


declare @EndOfWeekDayOfWeek as int, @DateA as Date, @DateB as Date; --This will be set by parameters or configurations

declare @WeekRecords table
(
WeekStartDate date,
WeekEndDate date,
AssociatedMonth int,
AssociatedYear int
)

with [Days] as (
select @DateA as [date], 1 as DayNum
union all
select DateAdd(day, 1, [date]) as [Date], DayNum + 1 as DayNum
from [Days]
where [Date] < DATEADD(day, 7, @DateB) --We overshoot the date range a bit because dateB might be during in the middle of the week so we have to pull an additional prior week ) insert into @WeekRecords select dateadd(day, -6, [date]), [date] from [Days] where Datepart(wk, [date]) = @EndOfWeekDayOfWeek option (MAXRECURSION 1000)

Next, I update the dates to associate them with months.

For the configuration to simply associate the week with the month it beginnings or ends in, this is straight forward. Just grab the month and year number from the start or end date.

--Beginning of week
update @WeekRecords set AssociatedMonth = month(WeekStartDate), AssociatedYear = year(WeekStartDate)

--end of week
update @WeekRecords set AssociatedMonth = month(WeekEndDate), AssociatedYear = year(WeekEndDate)

For the configuration to divide the weeks into two weeks, one for each month, it is also fairly straight forward. First, find all the weeks that start and end in different months, e.g. month(WeekStartDate) <> month(WeekEndDate), and add new records to represent the week that ranges from the 1st of the month to the end date of the original record. Next, update all the records where the months still don't match (which will exclude the new records) to end on the end of the month they start in (which there is a convenient function for) and at the same time, update everything to associate it with the month it starts in (which is guaranteed to be the correct month now that we added the new set of records that start on the first of the next month).


insert into @WeekRecords (WeekStartDate, WeekEndDate, AssociatedMonth, AssociatedYear)
select datefromparts(year(WeekEndDate), month(WeekEndDate), 1), WeekEndDate, month(WeekEndDate), year(WeekEndDate)
from @WeekRecords where month(WeekStartDate) <> month(WeekEndDate)

update @WeekRecords set WeekEndDate = case when month(WeekStartDate) <> month(WeekEndDate) then EOMONTH(WeekStartDate) else WeekEndDate end, AssociatedMonth = month(WeekStartDate),
AssociatedYear = year(WeekStartDate)

For the configuration where we associate the week with the month the week has more days in, we can do something clever with the day of the month. For the weeks that startle a month, the day of the month of the ending date is the number of days that the week has in the ending month. If this is 3 or less then the week has 4 or more days in the starting month and is associate with that month, otherwise the opposite is true.

update @WeekRecords set AssociatedMonth = case when day(WeekEndDate) > 3 then month(WeekEndDate) else month(WeekStartDate) end,
AssociatedYear = case when day(WeekEndDate) > 3 then year(WeekEndDate) else year(WeekStartDate) end

The case where we associate the week with the month that a specific day of the week is in is the most complex, but can be done efficiently by using some clever math. To account for the fact that the week could be split so the later day of the week is before an earlier day of the week, like if the week ends on Tuesday and we are looking at Thursday which is prior to Tuesday, I subtract the target day of the week from the end day of the week. This gives us a range that uniformly increase up to the value of the end of week day.

We can get the day of the week that the first of the month was on by taking a known day of the week, in this case the end of our week's day, and subtracting the day number of the month from it, thus counting back the days of the week to the day of the week of the first. Once I have that, I know that if the target day of the week was before that date then the target day of the week happened before the first and was thus in the starting month. Both day of the week values, the target and the calculated value, will both be adjusted to extend from a possible negative value representing the prior calendar week days of the week to the end of the week day of the week.


Declare @AdjustTargetDayOfWeek as int = case when @EndOfWeekDayOfWeek < @useClinicalDay then @EndOfWeekDayOfWeek - @useClinicalDay + 1 else @useClinicalDay end update @WeekRecords set AssociatedMonth = case when month(WeekStartDate) <> month(WeekEndDate) and (@EndOfWeekDayOfWeek - (day(WeekEndDate) - 1) > @AdjustTargetDayOfWeek) then month(WeekStartDate) else month(WeekEndDate) end,
AssociatedYear = case when month(WeekStartDate) <> month(WeekEndDate) and (@EndOfWeekDayOfWeek - (day(WeekEndDate) - 1) > @AdjustTargetDayOfWeek) then year(WeekStartDate) else year(WeekEndDate) end

Finally, we have to trim out weeks that we potentially created that happen entirely before Date A or after Date B.


delete from @WeekRecords where WeekEndDate < @DateA or WeekStartDate > @DateB

Here's the entire code block:


declare @EndOfWeekDayOfWeek as int, @DateA as Date, @DateB as Date; --This will be set by parameters or configurations

declare @WeekRecords table
(
WeekStartDate date,
WeekEndDate date,
AssociatedMonth int,
AssociatedYear int
)

with [Days] as (
select @DateA as [date], 1 as DayNum
union all
select DateAdd(day, 1, [date]) as [Date], DayNum + 1 as DayNum
from [Days]
where [Date] < DATEADD(day, 7, @DateB) --We overshoot the date range a bit because dateB might be during in the middle of the week so we have to pull an additional prior week ) insert into @WeekRecords select dateadd(day, -6, [date]), [date] from [Days] where Datepart(wk, [date]) = @EndOfWeekDayOfWeek option (MAXRECURSION 1000) if @ConfigSetting = 'Split Week' begin insert into @WeekRecords (WeekStartDate, WeekEndDate, AssociatedMonth, AssociatedYear) select datefromparts(year(WeekEndDate), month(WeekEndDate), 1), WeekEndDate, month(WeekEndDate), year(WeekEndDate) from @WeekRecords where month(WeekStartDate) <> month(WeekEndDate)

update @WeekRecords set WeekEndDate = case when month(WeekStartDate) <> month(WeekEndDate) then EOMONTH(WeekStartDate) else WeekEndDate end, AssociatedMonth = month(WeekStartDate), AssociatedYear = year(WeekStartDate)
end
else if @ConfigSetting = 'Start Month'
begin
update @WeekRecords set AssociatedMonth = month(WeekStartDate), AssociatedYear = year(WeekStartDate)
end
else if @ConfigSetting = 'End Month'
begin
update @WeekRecords set AssociatedMonth = month(WeekEndDate), AssociatedYear = year(WeekEndDate)
end
else if @ConfigSetting = 'Target Day of Week'
begin
Declare @AdjustTargetDayOfWeek as int = case when @EndOfWeekDayOfWeek < @useClinicalDay then @EndOfWeekDayOfWeek - @useClinicalDay + 1 else @useClinicalDay end update @WeekRecords set AssociatedMonth = case when month(WeekStartDate) <> month(WeekEndDate) and (@EndOfWeekDayOfWeek - (day(WeekEndDate) - 1) > @AdjustTargetDayOfWeek) then month(WeekStartDate) else month(WeekEndDate) end,
AssociatedYear = case when month(WeekStartDate) <> month(WeekEndDate) and (@EndOfWeekDayOfWeek - (day(WeekEndDate) - 1) > @AdjustTargetDayOfWeek) then year(WeekStartDate) else year(WeekEndDate) end
end
else
begin
update @WeekRecords set AssociatedMonth = case when day(WeekEndDate) > 3 then month(WeekEndDate) else month(WeekStartDate) end,
AssociatedYear = case when day(WeekEndDate) > 3 then year(WeekEndDate) else year(WeekStartDate) end
end

delete from @WeekRecords where WeekEndDate < @DateA or WeekStartDate > @DateB --Trim the excess weeks

select WeekStartDate, WeekEndDate, AssociatedMonth, AssociatedYear
from @WeekRecords
order by WeekStartDate desc