locked
sql server help RRS feed

  • 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 .
    Thursday, July 2, 2020 3:57 PM

Answers

  • 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]

    • Proposed as answer by Echo Liuz Friday, July 3, 2020 3:17 AM
    • Marked as answer by baluram321 Friday, July 3, 2020 5:28 AM
    Thursday, July 2, 2020 7:58 PM

All replies

  • 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
     
    SELECT  productid   ,
    dateadd(year,n,[Productrstartdate])  Installmentdate,  
    n as noofinstallmentcount  
    from [dbo].[productdetails]
    cross apply (values(1),(2),(3),(4),(5) ) d(n)
    
    where n<=[EMIInstallment]
    
    
    drop table [productdetails]

    Thursday, July 2, 2020 4:14 PM
  • How does ProductEndDate come into picture?

    Also, instead of using CROSS APLLY technique shown in other post you can simply join with dbo.Numbers assuming you do have such table using condition EMIInstallment < = Numbers.number.

    Otherwise it's the same query as Jingyang shown.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, July 2, 2020 4:33 PM
  • hi cross apply values is working upto 5 values. if installment count have more than 50 then this query is not working.can you please help without using crossapply to achieve this task

    Thursday, July 2, 2020 5:40 PM
  • Assuming you have numbers table or create one on the fly:

    SELECT  p.productid   ,
    dateadd(year,n.number,p.[Productrstartdate])  p.Installmentdate,  
    n.number as noofinstallmentcount  
    from [dbo].[productdetails] p
    inner join dbo.Numbers n on n.number<=p.[EMIInstallment]

    https://www.mssqltips.com/sqlservertip/4176/the-sql-server-numbers-table-explained--part-1/

    It has a script to create the table right at the top of the article.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Thursday, July 2, 2020 5:57 PM
    Thursday, July 2, 2020 5:56 PM
  • 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]

    • Proposed as answer by Echo Liuz Friday, July 3, 2020 3:17 AM
    • Marked as answer by baluram321 Friday, July 3, 2020 5:28 AM
    Thursday, July 2, 2020 7:58 PM
  • I have one doubt in sql server

    get records based on installments

    Table :product_details

    Please get a good book on SQL and relational databases. You are making very fundamental mistakes. You don't know the difference between a row and a record so use the wrong terms.. By definition, not as an option, a table must have a key. But all the columns can be null, so there is no possible way for this to be a table. Why is there no constraint between the start and end dates? Why do you insert rows one at a time, as if you were reading punchcards in the 1960s?

    CREATE TABLE Product_Schedule

    (product_gtin CHAR(15) NOT NULL PRIMARY KEY,

    product_start_date DATE NOT NULL,

    product_end_date DATE NOT NULL,

    CHECK (product_start_date <= product_end_date),

    emi_installment_cnt INTEGER NOT NULL

    CHECK (emi_installment_cnt > 0)

    );

    Since you didn't bother to tell us about the keys or anything else, I'm going to guess that the product identifier is the key in this table. You probably don't know what a global trade item number (GTIN) is, but you ought to know what a nominal scale is. By definition, an identifier cannot be a numeric value.

    INSERT INTO Product_Schedule

    VALUES

    (‘1’, ‘2020-10-02', ‘2024-10-02', 5),

    (‘2’, ‘2020-02-10', ‘2021-02-10', 2),

    (‘3’, ‘2019-01-10', ‘2019-01-10', 1),

    (‘4’, ‘2019-01-18', ‘2021-01-18', 3);

    Please read the ISO standards for naming data elements. The acceptable form is “<attribute name>_<attribute property>”, I am going to guess that your emi_installment is a count which you want to use for creating redundant data.

    I’m trying to figure out why you would want to use the Data this way. It seems to be just a waste of space and a great increase in redundancy. If you still think in terms of punchcards instead of a relational database, this would be the equivalent of printing out physical tickets.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, July 2, 2020 8:10 PM
  • Hi baluram321,
    Has your problem been solved? If it is solved, please mark the point that you 
    think is correct as an answer. This can help others who encounter similar problems.

    Best Regards
    Echo

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 3, 2020 3:18 AM