Grouping and Calculating SUM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grouping and Calculating SUM

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2011-05-26 : 12:38:56

Friends,

Please find my sample data

BillNo ItemNumber GrossAmnt
1001 1 100
1001 2 50
1001 3 250
1002 1 75
1002 2 50
1003 1 230

I will pass BillNo and ItemNumber as parameter and my Expected output will be,

@BillNo = 1001 , ItemNumber = 2

BillNo CurrentAmount TotalUptoPrevious Total
1001 50 100 150

@BillNo = 1001 , ItemNumber = 1

BillNo CurrentAmount TotalUptoPrevious Total
1001 50 0 50

@BillNo = 1001 , ItemNumber = 3

BillNo CurrentAmount TotalUptoPrevious Total
1001 250 150 400

@BillNo = 1003 , ItemNumber = 1

BillNo CurrentAmount TotalUptoPrevious Total
1003 230 0 230

Could you please help me to get my expected output, Thanks in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-26 : 13:01:20

A way of doing it:

SELECT
   a.BillNo,
   a.GrossAmt AS CurrentAmt,
   b.Total - a.GrossAmt AS TotalUptoPrevious,
   b.Total
FROM
   YourTable a
   CROSS APPLY
   (
      SELECT SUM(GrossAmt) AS Total
      FROM YourTable b
      WHERE b.BillNo = a.BillNo
         AND b.ItemNumber <= a.ItemNumber
   ) b
WHERE
   a.ItemNumber = @ItemNumber
   AND a.BillNumber = @BillNumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources