I have a table with the following fields:

and so on, I have 91M rows

I want a query which will help me create a view with following data:

I cant write a stored procedure. It has to be done using a view only.

Appreciate your help in this Thanks in advance.

CodePudding user response：

So first you need a tally table (AKA numbers table.)

;--==== Tally Table
DECLARE @Rows INT = 10;

WITH E1(N)     AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(x)),
     iTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1, E1 AS b)
SELECT t.N
FROM iTally AS t
WHERE t.N <= @Rows;

This gets you as many rows as you need (up to 100 for this example.)

N
------
1
2
3
4
5
6
7
8
9
10

Next, lets determine the correct amounts and number of rows required:

;--==== 1. Sample Data
DECLARE @t TABLE (
  LineId   INT,
  FromDate DATE,
  ToDate   DATE,
  Amount   INT);

INSERT @t VALUES
(1, '01.01.2021', '10.01.2021', 100),
(2, '05.07.2021', '07.07.2021', 300);

;--==== 2. Calculate # of months and divide amount by months for "Amount"
SELECT
  LineID = t.LineId,
  Months = mo.Months,
  Amount = t.Amount/mo.Months
FROM   @t AS t
CROSS APPLY (VALUES(DATEDIFF(MONTH,t.FromDate,t.ToDate) 1)) AS mo(Months)

This returns:

LineID      Months      Amount
----------- ----------- -----------
1           10          10
2           3           100

Now we will use the number of months to determine how many rows to generate:

;--==== 3. Solution
WITH E1(N)     AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(x)),
     iTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1, E1 AS b)
SELECT 
  LineId   = t.LineId,
  FromDate = DATEADD(MONTH,tally.N-1,t.FromDate),
  Amount   = t.Amount/mo.Months
FROM        @t                                              AS t
CROSS APPLY iTally                                          AS tally
CROSS APPLY (VALUES(DATEDIFF(MONTH,t.FromDate,t.ToDate) 1)) AS mo(Months)
WHERE       tally.N <= mo.Months
ORDER BY    t.LineId -- Not required;

Results:

LineId      FromDate   Amount
----------- ---------- -----------
1           2021-01-01 10
1           2021-02-01 10
1           2021-04-01 10
1           2021-05-01 10
1           2021-06-01 10
1           2021-07-01 10
1           2021-08-01 10
1           2021-09-01 10
1           2021-10-01 10
1           2021-03-01 10
2           2021-07-07 100
2           2021-06-07 100
2           2021-05-07 100

As a view you would replace my sample temp table with the real one.

CREATE VIEW dbo.YOUR_VIEW AS
WITH E1(N)     AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(x)),
     iTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1, E1 AS b)
SELECT 
  LineId   = t.LineId,
  FromDate = DATEADD(MONTH,tally.N-1,t.FromDate),
  Amount   = t.Amount/mo.Months
FROM        @t                                              AS t
CROSS APPLY iTally                                          AS tally
CROSS APPLY (VALUES(DATEDIFF(MONTH,t.FromDate,t.ToDate) 1)) AS mo(Months)
WHERE       tally.N <= mo.Months
ORDER BY    t.LineId -- Not required;