daily pastebin goal
63%
SHARE
TWEET

Untitled

a guest Feb 20th, 2019 54 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Declare @tbl_Data Table(Dates Date,WorkDayCount Int)
  2.  
  3. Insert Into @tbl_Data Select '2012-08-25',1
  4. Insert Into @tbl_Data Select '2012-08-26',1
  5. Insert Into @tbl_Data Select '2012-08-27',1
  6. Insert Into @tbl_Data Select '2012-08-28',1
  7. Insert Into @tbl_Data Select '2012-08-29',1
  8. Insert Into @tbl_Data Select '2012-08-30',1
  9. Insert Into @tbl_Data Select '2012-08-31',0
  10. Insert Into @tbl_Data Select '2012-09-01',1
  11.  
  12. Declare @InputData Date = '2012-09-01'
  13.  
  14. ;WITH CTE AS
  15. (
  16.     SELECT Sequence = ROW_NUMBER() OVER(ORDER BY Dates DESC),*
  17.     FROM @tbl_Data
  18.     WHERE  Dates < @InputData
  19. )
  20. ,CTE2 AS
  21. (
  22.     SELECT
  23.         Sequence = ROW_NUMBER() OVER(ORDER BY Dates DESC)
  24.         ,Dates
  25.         ,WorkDayCount
  26.     FROM
  27.     (
  28.         SELECT * FROM CTE WHERE Sequence  = ((SELECT TOP 1 Sequence FROM CTE WHERE WorkDayCount = 1)-1)
  29.         UNION  
  30.         SELECT TOP 2 * FROM CTE WHERE WorkDayCount = 1
  31.     )X
  32. )
  33. SELECT TOP 1
  34.             Date = CASE WHEN  Sequence = 1 AND  WorkDayCount = 0 THEN (SELECT Dates FROM CTE2 WHERE  Sequence = 3)
  35.                     WHEN  Sequence = 1 AND  WorkDayCount = 1 THEN (SELECT Dates FROM CTE2 WHERE  Sequence = 2)
  36.             END
  37. FROM CTE2
  38.    
  39. SELECT MIN(DATE_COL)
  40. FROM  (
  41.         SELECT  TOP 2  DATE_COL
  42.         FROM TBL_DATE
  43.         WHERE DATE_COL<='2012-09-01'
  44.         AND WORKDAYCOUNT =1)A
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top