CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
HeavenCore
HeavenCore

Reputation: 7683

Determine next date (including current date) from a date in the past + cycle

In SQL Server - how can one determine the next date (i.e. today or in the future) from a given date + cycle (in days).

For example:

2nd April 2018 + 7 day cycle would have a NextDate of 23rd April (at the time of writing this question on the 20th April).

I'm looking for a math / mod solution to this as opposed to a calendar table as the data in question is vast (millions of rows).

Example / What i've tried so far:

CREATE TABLE #temptable
(
    [OrderDate] DATE,
    [Cycle] SMALLINT
);
INSERT INTO #temptable
VALUES
     ( N'2018-03-26T00:00:00', 7 ),
     ( N'2018-03-26T00:00:00', 14 ),
     ( N'2018-03-26T00:00:00', 28 ),
     ( N'2018-03-26T00:00:00', 56 ),
     ( N'2018-04-02T00:00:00', 7 ),
     ( N'2018-04-02T00:00:00', 2 ),
     ( N'2018-04-09T00:00:00', 7 ),
     ( N'2018-04-16T00:00:00', 7 );

SELECT   OrderDate,
         Cycle,
         DATEDIFF(DAY, OrderDate, GETDATE()) / Cycle AS Cycles,
         DATEDIFF(DAY, OrderDate, GETDATE()) % Cycle AS CyclesRemainder,
         NULL AS NextDate
FROM     #temptable
ORDER BY OrderDate;

DROP TABLE #temptable;

Upvotes: 1

Views: 194

Answers (1)

Andrew
Andrew

Reputation: 27294

I think you are close, you have the number of cycles which have passed since the start date, but have this currently expressed as an integer, you want that as a decimal value that you automatically round up.

SELECT   OrderDate,
         Cycle,
         DATEDIFF(DAY, OrderDate, GETDATE()) / convert(decimal(10,2),Cycle) AS Cycles,
         DATEDIFF(DAY, OrderDate, GETDATE()) % Cycle AS CyclesRemainder,
         dateadd(d, cycle * ceiling(DATEDIFF(DAY, OrderDate, GETDATE()) / convert(decimal(10,2),Cycle)), orderdate) AS NextDate
FROM     temptable
ORDER BY OrderDate;

http://www.sqlfiddle.com/#!18/2f4db/5

The edge case when that next cycle is today, you can see that from the remainder part you calculated. That is where ceiling is protecting you since its always rounding up. If the cycle is a whole number it will just stay as it is, when you are 3.5 cycles through, it will round to 4 and you add 4 * cycles to get your date.

The 2018-04-02 with 7 / 2 day cycles exposes that edge case nicely, the results from the query are then 2018-04-23 and 2018-04-20 respectively (For future readers purposes, this was calculated on the 20th)

Upvotes: 2

Related Questions