Grouping help

Question

Grouping help

SO-462683

Mr or Mrs. 500

Points: 565
More actions
August 7, 2007 at 9:30 am

#373136

Hi - I have the following select statement that works, except that I can't figure out how to group by the case statement within the select. I know that's probably the wrong approach, but I'm stumped. Here is the query with results and expected results following:
SELECT HDR.CUSTNMBR,
MAX(HDR.ENDDATE) AS 'ENDDATE',
HDR.DSCRIPTN,
(CASE WHEN HDR.DSCRIPTN LIKE '%CS%'
  THEN LEFT(LNE.SERLNMBR,6)
  ELSE LNE.SERLNMBR
END) AS 'SERIAL',
LNE.SERLNMBR
FROM SVC00600 AS HDR
INNER JOIN SVC00601 AS LNE ON HDR.CONTNBR=LNE.CONTNBR
WHERE HDR.DSCRIPTN NOT LIKE '%KI%' AND
HDR.DSCRIPTN NOT LIKE '%HDWR%' AND
HDR.DSCRIPTN NOT LIKE '%FO%' AND
HDR.DSCRIPTN NOT LIKE '%APSS%' AND
HDR.DSCRIPTN NOT LIKE '%HARDWARE%' AND
LNE.SERLNMBR <>''
GROUP BY
LNE.SERLNMBR, HDR.CUSTNMBR, HDR.DSCRIPTN
HAVING COUNT(*)>1
ORDER BY HDR.CUSTNMBR, LNE.SERLNMBR

Results:
CUSTNMBR      ENDDATE                       DSCRIPTN                           SERIAL                 SERLNMBR
ATL0002        2007-10-31 00:00:00.000 Standard 8 to 8 e.s.t. CS     004886               004886-00
ATL0004        2008-04-30 00:00:00.000 Standard 8 to 8 e.s.t. CS     003625               003625-00
ATL0004        2008-04-30 00:00:00.000 Standard 8 to 8 e.s.t. CS     003625               003625-01
Required Results:
ATL0002        2007-10-31 00:00:00.000 Standard 8 to 8 e.s.t. CS     004886               004886-00
ATL0004        2008-04-30 00:00:00.000 Standard 8 to 8 e.s.t. CS     003625               003625-00
I don't need the SERLNMBR column, just the SERIAL. I left it in there to show why it isn't grouping correctly. I can't put the LEFT(LNE.SERLNMBR, 6) in the group by because there are other records that shouldn't be grouped based on the first 6 characters...only those with CS in the desc.
Any ideas would be great!

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you have to group by the case statment in this situation..it's wordy, but that's how it works...only in the ORDER BY clause can you reference by alias, so it's like this i believe:

SELECT HDR.CUSTNMBR,

MAX(HDR.ENDDATE) AS 'ENDDATE',

HDR.DSCRIPTN,

(CASE WHEN HDR.DSCRIPTN LIKE '%CS%'

THEN LEFT(LNE.SERLNMBR,6)

ELSE LNE.SERLNMBR

END) AS 'SERIAL',

LNE.SERLNMBR

FROM SVC00600 AS HDR

INNER JOIN SVC00601 AS LNE ON HDR.CONTNBR=LNE.CONTNBR

WHERE HDR.DSCRIPTN NOT LIKE '%KI%' AND

HDR.DSCRIPTN NOT LIKE '%HDWR%' AND

HDR.DSCRIPTN NOT LIKE '%FO%' AND

HDR.DSCRIPTN NOT LIKE '%APSS%' AND

HDR.DSCRIPTN NOT LIKE '%HARDWARE%' AND

LNE.SERLNMBR <>''

GROUP BY

CASE WHEN HDR.DSCRIPTN LIKE '%CS%'

THEN LEFT(LNE.SERLNMBR,6)

ELSE LNE.SERLNMBR

END,

HDR.CUSTNMBR,

HDR.DSCRIPTN

HAVING COUNT(*)>1

ORDER BY HDR.CUSTNMBR,SERIAL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SO-462683 Mr or Mrs. 500 Points: 565 More actions · Answer 2

SO-462683

Mr or Mrs. 500

Points: 565

August 7, 2007 at 10:36 am

#725239

Thank you! It works perfectly.