GROUPING CASE

Question

GROUPING CASE

matt_scott1984

SSC-Addicted

Points: 469
More actions
November 10, 2011 at 5:31 am

#251528

Hello all,
I have the following code:
SELECT TOP (100) PERCENT tr_trrefn, tr_abbrv,
CASE WHEN tr_abbrv = 'BIL' THEN tr_disamt WHEN tr_abbrv = 'ABT' THEN - tr_disamt WHEN tr_abbrv = 'UPD' THEN tr_amount ELSE NULL
END AS Disbs
FROM cafintrn
WHERE (tr_matter = '00067603') AND (tr_abbrv IN ('BIL', 'RTO', 'ABT', 'UPD'))
ORDER BY tr_trdate, tr_seqnum
I have a problem with certain lines in the data regarding the CASE WHEN function on the 'Disbs' column.
I need to join certain rows based on certain criteria. With the current code I get a row where tr_type = 'bl' and a row where tr_type = 'up'. The tr_trrefn is the same on both rows. I basically want to group this together. Is this possible?
This is the current data i'm getting:
This is my required result:
Let me know if more info is needed.
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

matt_scott1984 (11/10/2011)
Hello all,
I have the following code:
SELECT TOP (100) PERCENT tr_trrefn, tr_abbrv,
CASE WHEN tr_abbrv = 'BIL' THEN tr_disamt WHEN tr_abbrv = 'ABT' THEN - tr_disamt WHEN tr_abbrv = 'UPD' THEN tr_amount ELSE NULL
END AS Disbs
FROM cafintrn
WHERE (tr_matter = '00067603') AND (tr_abbrv IN ('BIL', 'RTO', 'ABT', 'UPD'))
ORDER BY tr_trdate, tr_seqnum
I have a problem with certain lines in the data regarding the CASE WHEN function on the 'Disbs' column.
I need to join certain rows based on certain criteria. With the current code I get a row where tr_type = 'bl' and a row where tr_type = 'up'. The tr_trrefn is the same on both rows. I basically want to group this together. Is this possible?
This is the current data i'm getting:
This is my required result:
Let me know if more info is needed.
Thanks in advance

I'm going to just go ahead and quote myself from your other post.

Cadavre (11/10/2011)
Hello and welcome to SSC!
~~It appears that you have forgotten to post a few details, so I'm unsure what your actual question is!~~
What we could do with from you are four things: ~~A clear description of your problem~~, DDL scripts, sample data in a readily consumable format and ~~expected results~~.
When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
Thanks.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

*sigh*

OK, here is my guess at your requirements. If I'm incorrect, read this article (http://www.sqlservercentral.com/articles/Best+Practices/61537/)[/url] then post DDL and readily consumable sample data.

First, here is the sample data I've used.

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment

END

SELECT tr_trrefn, tr_abbrv, costs, tr_disamt, tr_amount, tr_matter

INTO #testEnvironment

FROM (SELECT 123 AS tr_trrefn, 'ABT' AS tr_abbrv, -500 AS costs, 5 AS tr_disamt, NULL AS tr_amount,

'00067603' AS tr_matter

UNION ALL

SELECT 123, 'UPD', NULL, NULL, -5, '00067603'

UNION ALL

SELECT 245, 'BIL', 1000, 100, NULL, '00067603'

UNION ALL

SELECT 245, 'UPD', NULL, NULL, 200, '00067603'

UNION ALL

SELECT 135, 'BIL', NULL, 500, NULL, '00067603'

UNION ALL

SELECT 135, 'UPD', NULL, NULL, 500, '00067603'

UNION ALL

SELECT 987, 'BIL', 1000, NULL, NULL, '00067603') a

And this is my guess at your requirements.

SELECT tr_trrefn, MIN(tr_abbrv) AS tr_abbrv, SUM(ISNULL(Disbs,0)) AS Disbs

FROM (SELECT tr_trrefn, tr_abbrv,

CASE WHEN tr_abbrv = 'BIL'

THEN tr_disamt

WHEN tr_abbrv = 'ABT'

THEN - tr_disamt

WHEN tr_abbrv = 'UPD'

THEN tr_amount

ELSE NULL END AS Disbs

FROM #testEnvironment

WHERE (tr_matter = '00067603') AND (tr_abbrv IN ('BIL', 'RTO', 'ABT', 'UPD'))) a

GROUP BY tr_trrefn

All together, the script is this: -

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment

END

SELECT tr_trrefn, tr_abbrv, costs, tr_disamt, tr_amount, tr_matter

INTO #testEnvironment

FROM (SELECT 123 AS tr_trrefn, 'ABT' AS tr_abbrv, -500 AS costs, 5 AS tr_disamt, NULL AS tr_amount,

'00067603' AS tr_matter

UNION ALL

SELECT 123, 'UPD', NULL, NULL, -5, '00067603'

UNION ALL

SELECT 245, 'BIL', 1000, 100, NULL, '00067603'

UNION ALL

SELECT 245, 'UPD', NULL, NULL, 200, '00067603'

UNION ALL

SELECT 135, 'BIL', NULL, 500, NULL, '00067603'

UNION ALL

SELECT 135, 'UPD', NULL, NULL, 500, '00067603'

UNION ALL

SELECT 987, 'BIL', 1000, NULL, NULL, '00067603') a

PRINT '========== Your Query =========='

PRINT REPLICATE('=',80)

SELECT TOP (100) PERCENT

tr_trrefn, tr_abbrv,

CASE WHEN tr_abbrv = 'BIL'

THEN tr_disamt

WHEN tr_abbrv = 'ABT'

THEN - tr_disamt

WHEN tr_abbrv = 'UPD'

THEN tr_amount

ELSE NULL END AS Disbs

FROM #testEnvironment

WHERE (tr_matter = '00067603') AND (tr_abbrv IN ('BIL', 'RTO', 'ABT', 'UPD'))

--ORDER BY tr_trdate, tr_seqnum

PRINT '========== My Guess at your requirements =========='

PRINT REPLICATE('=',80)

SELECT tr_trrefn, MIN(tr_abbrv) AS tr_abbrv, SUM(ISNULL(Disbs,0)) AS Disbs

FROM (SELECT tr_trrefn, tr_abbrv,

CASE WHEN tr_abbrv = 'BIL'

THEN tr_disamt

WHEN tr_abbrv = 'ABT'

THEN - tr_disamt

WHEN tr_abbrv = 'UPD'

THEN tr_amount

ELSE NULL END AS Disbs

FROM #testEnvironment

WHERE (tr_matter = '00067603') AND (tr_abbrv IN ('BIL', 'RTO', 'ABT', 'UPD'))) a

GROUP BY tr_trrefn

And returns: -

========== Your Query ==========

================================================================================

tr_trrefn tr_abbrv Disbs

----------- -------- -----------

123 ABT -5

123 UPD -5

245 BIL 100

245 UPD 200

135 BIL 500

135 UPD 500

987 BIL NULL

(7 row(s) affected)

========== My Guess at your requirements ==========

================================================================================

tr_trrefn tr_abbrv Disbs

----------- -------- -----------

123 ABT -10

135 BIL 1000

245 BIL 300

987 BIL 0

(4 row(s) affected)

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

It's going to be something like this:

SELECT

tr_trrefn,

tr_abbrv,

Disbs = SUM(CASE

WHEN tr_abbrv = 'BIL' THEN tr_disamt

WHEN tr_abbrv = 'ABT' THEN - tr_disamt

WHEN tr_abbrv = 'UPD' THEN tr_amount

ELSE NULL END)

FROM cafintrn

WHERE tr_matter = '00067603' AND tr_abbrv IN ('BIL', 'RTO', 'ABT', 'UPD')

GROUP BY tr_trrefn, tr_abbrv

But as Cadavre's pointed out, it's difficult to be sure without a little sample data to play with.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden