How do I continue the running totals computation from union selects in MS SQL

user_6096529 0
Viewed 48

I have two queries joined using Union All. The first computes the opening balance and the second returns rows of columns. I need to have the computed balance from the first query carry over to the second query.

Here's my table:

ItemTrans
id    ItemNo    InQty    OutQty   DateAdded   
100   'A001'    0        100      '2020-12-20'
101   'A002'    600      0        '2020-12-20'
102   'A001'    500      0        '2020-12-21'
103   'A002'    0        110      '2020-12-20'
104   'A002'    0        75       '2020-12-20'
105   'A001'    0        50       '2020-12-22'
106   'A001'    10       0        '2021-01-05'
107   'A001'    0        20       '2021-01-06'
108   'A001'    0        10       '2021-01-07'
109   'A001'    0        10       '2021-01-08'

Here's my query:

Select 
  'Opening bal' as 'IDNo',
  '' as ItemNo,
  '2020-12-31' as 'Date Posted/ As of',
  0 as QTY,
  Balance = (sum(InQty) - sum(OutQty)) 
  From itemTrans
  Where ItemNo = 'A001'
  AND DateAdded < '2021-01-01'
Union All
Select 
  cast(id as nvarchar(20)) as 'IDNo',
  ItemNo,
  DateAdded as 'Date Posted/ As of',
  QTY = InQty - OutQty,
  Case
    When InQty > 0 Then sum(InQty) OVER (ORDER BY id)
    When OutQty > 0 Then sum(OutQty*-1) OVER (ORDER BY id)
    else 0
  End as Balance
  From ItemTrans
  Where ItemNo = 'A001'
  AND DateAdded >= '2021-01-01'

My query needs to return the following:

IDNo           ItemNo    Date Posted/ As of    QTY    Balance
Opening bal              2020-12-31            0      350
106            A001      2021-01-05            10     360
107            A001      2021-01-06            -20    340
108            A001      2021-01-07            -10    330
109            A001      2021-01-08            -10    210

But my query returns:

IDNo           ItemNo    Date Posted/ As of    QTY    Balance
Opening bal              2020-12-31            0      350
106            A001      2021-01-05            10     10
107            A001      2021-01-06            -20    -20
108            A001      2021-01-07            -10    -30
109            A001      2021-01-08            -10    -40

It also seems that the running InQty is computed separately from the OutQty

sql-server
2 Answers

You are correct. Each part of the query stands on its own, and the running sum is calculated only on that part. You need to subquery it, and make sure that it is all ordered correctly:

SELECT
  IDNo,
  ItemNo,
  [Date Posted/ As of],
  QTY,
  Case
    When InQty > 0 Then sum(InQty) OVER (ORDER BY ordering, id)
    When OutQty > 0 Then sum(OutQty*-1) OVER (ORDER BY ordering, id)
    else 0
  End AS Balance
FROM (
    Select 
      0 as id,
      0 as ordering,
      'Opening bal' as IDNo,
      '' as ItemNo,
      '2020-12-31' as [Date Posted/ As of],
      0 as QTY,
      AddBalance = (sum(InQty) - sum(OutQty)),
      sum(InQty) InQty,
      sum(OutQty) OutQty
    From itemTrans
    Where ItemNo = 'A001'
      AND DateAdded < '2021-01-01'
    Union All
    Select 
      id,
      1,
      cast(id as nvarchar(20)),
      ItemNo,
      DateAdded,
      InQty - OutQty,
      0,
      InQty,
      OutQty
    From ItemTrans
    Where ItemNo = 'A001'
      AND DateAdded >= '2021-01-01'
) t

Use [] not '' to quote column names with spaces.

user_148689970

Taking inspiration from the previous answer, I came up with the query below that gave me the results I needed.

SELECT
  IDNo,
  ItemNo,
  [Date Posted/ As of],
  QTY,
  sum(Balance) OVER (ORDER BY id) as Balance
FROM 
(
  Select 
  0 as id,
  'Opening bal' as IDNo,
  '' as ItemNo,
  '2020-12-31' as [Date Posted/ As of],
  0 as QTY,
  Balance = (sum(InQty) - sum(OutQty))
  From itemTrans
  Where ItemNo = 'A001'
    AND DateAdded < '2021-01-01'
Union All
  Select 
    id,
    cast(id as nvarchar(20)) as IDNo,
    ItemNo,
    DateAdded as [Date Posted/ As of],
    InQty - OutQty,
    InQty - OutQty as Balance
  From ItemTrans
  Where ItemNo = 'A001'
    AND DateAdded >= '2021-01-01'
)t
user_60965290
Related