sqlserver get records count repeating

Question

I have one doubt in sql server get records based on installments Table :productdetails

CREATE TABLE [dbo].[productdetails](
    [productid] [int] NULL,
    [Productrstartdate] [date] NULL,
    [Productenddate] [date] NULL,
    [EMIInstallment] [int] NULL
) ON [PRIMARY]
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)
GO
INSERT [dbo].[productdetails] ([productid], [Productrstartdate], [Productenddate], [EMIInstallment]) VALUES (2, CAST(N'2020-02-10' AS Date), CAST(N'2021-02-10' AS Date), 2)
GO
INSERT [dbo].[productdetails] ([productid], [Productrstartdate], [Productenddate], [EMIInstallment]) VALUES (3, CAST(N'2019-01-10' AS Date), CAST(N'2019-01-10' AS Date), 1)
GO
INSERT [dbo].[productdetails] ([productid], [Productrstartdate], [Productenddate], [EMIInstallment]) VALUES (4, CAST(N'2019-01-18' AS Date), CAST(N'2021-01-18' AS Date), 3)
GO

based on above data i want output like below

Productid |Installmentdate  |noofinstallmentcount
1         |2020-10-02       |1
1         |2021-10-02       |2
1         |2022-10-02       |3
1         |2023-10-02       |4
1         |2024-10-02       |5
2         |2020-02-10       |1
2         |2021-02-10       |2
3         |2019-01-10       |1
4         |2019-01-18       |1
4         |2020-01-18       |2
4         |2021-01-18       |3

i tried like below :

DECLARE @MINDATE DATE='2019-01-18'
DECLARE @COUNT INT=10
dECLARE @MAXDATE DATE='2024-10-02'

;WITH ABC
AS 
(
SELECT  productid ,@MINDATE CalendarDate ,1 as id from [dbo].[productdetails]
UNION ALL
SELECT a.productid ,DATEADD(YEAR,1,a.Productrstartdate ), 1 FROM  [dbo].[productdetails] a
join  [dbo].[productdetails]  b on a.productid=b.productid
 WHERE   @MINDATE <@MAXDATE     ) 
 SELECT * FROM ABC

above query not given expected output

could you please tell me how to write query to achive this task in sql server .

user_2029983 · Answer

You want a tally here. If you're not going to have a value (much) higher than 10, then just a VALUES clause will work:

SELECT pd.productid,
       DATEADD(YEAR, V.I, pd.Productrstartdate) AS Installmentdate,
       V.I + 1 AS noofinstallmentcount
FROM dbo.productdetails pd
     JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))V(I) ON pd.EMIInstallment > V.I;

If you need bigger numbers, just use a bigger tally:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1, N N2, N N3) --1000 rows
SELECT pd.productid,
       DATEADD(YEAR, T.I, pd.Productrstartdate) AS Installmentdate,
       T.I + 1 AS noofinstallmentcount
FROM dbo.productdetails pd
     JOIN Tally T ON pd.EMIInstallment > T.I;

user_13730920 · Answer

You could create a temp table that mimics the actual productdetails table, then iterate through it, creating an output table that lists the product id with each installment date. Once you have that, you can use ROW_NUMBER() to count the installment numbers. Try this:

SELECT *
INTO #productdetails
FROM productdetails

CREATE TABLE #output (
    productid int null
    ,installmentdate date null)

WHILE (SELECT COUNT(*) FROM #productdetails) > 0
BEGIN
    DECLARE @yr int = 0
    WHILE (SELECT TOP 1 DATEDIFF(year, DATEADD(year, @yr, Productrstartdate), productenddate) FROM #productdetails) >= 0
    BEGIN
        INSERT INTO #output (
            productid
            ,installmentdate)
        SELECT top 1
            productid
            ,DATEADD(year, @yr, Productrstartdate) installmentdate
        FROM #productdetails

        SET @yr = @yr + 1
    END

    DELETE TOP (1) FROM #productdetails
END

SELECT *
    ,ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY InstallmentDate asc) NumberOfInstallmentCount
FROM #output

DROP TABLE #output, #productdetails

sqlserver get records count repeating

2 Answers