CODEWITHSUNDEEP
sqlsql-server
Etienne
Etienne

Reputation: 420

Select working days from a calendar in a table

I have a table with a calendar which look like this in SQL Server

Date        WorkingDay
20200514    1
20200515    1
20200516    0
20200517    0
20200518    1
20200519    1
20200520    1
20200521    0
20200522    1

I am trying to select the third working day from a specific date. If I start the 20200514, result must be the 20200518. I try with a query like this but I do not have the date, only a list of result

select top 3 *
from tmp_workingdays
where workingday = 1 and date >= 20200514
order by date asc

How can I select only the date?

Upvotes: 0

Views: 239

Answers (3)

MattM
MattM

Reputation: 384

DECLARE @date AS DATE = '2020-05-14' ;
WITH cte_WorkingDates AS
(
    SELECT  [Date]
    FROM    tmp_workingdays
    WHERE   WorkingDay = 1
)
SELECT      [Date]
FROM        cte_WorkingDates
WHERE       [Date] >= @date
ORDER BY    [Date] ASC
OFFSET      2 ROWS FETCH NEXT 1 ROWS ONLY ;

Upvotes: 2

zealous
zealous

Reputation: 7503

Try the following, here is the demo.

select
    date
from
(
    select 
        *,
        row_number() over (order by date) as rnk
    from tmp_workingdays 
    where workingday=1 
    and date >= 20200514
) val
where rnk = 3

output:

|  Date    |  
------------
|2020-05-18|

Upvotes: 2

juergen d
juergen d

Reputation: 204766

select date 
from tmp_workingdays 
where workingday = 1 
  and date >= 20200514
order by date asc
OFFSET 2 ROWS 
FETCH NEXT 1 ROWS ONLY

Upvotes: 1

Related Questions