Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Declare @tbl_Data Table(Dates Date,WorkDayCount Int)
- Insert Into @tbl_Data Select '2012-08-25',1
- Insert Into @tbl_Data Select '2012-08-26',1
- Insert Into @tbl_Data Select '2012-08-27',1
- Insert Into @tbl_Data Select '2012-08-28',1
- Insert Into @tbl_Data Select '2012-08-29',1
- Insert Into @tbl_Data Select '2012-08-30',1
- Insert Into @tbl_Data Select '2012-08-31',0
- Insert Into @tbl_Data Select '2012-09-01',1
- Declare @InputData Date = '2012-09-01'
- ;WITH CTE AS
- (
- SELECT Sequence = ROW_NUMBER() OVER(ORDER BY Dates DESC),*
- FROM @tbl_Data
- WHERE Dates < @InputData
- )
- ,CTE2 AS
- (
- SELECT
- Sequence = ROW_NUMBER() OVER(ORDER BY Dates DESC)
- ,Dates
- ,WorkDayCount
- FROM
- (
- SELECT * FROM CTE WHERE Sequence = ((SELECT TOP 1 Sequence FROM CTE WHERE WorkDayCount = 1)-1)
- UNION
- SELECT TOP 2 * FROM CTE WHERE WorkDayCount = 1
- )X
- )
- SELECT TOP 1
- Date = CASE WHEN Sequence = 1 AND WorkDayCount = 0 THEN (SELECT Dates FROM CTE2 WHERE Sequence = 3)
- WHEN Sequence = 1 AND WorkDayCount = 1 THEN (SELECT Dates FROM CTE2 WHERE Sequence = 2)
- END
- FROM CTE2
- SELECT MIN(DATE_COL)
- FROM (
- SELECT TOP 2 DATE_COL
- FROM TBL_DATE
- WHERE DATE_COL<='2012-09-01'
- AND WORKDAYCOUNT =1)A
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement