CODEWITHSUNDEEP
sqlsql-servert-sql
user667430
user667430

Reputation: 1547

TSQL Calendar table, count 10 workings days from date

I have a calendar table which stores rows of dates and an indication of wether that date is a holiday or working day.

How can I select the date that is 5 working days into the future from the 2014-12-22 so the selected date will be 2014-12-31

Date_Id     Date_Date   Date_JDE    Is_WorkingDay
20141222    2014-12-22  114356      1
20141223    2014-12-23  114357      1
20141224    2014-12-24  114358      1
20141225    2014-12-25  114359      0
20141226    2014-12-26  114360      0
20141227    2014-12-27  114361      0
20141228    2014-12-28  114362      0
20141229    2014-12-29  114363      1
20141230    2014-12-30  114364      1
20141231    2014-12-31  114365      1

Upvotes: 0

Views: 91

Answers (4)

kalaivanan
kalaivanan

Reputation: 1

You can Try Like This:

with calender as 
(select top 5 date_id,date_date,date_jde from calender 
where date_date>='2014-12-22' and  is_workingday='1)calender 
select top 1 * from calender order by date_date desc

Upvotes: 0

Dgan
Dgan

Reputation: 10295

with Derived Tables

select * from
(
SELECT Date_Date,  ROW_NUMBER() OVER (ORDER BY Date_Date) as 'RowNum' 
 FROM Table_calendar  
 WHERE Is_WorkingDay = 1 
 and CAST(Date_Date as DATE) > '2014-12-22'
)d
where d.RowNum=5

Upvotes: 0

Christian Phillips
Christian Phillips

Reputation: 18769

You can use a CTE like this...

;WITH cteWorkingDays AS 
(
 SELECT Date_Date,  ROW_NUMBER() OVER (ORDER BY Date_Date) as 'rowNum' 
 FROM TableName 
 WHERE Is_WorkingDay = 1 
 and Date_Date > '20141222' -- this will be a param I suppose
)

SELECT Date_Date 
FROM cteWorkingDays 
WHERE rowNum = 5 -- this can be changed to 10 (title value

This is hand typed, but it will be close enough.

EDIT: Based on comment.

Declare @DateToUse TYPE  --  unsure if you're using a string or a date type.
SELECT @DateToUse = Date_Date 
    FROM cteWorkingDays 
    WHERE rowNum = 5

Upvotes: 2

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

...;

WITH DatesCTE AS
(
    SELECT Date_Id,
           Date_Date,
           Date_JDE,
           Is_WorkingDay,
           ROW_NUMBER() OVER(ORDER BY Date_Date) AS rn
    FROM   DatesTable
    WHERE  Is_WorkingDay = 1
           AND Date_Date > '2014-12-22'
)
SELECT Date_Date
FROM   DatesCTE
WHERE  rn = 5

SQL Fiddle Demo

Upvotes: 1

Related Questions