CODEWITHSUNDEEP
sqlsql-serversql-server-2012
mHelpMe
mHelpMe

Reputation: 6668

selecting a date n days ago excluding weekends

I have a table with daily dates starting from 31st December 1999 up to 31st December 2050, excluding weekends.

Say given a particular date, for this example lets use 2019-03-14. I want to pick the date that was 30 days previous (the number of days needs to be flexible as it won't always be 30), ignoring weekends which in this case would be 2019-02-01.

How to do this?

I wrote the query below & it indeed lists 30 days previous to the specified date.

 select top 30 Date 
 from DateDimension
 where IsWeekend = 0 and Date <= '2019-03-14'
 order by Date desc

So I thought I could use the query below to get the correct answer of 2019-02-01

 ;with ds as
 ( 
    select top 30 Date 
    from DateDimension
    where IsWeekend = 0 and Date <= '2019-03-14'
 )
 select min(Date) from ds

However this doesn't work. It returns me the first date in my table, 1999-12-31.

 2019-03-14
 2019-03-13
 2019-03-12
 2019-03-11
 2019-03-08
 2019-03-07
 2019-03-06
 2019-03-05
 2019-03-04
 2019-03-01
 2019-02-28
 2019-02-27
 2019-02-26
 2019-02-25
 2019-02-22
 2019-02-21
 2019-02-20
 2019-02-19
 2019-02-18
 2019-02-15
 2019-02-14
 2019-02-13
 2019-02-12
 2019-02-11
 2019-02-08
 2019-02-07
 2019-02-06
 2019-02-05
 2019-02-04
 2019-02-01

Upvotes: 0

Views: 208

Answers (1)

SQLRaptor
SQLRaptor

Reputation: 681

TOP is meaningless without an ORDER BY, so you could do something like

 ;with ds as
 ( 
    select top 30 Date 
    from DateDimension
    where IsWeekend = 0 and Date <= '2019-03-14'
    order by Date DESC
 )
 select min(Date) from ds;

even better would be to use the ANSI syntax instead of TOP:

select Date 
from DateDimension
where IsWeekend = 0 and Date <= '2019-03-14'
order by Date DESC
OFFSET 30 ROWS FETCH NEXT 1 ROW ONLY;

DISCLAIMER - code not tested since you did not provide DDL and sample data

HTH

Upvotes: 1

Related Questions