Created
July 2, 2020 19:34
-
-
Save jingyang-li/a8c1ea0bfeb5801cca90a6c77a2bb3cc to your computer and use it in GitHub Desktop.
querySample2
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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