0
SELECT     bid, PurAmount, row_number() OVER (ORDER BY id, bill_date, type) AS rrn, RunningTotal, rn
FROM         View_6 I1 OUTER APPLY
                          (SELECT     SUM(PurchaseAvg * PurAmount) / NULLIF (SUM(PurAmount), 0) RunningTotal
                             FROM         View_6 I2
                             WHERE     I1.id = I2.id AND I1.IslId = I2.IslId AND I1.rn >= I2.rn AND TYPE = 1) C
gunr2171
  • 16,104
  • 25
  • 61
  • 88
Shreef Hamdi Mekawy
  • 11
  • 2
    **Table and index definitions please** You don't need a left join, you need a running sum – Charlieface Jan 25 '21 at 15:00
  • Note: in July 2019, extended support for SQL Server 2008 R2 ended, so.... it may be moot how it performs? – Marc Gravell Jan 25 '21 at 15:00
  • @MarcGravell Why moot, the performance (or lack) of a triangular join doesn't change – Charlieface Jan 25 '21 at 15:01
  • It doesn't really, @Charlieface , but not using one at all, like you demonstrate in your answer, is a *HUGE* performance benefit; and the OP can't if they aren't using a supported version of SQL Server. – Thom A Jan 25 '21 at 15:11

1 Answers1

0

Move to SQL Server 2012+ and use SUM() OVER

SELECT
    bid,
    PurAmount,
    row_number() OVER (ORDER BY id, bill_date, type) AS rrn,
    SUM(CASE WHEN TYPE = 1 THEN PurchaseAvg * PurAmount END) OVER (ORDER BY rn ROWS UNBOUNDED PRECEDING) /
        NULLIF (SUM(CASE WHEN TYPE = 1 THEN PurAmount END) OVER (ORDER BY rn ROWS UNBOUNDED PRECEDING), 0)
        AS RunningTotal,
    rn
FROM View_6 I1

If you really, really want to use an out-of-support, insecure version, see Calculate a Running Total in SQL Server for other options.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • is sql server 2012 more performance than 2008 and what about 2019 performance – Shreef Hamdi Mekawy Jan 25 '21 at 15:30
  • On Server 2012+ you can use this syntax, it is much faster as it only requires one scan of the table. It's not available in 2008. If you're worried about cost, you can get either SQL Server Developer Edition or LocalDB Edition – Charlieface Jan 25 '21 at 16:08