Assign a value to a column based on multiple conditions

I have accounting data in SQL. These are entries made for a sale so for every sale made there is an offsetting entry for cost of goods sold as well. In the accounting system we use, it auto generates a transaction id in the TxnId column only for the sales lines not for the cost of goods sold lines for some reason unknown to me. I extract the data using the following query:

  SELECT account,name, memo, debit,credit,amount,txnid,refnum,ROW_NUMBER()
                    OVER ( 
                      partition BY memo, refnum 
                      ORDER BY account,memo,amount desc) AS Row_Counter
  FROM [dbo].[Table] order by memo asc

The resulting table looks like this:

Account                Name       Memo           Debit    Credit   Amount   TxnID      RefNum    Row_Counter 
Sales                Company A    Wingding 1     Null     855       855    5EASUC-X    12345        1
CostofGoodsSold      Company A    Wingding 1     -684     Null     -684    Null        12345        2
Sales                Company A    Wingding 2     Null     910       910    5EASUC-X    12345        1
CostofGoodsSold      Company A    Wingding 2     -850     Null     -850    Null        12345        2
Sales                Company A    Wingding 3     Null     760       760    5EASUC-X    12345        1
CostofGoodsSOld      Company A    Wingding 3     -665     Null     -665    Null        12345        2

What I am trying to do is modify the query above to add a column on the end where it places the amount from the sales column in the same row for the cost of goods sold line based on the memo column. I am hoping to get a table that looks like this:

Account                Name       Memo           Debit    Credit   Amount   TxnID      RefNum    Row_Counter  Newnum
Sales                Company A    Wingding 1     Null     855       855    5EASUC-X    12345        1         855
CostofGoodsSold      Company A    Wingding 1     -684     Null     -684    Null        12345        2         855
Sales                Company A    Wingding 2     Null     910       910    5EASUC-X    12345        1         910
CostofGoodsSold      Company A    Wingding 2     -850     Null     -850    Null        12345        2         910
Sales                Company A    Wingding 3     Null     760       760    5EASUC-X    12345        1         760
CostofGoodsSOld      Company A    Wingding 3     -665     Null     -665    Null        12345        2         760

If anyone has any suggestions, they would be greatly appreciated.

Answer

This is what I understood you would want:

  SELECT account,name, memo, debit,credit,amount,txnid,refnum,ROW_NUMBER()
                    OVER ( 
                      partition BY memo, refnum 
                      ORDER BY account,memo,amount desc) AS Row_Counter
, SUM(Credit) OVER (PARTITION BY memo) as Newnum
  FROM [dbo].[Table] order by memo asc

Advertisement

Answer