CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-server-2008-r2
PingPing
PingPing

Reputation: 999

T-SQL to select every nth date from column of dates

I have a column of dates. They are all workdays. I would like to generate a list of dates that are 'n' days apart. For example, starting with the most recent date, I want to find the date n days before it, 2n days before it, 3n days before it, etc. I could use a while loop but I wanted to know if I could use SQL set operations instead. Can it be done?

Upvotes: 0

Views: 2164

Answers (4)

Kevin Nguyen
Kevin Nguyen

Reputation: 21

Find the difference between the most_recent_date and dates in your table, then use the modulo function, where n is the interval.

SELECT date
FROM my_table
WHERE mod(most_recent_date - date, n) = 0

Upvotes: 2

Richard Newman
Richard Newman

Reputation: 630

DECLARE @mostRecent datetime2

SELECT @mostRecent = MAX(dateColumn)
FROM table

SELECT columns
FROM table
WHERE (DATEDIFF(day, dateColumn, @mostRecent) % n) = 0

Upvotes: 0

Doug Morrow
Doug Morrow

Reputation: 1366

This is the perfect case for a CTE:

DECLARE @LastDate datetime;
DECLARE @N int;    
DECLARE @NCoefficientMax;

SELECT @N = 1, @NCoefficientMax = 10;

SELECT @LastDate = MyDate
FROM MyTable
ORDER BY MyDate DESC

WITH mycte
AS
(
SELECT DATEADD(dd, @N, @LastDate) AS NextDate, @N AS NCoefficient
UNION ALL
SELECT DATEADD(dd, @N, NextDate), @N + NCoefficient AS NCoefficient
FROM mycte WHERE NCoefficient < @NCoefficientMax 
)

SELECT NextDate FROM mycte

Where @NCoefficientMax is the max coefficient for N.

Upvotes: 1

inon
inon

Reputation: 1772

You can use the dateadd funcion and make select with join to self table.

What that you need to do it - Insert the result to temporary table, with additional column then contain the row_number then order like the result

simple example:

declare @t1 table (d datetime, row int)

insert @t1
select d, row_number()over(order by d)
from T1 
order by d

select T1A.*, datediff(day,T1A.d,T1B.d) as dif
from @t1 as T1A
left join @t1 as T1B on T1A.row = T1B.row-1

Upvotes: 0

Related Questions