Skip to content

Instantly share code, notes, and snippets.

@jingyang-li
Created July 2, 2020 19:34
Show Gist options
  • Save jingyang-li/a8c1ea0bfeb5801cca90a6c77a2bb3cc to your computer and use it in GitHub Desktop.
Save jingyang-li/a8c1ea0bfeb5801cca90a6c77a2bb3cc to your computer and use it in GitHub Desktop.
querySample2
CREATE TABLE [dbo].[productdetails](
[productid] [int] NULL,
[Productrstartdate] [date] NULL,
[Productenddate] [date] NULL,
[EMIInstallment] [int] NULL
)
GO
INSERT [dbo].[productdetails] ([productid], [Productrstartdate], [Productenddate], [EMIInstallment])
VALUES (1, CAST(N'2020-10-02' AS Date), CAST(N'2024-10-02' AS Date), 5)
,(2, CAST(N'2020-02-10' AS Date), CAST(N'2021-02-10' AS Date), 2)
,(3, CAST(N'2019-01-10' AS Date), CAST(N'2019-01-10' AS Date), 1)
,(4, CAST(N'2019-01-18' AS Date), CAST(N'2021-01-18' AS Date), 3)
GO
---Cross Apply with a number table
-- Create Numbers cte
;WITH Num1 (num) AS (
SELECT 1 as num
UNION ALL SELECT num+1
FROM Num1 Where num<101),
Num2 (num) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (num) AS (SELECT ROW_NUMBER() OVER(ORDER BY num) FROM Num2)
Select a.productid
, DATEADD(YEAR,num-1,a.[Productrstartdate] ) CalendarDate
, num as noofinstallmentcount
from [productdetails] a
cross apply (select num from nums Where num<60) d
Where DATEADD(YEAR,num-1,a.[Productrstartdate] )<= a.[Productenddate]
Order by a.productid,num
---Cross JOIN with a number table
-- Create Numbers cte
;WITH Num1 (num) AS (
SELECT 1 as num
UNION ALL SELECT num+1
FROM Num1 Where num<101),
Num2 (num) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (num) AS (SELECT ROW_NUMBER() OVER(ORDER BY num) FROM Num2)
Select a.productid
, DATEADD(YEAR,num-1,a.[Productrstartdate] ) CalendarDate
, num as noofinstallmentcount
from [productdetails] a, nums
Where num<60 and DATEADD(YEAR,num-1,a.[Productrstartdate] )<= a.[Productenddate]
Order by a.productid,num
--Recursive
;WITH ABC
AS
(
SELECT productid ,[Productrstartdate] CalendarDate ,[Productenddate],
1 as lvl from [dbo].[productdetails]
UNION ALL
SELECT a.productid ,DATEADD(YEAR,1,b.CalendarDate ) CalendarDate, b.[Productenddate], lvl + 1
FROM [dbo].[productdetails] a join ABC b on a.productid=b.productid
WHERE b.CalendarDate <a.[Productenddate] )
SELECT productid, CalendarDate, lvl as noofinstallmentcount
FROM ABC
order by productid
drop table [productdetails]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment