New Extract
-
I've got a report that shows the following data and I need to add a new column based on certain criteria.
The original extract is like this
AccountAcctRefTxnSetTxnRef
Div-123416021501860
8637414021501860
DIV-234123421601870
9876123521601870
5678123621601870
The new extract requires an additional column, based on what already appears. I need to be able to get a count of the 'Txn Set' column, and where the count is 2 then populate the new column with the contents of the 'Account' details for the other record. If the count is greater than 2 then populate the new column with the text '1 to many' as in the example below.
AccountAcctRefTxnSetTxnRefAltAcct
Div-1234160215018608637
8637414021501860Div-123
DIV-2341234216018701 to Many
98761235216018701 to Many
56781236216018701 to Many
I hope what I've typed is clear enough, if not then let me know
-
Something like this?
SELECT a.Account, a.[Acct Ref], a.[Txn Set], a.[Txn Ref],
CASE WHEN COUNT(*) = 1 THEN MAX(b.[Account]) ELSE '1 to Many' END AS [Alt Acct]
FROM myTable a
LEFT OUTER JOIN myTable b ON b.[Txn Set] = a.[Txn Set]
AND b.Account <> a.Account
GROUP BY a.Account, a.[Acct Ref], a.[Txn Set], a.[Txn Ref]
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I'll give it a go. Many thanks for the quick response
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply