CODEWITHSUNDEEP
sql-server
sheshadri
sheshadri

Reputation: 1217

How to perform calculation for multiple rows in sql

I have 4 result sets which i moved into one temp table for report purpose:

Date        Issued_Id   Item_Name   Qty_Issued  Qty_Return  Qty_Damage  Type    Balance OPBal
----------------------------------------------------------------------------------------------
Dec 18 2014 6003        Bed Covers  4           0           0           IS      NULL    245
Dec 18 2014 6008        Bed Covers  4           0           0           IS      NULL    245
2014-12-17  6000        Bed Covers  0           22          0           RT      NULL    245
2014-12-22  7002        Bed Covers  0           10          0           RT      NULL    245

Now I have to add (OPBal=Qty_Issued + OPBal) when Type="IS" and subtract (OPBal=Qty_Return - OPBal) when type="RT".

It should print like this way

Date        Issued_Id   Item_Name   Qty_Issued  Qty_Return  Qty_Damage  Type    Balance OPBal
---------------------------------------------------------------------------------------------
Dec 18 2014 6003        Bed Covers  4           0           0           IS      NULL    249
Dec 18 2014 6008        Bed Covers  4           0           0           IS      NULL    253
2014-12-17  6000        Bed Covers  0           22          0           RT      NULL    231
2014-12-22  7002        Bed Covers  0           10          0           RT      NULL    221

How can I achieve using cursor in SQL Server?

Upvotes: 1

Views: 1348

Answers (5)

200_success
200_success

Reputation: 7582

It's unclear what the sort criteria are for the four rows in your question — it's neither by date nor by Issued_Id. I'm going to assume that the entries should be ordered by Issued_Id, and that your example is a mistake.

The simplest way to get a cumulative sum is to use a window query.

SELECT Date, Issued_Id, Item_Name, Qty_Issued, Qty_Return, Qty_Damage, Type, Balance
     , SUM(
           CASE WHEN Type='IS' THEN Qty_Issued
                WHEN Type='RT' THEN -Qty_Return
           END
       ) OVER (
           ORDER BY Issued_Id ROWS UNBOUNDED PRECEDING
       ) + OPBal AS OPBal
    FROM #Temp1
    ORDER BY Issued_Id;

It might even be possible to simplify it by ignoring the Type column and the CASE expression altogether.

SELECT Date, Issued_Id, Item_Name, Qty_Issued, Qty_Return, Qty_Damage, Type, Balance
     , SUM(Qty_Issued - Qty_Return)
       OVER (ORDER BY Issued_Id ROWS UNBOUNDED PRECEDING) + OPBal AS OPBal
    FROM #Temp1
    ORDER BY Issued_Id;

The setup for the queries above:

CREATE TABLE #Temp1
( Date DATE NOT NULL
, Issued_Id INTEGER NOT NULL
, Item_Name VARCHAR(32) NOT NULL
, Qty_Issued DECIMAL NOT NULL DEFAULT 0
, Qty_Return DECIMAL NOT NULL DEFAULT 0
, Qty_Damage DECIMAL NOT NULL DEFAult 0
, Type VARCHAR(2) NOT NULL
, Balance DECIMAL
, OPBal DECIMAL NOT NULL
);

INSERT INTO #Temp1 VALUES
('2014-12-18', 6003, 'Bed Covers', 4, 0,  0, 'IS', NULL, 245),
('2014-12-18', 6008, 'Bed Covers', 4, 0,  0, 'IS', NULL, 245),
('2014-12-17', 6000, 'Bed Covers', 0, 22, 0, 'RT', NULL, 245),
('2014-12-22', 7002, 'Bed Covers', 0, 10, 0, 'RT', NULL, 245);

Upvotes: 1

sheshadri
sheshadri

Reputation: 1217

Thanks to everyone.. I got solution using cursor and case...

DECLARE db_cursor CURSOR for select id from #Temp1;
open db_cursor
fetch next from db_cursor into @varData
while @@FETCH_STATUS=0
begin
set @LastAmt =  ( select OPBal= case 
when Type='IS' then @LastAmt+Qty_Issued
when type='RT' then @LastAmt-(Qty_Return+Qty_Damage) 
when type='OP' then OPBal
else 0
end
from #Temp1 where id = @varData)
update #Temp1 set OPBal= @LastAmt where id = @varData
FETCH NEXT
FROM db_cursor INTO @varData
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from #Temp1

Upvotes: 0

Jitendra Rathor
Jitendra Rathor

Reputation: 130

CREATE TABLE #TT
(
    [Date] DateTime,
    Issued_Id VARCHAR(100),
    Item_Name VARCHAR(100),
    Qty_Issued INT,
    Qty_Return INT,
    Qty_Damage INT,
    [Type] VARCHAR(100),
    Balance INT,
    OPBal INT
)

INSERT INTO  #TT
SELECT 'Dec 18 2014', 6003,' Bed Covers ', 4,0,0,'IS', NULL,245 UNION ALL
SELECT 'Dec 18 2014', 6008,' Bed Covers ', 4,0,0,'IS',  NULL,245 UNION ALL
SELECT '2014-12-17', 6000,' Bed Covers ', 4,22,0,'RT', NULL, 245 UNION ALL
SELECT '2014-12-22',  7002,' Bed Covers ', 4,10,0,'RT',  NULL,245 

--- IF you want to  apply this logic with out any sort(as shown in your question)
     SELECT [Date],Issued_Id, Item_Name, Qty_Issued, Qty_Return, Qty_Damage, Type, Balance,
  OPBal 
  + SUM(case when type = 'IS' then Qty_Issued else 0 end) OVER (ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
  - SUM(case when type = 'RT' then Qty_Return else 0 end) OVER (ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
  AS OPBal
FROM #TT 


--- IF you want to order records by [Date] & issued_id then apply this logic
     SELECT [Date],Issued_Id, Item_Name, Qty_Issued, Qty_Return, Qty_Damage, Type, Balance,
  OPBal 
  + SUM(case when type = 'IS' then Qty_Issued else 0 end) OVER (ORDER BY [Date] ASC, issued_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
  - SUM(case when type = 'RT' then Qty_Return else 0 end) OVER (ORDER BY [Date] ASC, issued_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
  AS OPBal
  ,SUM(case when type = 'IS' then Qty_Issued else 0 end) OVER (ORDER BY [Date] ASC, issued_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
  ,SUM(case when type = 'RT' then Qty_Return else 0 end) OVER (ORDER BY [Date] ASC, issued_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
FROM #TT

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

It is not clear what your records are to be ordered by. It seems to be Date (oldest first) and Issue_Id (smallest first). If I am wrong abot this, simply change the order by clauses in below statement according to your wishes.

What you are looking for is a kind of running total, where conditionally Qty_Issued and Qty_Return get summed up. You achive this by an analytic use of SUM with a window clause.

SELECT [Date], Issued_Id, Item_Name, Qty_Issued, Qty_Return, Qty_Damage, Type, Balance,
  OPBal 
  + SUM(case when type = 'IS' then Qty_Issued else 0 end) OVER (ORDER BY [Date] desc, issue_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
  - SUM(case when type = 'RT' then Qty_Return else 0 end) OVER (ORDER BY [Date] desc, issue_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
  AS OPBal
FROM mytable
ORDER BY [Date] desc, issue_id;

As you have zeros in your data for Qty_Issued and Qty_Return where they are not appropriate, you might also simply be able to use:

SELECT [Date], Issued_Id, Item_Name, Qty_Issued, Qty_Return, Qty_Damage, Type, Balance,
  OPBal 
  + SUM(Qty_Issued - Qty_Return) OVER (ORDER BY [Date] desc, issue_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
  AS OPBal
FROM mytable
ORDER BY [Date] desc, issue_id;

Upvotes: 0

PP006
PP006

Reputation: 709

Try this

SELECT [Date],
       Issued_ID,
       Item_Name,
       Qty_Issued,
       Qty_Return,
       Qty_Damage,
       [Type],
       Balance,
       OPBal= CASE
                WHEN TYPE = 'IS' THEN Qty_Issued + OPBal
                WHEN TYPE = 'RT' THEN Qty_Return - OPBal
                ELSE 0
              END
FROM   #TEMP

Upvotes: 0

Related Questions