Sql server help

Transact-SQL
, ,
1

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 :
;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
above query is giving expected result,but query execution taking more time.
here i give sample records ,in orginal table 200 core records.cte is occupy 100% ram memory utilization consequences of storiing milion records in cte.

can you pleaese me how to write alernative solution with out using cte in sql server

2

hi hope this helps :slight_smile:

this is using tally table .. what is tally table you can google .. !!!
this should be very fast

please note i have used 100 as max limit for tally table
if your EMIInstallment is greater than 100 than change 100 in tally table

;WITH Tally (N) AS
(
    SELECT top 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))   FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
select 
      b.*
	, N 
from 
      tally  a 
	, [productdetails] b  
where 
    a.N <= b.EMIInstallment
order by 
    b.productid,a.N

image
image715×568 10 KB

3

can we you please provide any alternative query with out using cte function.cte total occupy the 100 % ram..

4

you can create a physical table .. populate it with 100 rows ... 1 to 100
and use it in place of CTE

create table temp
(
N int
)

declare @i int = 0
while @i <= 100
insert into temp select @i + 1

select 
      b.*
	, N 
from 
      temp  a 
	, [productdetails] b  
where 
    a.N <= b.EMIInstallment
order by 
    b.productid,a.N
5

To do this - you have to have a numbers or tally table - that can either be generated using a CTE or from a permanent table.

Either way- this should not cause a significant issue with memory or CPU but that all depends on the product installment table and the maximum value in the EMInstallment table.

This solution generates only the number of rows needed for the maximum value from the EMInstallment table:

Declare @productdetails Table (
        productid int
      , Productrstartdate date
      , Productenddate date
      , EMIInstallment int);

 Insert @productdetails (productid, Productrstartdate, Productenddate, EMIInstallment)
 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);

 --==== Get maximum number of installments
Declare @maxInstallment int = (Select max(p.EMIInstallment) From @productdetails p);

   With t(n)
     As (
 Select t.n 
   From (
 Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
        )
      , installments (Number)
     As (
 Select Top (@maxInstallment)
        checksum(row_number() over(Order By @@spid))
   From t t1, t t2              -- up to 100 installments
        )
 Select p.productid
      , p.Productrstartdate
      , NoOfInstallmentCount = i.Number
   From @productdetails         p
  Inner Join installments       i On i.Number <= p.EMIInstallment
  Order By
        p.productid
      , p.Productrstartdate
      , i.Number;

If you already have a Numbers or Tally table - then you can do this:

 Select p.productid
      , p.Productrstartdate
      , NoOfInstallmentCount = t.N
   From @productdetails         p
  Inner Join dbo.Tally          t On t.N <= p.EMIInstallment
  Order By
        p.productid
      , p.Productrstartdate
      , t.N;
1 Like
6

There's something really wrong there. Can you explain why you think a CTE will occupy 100% of the RAM?