CODEWITHSUNDEEP
sqlsql-server
Kannan Muthiah
Kannan Muthiah

Reputation: 37

add current row value with previous row calculated value

I have table with table as below

OwnerId StartDate      EndDate      Volume
1         2020-01-01    2020-01-01  10.80
1         2020-01-02    2020-01-02  5.60
1         2020-01-03    2020-01-03  7.90
1         2020-01-04    2020-01-04  8.50

Another table having their previous value. 

OwnerId PreviousVolume
1        0.70

1) The previous volume should ADD with my first table first row. And then get whole number part and decimal part value.

2) The decimal part add with first table second row and then get whole number and decimal part and vice-versa.

ownerid startdate   enddate     Volume    Calculatedvolume  remainingexisting
1      2020-01-01   2020-01-01    10.80         11                   0.50
1      2020-01-02   2020-01-02    5.60           6                   0.10
1      2020-01-03   2020-01-03    7.90           8                   0.00
1      2020-01-04   2020-01-04    8.50           8                   0.50

Could you please anyone help?

Upvotes: 1

Views: 652

Answers (2)

Zhorov
Zhorov

Reputation: 29943

If I understand your question correctly, the next statement may help to get your expected results:

Tables:

CREATE TABLE Table1 (
    OwnerId int, 
    StartDate date,      
    EndDate date,      
    Volume numeric(10, 2)
)
INSERT INTO Table1 
   (OwnerId, StartDate, EndDate, Volume)
VALUES
    (1, '20200101', '20200101', 10.80),
    (1, '20200102', '20200102', 5.60),
    (1, '20200103', '20200103', 7.90),
    (1, '20200104', '20200104', 8.50)
CREATE TABLE Table2 (
    OwnerId int,
    PreviousVolume numeric(10, 2)
)
INSERT INTO Table2 
    (OwnerId, PreviousVolume)
VALUES
    (1, 0.70)

Statement:

;WITH DataCte AS (
    SELECT OwnerId, NULL AS StartDate, NULL AS EndDate, PreviousVolume AS Volume
    FROM Table2
    UNION ALL
    SELECT OwnerId, StartDate, EndDate, Volume
    FROM Table1
), CalculationCTE AS (
    SELECT 
        OwnerId, StartDate, EndDate, Volume,
        SUM(Volume) OVER (PARTITION BY OwnerId ORDER BY StartDate) AS Total,
        CONVERT(numeric(10, 2), FLOOR(SUM(Volume) OVER (PARTITION BY OwnerId ORDER BY StartDate))) AS TotalFloor
    FROM DataCTE
)
SELECT
    OwnerId, StartDate, EndDate,
    FLOOR(Total - LAG(TotalFloor) OVER (PARTITION BY OwnerID ORDER BY StartDate)) AS CalculatedVolume,
    Total - TotalFloor AS RemainingExisting
FROM CalculationCTE

Result:

---------------------------------------------------------------------
OwnerId StartDate   EndDate     CalculatedVolume    RemainingExisting
---------------------------------------------------------------------
1       NULL        NULL        NULL                0.70 
1       2020-01-01  2020-01-01  11                  0.50
1       2020-01-02  2020-01-02  6                   0.10
1       2020-01-03  2020-01-03  8                   0.00
1       2020-01-04  2020-01-04  8                   0.50

Upvotes: 0

El.Hum
El.Hum

Reputation: 1485

I think this kind of query might work for your problem but also I have another idea with LEAD() (or LAG()?) function which I will edit my answer and add it if this didnt work: 

DECLARE @count = 1;

SELECT TOP 1 
    ROW_NUMBER() OVER (PARTITION BY T1.OwnerId ORDER BY T1.startdate,T1.enddate) R#
    ,T1.OwnerId 
    ,T1.StartDate      
    ,T1.EndDate      
    ,T1.Volume
    ,FLOOR(T1.Volumn+T2.PreviousVolum) AS Calculatedvolume  
    ,(T1.Volumn+T2.PreviousVolum)%1 AS remainingexisting 
INTO #tmp
FROM T1 
INNER JOIN T2 ON T1.OwnerId = T2.OwnerId
ORDER BY T1.startdate,T1.enddate;

WHILE @count > (SELECT COUNT(*) FROM T1) 
BEGIN 

    INSERT INTO #tmp
    SELECT 
         @count+1
        ,T1.OwnerId 
        ,T1.StartDate      
        ,T1.EndDate      
        ,T1.Volume
        ,FLOOR(T1.Volumn+T2.Calculatedvolume) AS Calculatedvolume  
        ,(T1.Volumn+T2.Calculatedvolume)%1 AS remainingexisting 
    FROM T1 
    INNER JOIN #tmp T2 ON T1.OwnerId = T2.OwnerId AND T2.R# = @count
    WHERE ROW_NUMBER() OVER (ORDER BY T1.startdate,T1.enddate) = @count+1;

    SET @count += 1;

END

Upvotes: 1

Related Questions