September 29, 2020 at 3:45 pm
Ok, I've written a bit of code and it's horrible and looks terribly inefficient and long winded but I'm having some brain fade on how to improve it...
I have a DateTime say today. I have a number of elapsed days after this first date. This is used to calculate a second date by adding the elapsed days to the first date. All good so far. However, If the original date was a Monday, the second date must also be a Monday so I need to calculate an offset value increment to add to the date to move it forward to the next Monday if the second date did not fall on a Monday. If it was a Tuesday then the new date needs to move forwards to a Tuesday if it's not and so on. So basically I'm trying to calculate the offset number which might be zero if the two dates are the same day of the week.
Here is what I've come up with (it works but looks so long winded)... Any suggestions most welcome. Many thanks.
DECLARE @OriginalFirstStartDate DATETIME = GETDATE()
DECLARE @DaysDifference int = 200
DECLARE @OriginalFirstStartDayOfWeek INT = DATEPART(dw, @OriginalFirstStartDate)
DECLARE @NewFirstStartDate DATETIME = DATEADD(DAY, @DaysDifference, @OriginalFirstStartDate)
DECLARE @NewFirstStartDayOfWeek INT = DATEPART(dw, @NewFirstStartDate)
DECLARE @OffsetDays INT
SET @OffsetDays = CASE
WHEN @OriginalFirstStartDayOfWeek = 1 AND @NewFirstStartDayOfWeek = 1 THEN 0
WHEN @OriginalFirstStartDayOfWeek = 1 AND @NewFirstStartDayOfWeek = 2 THEN 6
WHEN @OriginalFirstStartDayOfWeek = 1 AND @NewFirstStartDayOfWeek = 3 THEN 5
WHEN @OriginalFirstStartDayOfWeek = 1 AND @NewFirstStartDayOfWeek = 4 THEN 4
WHEN @OriginalFirstStartDayOfWeek = 1 AND @NewFirstStartDayOfWeek = 5 THEN 3
WHEN @OriginalFirstStartDayOfWeek = 1 AND @NewFirstStartDayOfWeek = 6 THEN 2
WHEN @OriginalFirstStartDayOfWeek = 1 AND @NewFirstStartDayOfWeek = 7 THEN 1
WHEN @OriginalFirstStartDayOfWeek = 2 AND @NewFirstStartDayOfWeek = 1 THEN 1
WHEN @OriginalFirstStartDayOfWeek = 2 AND @NewFirstStartDayOfWeek = 2 THEN 0
WHEN @OriginalFirstStartDayOfWeek = 2 AND @NewFirstStartDayOfWeek = 3 THEN 6
WHEN @OriginalFirstStartDayOfWeek = 2 AND @NewFirstStartDayOfWeek = 4 THEN 5
WHEN @OriginalFirstStartDayOfWeek = 2 AND @NewFirstStartDayOfWeek = 5 THEN 4
WHEN @OriginalFirstStartDayOfWeek = 2 AND @NewFirstStartDayOfWeek = 6 THEN 3
WHEN @OriginalFirstStartDayOfWeek = 2 AND @NewFirstStartDayOfWeek = 7 THEN 2
WHEN @OriginalFirstStartDayOfWeek = 3 AND @NewFirstStartDayOfWeek = 1 THEN 2
WHEN @OriginalFirstStartDayOfWeek = 3 AND @NewFirstStartDayOfWeek = 2 THEN 1
WHEN @OriginalFirstStartDayOfWeek = 3 AND @NewFirstStartDayOfWeek = 3 THEN 0
WHEN @OriginalFirstStartDayOfWeek = 3 AND @NewFirstStartDayOfWeek = 4 THEN 6
WHEN @OriginalFirstStartDayOfWeek = 3 AND @NewFirstStartDayOfWeek = 5 THEN 5
WHEN @OriginalFirstStartDayOfWeek = 3 AND @NewFirstStartDayOfWeek = 6 THEN 4
WHEN @OriginalFirstStartDayOfWeek = 3 AND @NewFirstStartDayOfWeek = 7 THEN 3
WHEN @OriginalFirstStartDayOfWeek = 4 AND @NewFirstStartDayOfWeek = 1 THEN 3
WHEN @OriginalFirstStartDayOfWeek = 4 AND @NewFirstStartDayOfWeek = 2 THEN 2
WHEN @OriginalFirstStartDayOfWeek = 4 AND @NewFirstStartDayOfWeek = 3 THEN 1
WHEN @OriginalFirstStartDayOfWeek = 4 AND @NewFirstStartDayOfWeek = 4 THEN 0
WHEN @OriginalFirstStartDayOfWeek = 4 AND @NewFirstStartDayOfWeek = 5 THEN 6
WHEN @OriginalFirstStartDayOfWeek = 4 AND @NewFirstStartDayOfWeek = 6 THEN 5
WHEN @OriginalFirstStartDayOfWeek = 4 AND @NewFirstStartDayOfWeek = 7 THEN 4
WHEN @OriginalFirstStartDayOfWeek = 5 AND @NewFirstStartDayOfWeek = 1 THEN 4
WHEN @OriginalFirstStartDayOfWeek = 5 AND @NewFirstStartDayOfWeek = 2 THEN 3
WHEN @OriginalFirstStartDayOfWeek = 5 AND @NewFirstStartDayOfWeek = 3 THEN 2
WHEN @OriginalFirstStartDayOfWeek = 5 AND @NewFirstStartDayOfWeek = 4 THEN 1
WHEN @OriginalFirstStartDayOfWeek = 5 AND @NewFirstStartDayOfWeek = 5 THEN 0
WHEN @OriginalFirstStartDayOfWeek = 5 AND @NewFirstStartDayOfWeek = 6 THEN 6
WHEN @OriginalFirstStartDayOfWeek = 5 AND @NewFirstStartDayOfWeek = 7 THEN 5
WHEN @OriginalFirstStartDayOfWeek = 6 AND @NewFirstStartDayOfWeek = 1 THEN 5
WHEN @OriginalFirstStartDayOfWeek = 6 AND @NewFirstStartDayOfWeek = 2 THEN 4
WHEN @OriginalFirstStartDayOfWeek = 6 AND @NewFirstStartDayOfWeek = 3 THEN 3
WHEN @OriginalFirstStartDayOfWeek = 6 AND @NewFirstStartDayOfWeek = 4 THEN 2
WHEN @OriginalFirstStartDayOfWeek = 6 AND @NewFirstStartDayOfWeek = 5 THEN 1
WHEN @OriginalFirstStartDayOfWeek = 6 AND @NewFirstStartDayOfWeek = 6 THEN 0
WHEN @OriginalFirstStartDayOfWeek = 6 AND @NewFirstStartDayOfWeek = 7 THEN 6
WHEN @OriginalFirstStartDayOfWeek = 7 AND @NewFirstStartDayOfWeek = 1 THEN 6
WHEN @OriginalFirstStartDayOfWeek = 7 AND @NewFirstStartDayOfWeek = 2 THEN 5
WHEN @OriginalFirstStartDayOfWeek = 7 AND @NewFirstStartDayOfWeek = 3 THEN 4
WHEN @OriginalFirstStartDayOfWeek = 7 AND @NewFirstStartDayOfWeek = 4 THEN 3
WHEN @OriginalFirstStartDayOfWeek = 7 AND @NewFirstStartDayOfWeek = 5 THEN 2
WHEN @OriginalFirstStartDayOfWeek = 7 AND @NewFirstStartDayOfWeek = 6 THEN 1
WHEN @OriginalFirstStartDayOfWeek = 7 AND @NewFirstStartDayOfWeek = 7 THEN 0
END
SELECT(DATEADD(DAY, @DaysDifference + @OffsetDays, @OriginalFirstStartDate)) AS [NewDate]
September 29, 2020 at 3:55 pm
I'm wondering if you can just replace the stack of case statements with one:
case
when @OriginalFirstStartDayOfWeek < @NewFirstStartDayOfWeek then @OriginalFirstStartDayOfWeek + 7 - @NewFirstStartDayOfWeek
else @OriginalFirstStartDayOfWeek - @NewFirstStartDayOfWeek
end
September 29, 2020 at 4:13 pm
That did it - seems very obvious now and I feel a bit foolish!
Many thanks for taking the time to respond.
September 29, 2020 at 4:22 pm
Or, if for fun we're trying to minimize characters used:
DECLARE @OriginalFirstStartDate DATETIME = GETDATE();
DECLARE @DaysDifference int = 200;
SELECT DATEADD(DAY,((@DaysDifference+7)/7)*7,@OriginalFirstStartDate) AS NewDate;
Cheers!
Edit: Actually that doesn't work (bonus points for pointing out why), it's what I get for copy/pasting snippets from other similar problems in the past, without ample coffee!
I'm going to have to take my coffee break and fix it in a bit 🙂
Edit 2: Ok, so as I'm sure you noticed, the code snippet above is a solution for ALWAYS going to the next occurrence of the day of the week on which you started, even if the date after addition is already that day of the week.
For this problem, I think this is the fewest characters, but it's been a while since I've gotten to try something silly like this, so not 100% certain:
DECLARE @OriginalFirstStartDate DATETIME = GETDATE();
DECLARE @DaysDifference int = 210;
SELECT DATEADD(DAY,CEILING((@DaysDifference/7.0))*7,@OriginalFirstStartDate) AS NewDate;
September 29, 2020 at 4:24 pm
Nice Jacob. thanks for the response.
September 29, 2020 at 9:04 pm
SELECT DATEADD(DAY, (@DaysDifference + 6) / 7 * 7, @OriginalFirstStartDate)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 29, 2020 at 9:18 pm
Heh, nice, Scott. Further evidence my brain was not fully functioning this morning.
I had convinced myself that option didn't work, but for the life of me can't remember now what drivel I told myself.
Cheers!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply