StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>YACTES:</p> <pre><code>CREATE TABLE dbo.TestData ( Fee_Item_ID INT PRIMARY KEY, Fee_Basis_ID INT NOT NULL, [Start_Date] DATE NOT NULL, [End_Date] DATE NOT NULL ); CREATE UNIQUE INDEX IX_TestData_FeeBasisID_StartDate@EndDate ON dbo.TestData(Fee_Basis_ID, [Start_Date]) INCLUDE (End_Date); INSERT dbo.TestData VALUES (1,1,'2011-02-01','2011-03-31'), (2,1,'2011-04-01','2011-05-31'), (3,2,'2011-01-01','2011-03-31'), (4,2,'2011-05-01','2011-06-30'), (5,2,'2011-11-01','2011-11-30'), (6,3,'2011-05-01','2011-06-30'); CREATE TABLE dbo.TestData2(Fee_Basis_ID INT PRIMARY KEY); INSERT dbo.TestData2 VALUES (1),(2),(3); DECLARE @Query_Start_Date DATE = '2011-01-01' ,@Query_End_Date DATE = '2011-09-30'; ;WITH FeeRank AS ( SELECT a.Fee_Basis_ID ,a.[Start_Date] ,a.End_Date ,ROW_NUMBER() OVER(PARTITION BY a.Fee_Basis_ID ORDER BY a.[Start_Date]) GroupRowNum ,COUNT(*) OVER(PARTITION BY a.Fee_Basis_ID) GroupCount FROM dbo.TestData a WHERE @Query_Start_Date <= a.[Start_Date] AND a.[End_Date] <= @Query_End_Date ), Scan AS ( SELECT a.Fee_Basis_ID, @Query_Start_Date [Start_Date], DATEADD(DAY,-1,a.[Start_Date]) End_Date FROM FeeRank a WHERE a.GroupRowNum = 1 AND @Query_Start_Date <> a.[Start_Date] UNION ALL SELECT a.Fee_Basis_ID, DATEADD(DAY,1,a.End_Date) b, @Query_End_Date c FROM FeeRank a WHERE a.End_Date <> @Query_End_Date AND a.GroupRowNum = a.GroupCount UNION ALL SELECT crt.Fee_Basis_ID, DATEADD(DAY,1,prev.End_Date), DATEADD(DAY,-1,crt.[Start_Date]) FROM FeeRank prev INNER JOIN FeeRank crt ON prev.Fee_Basis_ID = crt.Fee_Basis_ID AND prev.GroupRowNum + 1 = crt.GroupRowNum WHERE DATEDIFF(DAY,prev.[End_Date],crt.[Start_Date]) <> 1 ) SELECT cte.Fee_Basis_ID, cte.[Start_Date], cte.End_Date FROM Scan cte UNION ALL SELECT b.Fee_Basis_ID, NULL, NULL FROM dbo.TestData2 b WHERE b.Fee_Basis_ID NOT IN (SELECT c.Fee_Basis_ID FROM dbo.TestData c) ORDER BY Fee_Basis_ID; DROP TABLE dbo.TestData; DROP TABLE dbo.TestData2 </code></pre> <p>YACTES: Yet Another CTE Solution</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload