Split Rows in multiple rows

Question

I need to divide a row in several rows

I have for example this result, which represents Total Amount the user should pay in several Installments, and each installment is due to some date, based on Installment Interval, so each TotalSum is divided with InstallsmentNoTotal and that is each Installment Amount, and for InstallmentDate for first Installment is FirstInstallmentDate, but for every another installment we should add InstallsmentInterval Days.

DECLARE @temTable TABLE
(
    Id INT,
    TotalSum DECIMAL(18, 0),
    InstallsmentNoTotal INT,
    InstallsmentInterval INT,
    FirstInstallmentDate DATE
);

INSERT INTO @temTable
(
    Id,
    TotalSum,
    InstallsmentNoTotal,
    InstallsmentInterval,
    FirstInstallmentDate
)
VALUES 
(1, 6000, 3, 30, '2020-08-01'),
(2, 8000, 2, 60, '2020-10-01'),
(3, 5000, 1,  0, '2020-08-01')

SELECT * FROM @temTable

Id  TotalSum    InstallsmentNoTotal   InstallsmentInterval  FirstInstallmentDate
1    6000                3                     30                2020-08-01 
2    8000                2                     60                2020-10-01
3    5000                1                      0                2020-08-01

I need to display data for each row like this:

SaleId     InstallmentNo   InstallmensAmount   NextInstallmentDate
   1              1             2000              2020-08-01
   1              2             2000              2020-09-01
   1              3             2000              2020-10-01
   2              1             4000              2020-10-01
   2              2             4000              2020-12-01
   3              1             5000              2020-08-01

I tried using, Cursors, Cross Apply, but I can't manage working.

Thank you in advance for help

Answer 1

You can go for CTE to achieve the resultset.

;WITH CTE_Installments as
(
SELECT Id,TotalSum/InstallsmentNoTotal AS InstallmentSum
,1 as CurrentinstallmentNo, InstallsmentNoTotal, 
dateadd(dd,InstallsmentInterval,FirstInstallmentDate) as nextInstallmentDate, InstallsmentInterval  FROM @temTable
union all
SELECT Id, InstallmentSum, CurrentInstallmentNo+1 as currentinstallmentNo, InstallsmentNoTotal,
dateadd(dd,InstallsmentInterval,nextInstallmentDate) as nextInstallmentDate, InstallsmentInterval
from CTE_Installments
where CurrentinstallmentNo < InstallsmentNoTotal
)
SELECT Id as salesId, CurrentinstallmentNo as InstallmentNo, InstallmentSum,nextInstallmentDate
FROM CTE_Installments
order by Id

+---------+---------------+------------------+---------------------+
| salesId | InstallmentNo |  InstallmentSum  | nextInstallmentDate |
+---------+---------------+------------------+---------------------+
|       1 |             1 | 2000.00000000000 | 2020-08-31          |
|       1 |             2 | 2000.00000000000 | 2020-09-30          |
|       1 |             3 | 2000.00000000000 | 2020-10-30          |
|       2 |             1 | 4000.00000000000 | 2020-11-30          |
|       2 |             2 | 4000.00000000000 | 2021-01-29          |
|       3 |             1 | 5000.00000000000 | 2020-08-01          |
+---------+---------------+------------------+---------------------+

Answer 2

This is a "tally" table based approach which meets the requirement. This approach is a simple and efficient way to calculate forward based on a numbers or tally table. In this case I made it 6 values but it could be increased to whatever is needed (to meet the maximum InstallsmentNoTotal). Imo tally based approach is simpler, more readable, and (in the limit) faster.

Data

create table #temTable
(
    Id INT,
    TotalSum DECIMAL(18, 0),
    InstallsmentNoTotal INT,
    InstallsmentInterval INT,
    FirstInstallmentDate DATE
);

INSERT INTO #temTable
(
    Id,
    TotalSum,
    InstallsmentNoTotal,
    InstallsmentInterval,
    FirstInstallmentDate
)
VALUES 
(1, 6000, 3, 30, '2020-08-01'),
(2, 8000, 2, 60, '2020-10-01'),
(3, 5000, 1,  0, '2020-08-01');

Query

select id SaleId, t.TotalSum/t.InstallsmentNoTotal InstallmentAmount,
       dateadd(month, (InstallsmentInterval/30)*(tally.n-1), FirstInstallmentDate) NextInstallmentDate
from #temTable t
     cross apply
     (select n from (values (1),(2),(3),(4),(5),(6)) v(n)
      where n<=t.InstallsmentNoTotal) tally;

Output

SaleId  InstallmentAmount   NextInstallmentDate
1       2000.00000000000    2020-08-01
1       2000.00000000000    2020-09-01
1       2000.00000000000    2020-10-01
2       4000.00000000000    2020-10-01
2       4000.00000000000    2020-12-01
3       5000.00000000000    2020-08-01

Answer 3

You can use a recursive query:

with cte as (
    select 
        id saleId, 
        1 InstallmentNo,
        TotalSum / InstallsmentNoTotal InstallmentAmount,
        FirstInstallmentDate NextInstallmentDate,
        InstallsmentInterval,
        InstallsmentNoTotal
    from @temTable
    union all
    select 
        saleId,
        InstallmentNo + 1,
        InstallmentAmount,
        cast(dateadd(day, InstallsmentInterval, NextInstallmentDate) as date),
        InstallsmentInterval,
        InstallsmentNoTotal
    from cte
    where InstallmentNo < InstallsmentNoTotal
)
select saleId, InstallmentAmount, InstallmentAmount, NextInstallmentDate
from cte
order by saleId, InstallmentNo

For your sample data, this returns:

saleId | InstallmentAmount | InstallmentAmount | NextInstallmentDate
-----: | ----------------: | ----------------: | :------------------
     1 |  2000.00000000000 |  2000.00000000000 | 2020-08-01         
     1 |  2000.00000000000 |  2000.00000000000 | 2020-08-31         
     1 |  2000.00000000000 |  2000.00000000000 | 2020-09-30         
     2 |  4000.00000000000 |  4000.00000000000 | 2020-10-01         
     2 |  4000.00000000000 |  4000.00000000000 | 2020-11-30         
     3 |  5000.00000000000 |  5000.00000000000 | 2020-08-01         

You can see that the date arithmetics is not quite what you want. If you are going to increase the dates on a monthly basis, then you should store a number of months rather than days (and modify dateadd(day, ...) with dateadd(month, ...) in the recursive member of the CTE.