Query Help - GROUP BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query Help - GROUP BY

Author

Topic

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-08-24 : 11:55:32

I have a rather nasty query I'm hoping someone can help me work out.

Basically, I have a large number of medical claims that we've adjudicated, and I need to do a breakdown of approved and denied claims, and for the denied claims a breakdown of the reason(s) they were denied.

The claims are broken down into a provider and location; one provider can have one or more locations. The groupings that I need to make are a breakdown of each location and a final grouping for all locations for that provider combined.

The data contains a single CHAR(20) column that contains a string of zeros or possibly zeros and ones intermingled, where each position that contains a '1' indicates a denial reason. For example, a '1' in the first position means that the participant ID is invalid, and a '1' in the fifth position means that the claim is a duplicate of one previously submitted. A sequence of 20 zero characters means that the claim was approved. (I know - I didn't design it; it's legacy, and I can't change it at this point.)

Here's samples of the relevant DDL, data, and desired output.


CREATE TABLE CLAIMS(Provider Char(8), Locator Char(2), Comments Char(20));

INSERT INTO CLAIMS
SELECT '01234567', '01', '10001000000000000000' UNION ALL
SELECT '01234567', '01', '01000000000000000000' UNION ALL
SELECT '01234567', '02', '00100000000000000000'

Desired output:

PROVIDER  LOCATOR Reason1 Reason2 Reason3 Reason4 Reason5, etc.
========  ======= ======= ======= ======= ======= =======
01234567    01       1       1       0       0       0
01234567    02       0       0       1       0       0
01234567             1       1       1       0       1

I tried a very nasty query, to the effect of:


SELECT Provider, Locator,
  CASE SUBSTRING(Comments, 1, 1) 
    WHEN '1' THEN 1 ELSE 0 END AS Reason1,
  CASE SUBSTRING(Comments, 2, 1)
    WHEN '1' THEN 1 ELSE 0 END AS Reason2, ...
FROM CLAIMS
GROUP BY PROVIDER, LOCATOR, Reason1, ...

Obviously, listing 22 columns in the GROUP BY looked nasty, and didn't work correctly (the actual provider I'm using to work this out submitted a total of 981 claims in the batch I'm using for testing from three separate locations, but the query above returns only a total of 30 or so).

I realize I can get the total for all locations for the provider and union them into the GROUPed data at the end; I just can't seem to get the grouping to work right.

I appreciate any help you can give me. Thanks in advance.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-24 : 12:09:13

Just group by Comments, instead of reason1,Reason2, etc.

SELECT Provider,Locator,
SUM(reason1) as Reason1,
SUM(reason2) as Reason2
,etc.

FROM
(

SELECT Provider, Locator,
CASE SUBSTRING(Comments, 1, 1)
WHEN '1' THEN 1 ELSE 0 END AS Reason1,
CASE SUBSTRING(Comments, 2, 1)
WHEN '1' THEN 1 ELSE 0 END AS Reason2
FROM #CLAIMS
GROUP BY PROVIDER, LOCATOR,Comments
) a
GROUP BY Provider,locator

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-24 : 12:17:29

It's lunch time so

declare @x int
declare @sql varchar(1000)
set @sql = ''

SET @x= 2
WHILE @x <= 20
BEGIN
SET @sql = @sql + '
,CASE SUBSTRING(Comments,'+CONVERT(varchar(5),@x)+', 1) = ''1'' THEN 1 ELSE 0 END as Reason'+CONVERT(varchar(5),@x)
SET @x = @x+1
END
SELECT 'SELECT Provider,Locator '
+@sql
+' FROM Claims '
+'
GROUP BY Provider,Locator,Comments'

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-08-24 : 13:21:03

jimf,

That was quick, and helped with not having to put all of the ReasonX columns in the GROUP BY. Thanks!

However, it still doesn't work. For the provider mentioned above (981 total claims), it reports only 30.

Here's a larger sample of data (but smaller than 1K rows) to see if it'll help.


SELECT '00243105', '03', '10000100000000000000' UNION ALL
SELECT '00243105', '03', '00001000000000000000' UNION ALL
SELECT '00243105', '03', '10000100000000000000' UNION ALL
SELECT '00243105', '03', '00000000011000000000' UNION ALL
SELECT '00243105', '03', '00000000011000000000' UNION ALL
SELECT '00243105', '03', '00000000011000000000' UNION ALL
SELECT '00243105', '03', '00001000000000000000' UNION ALL
SELECT '00243105', '03', '10000000000000000000' UNION ALL
SELECT '00243105', '03', '10000100000000000000' UNION ALL
SELECT '00243105', '03', '10000100000000000000' UNION ALL
SELECT '00243105', '03', '00001000000000000000' UNION ALL
SELECT '00243105', '03', '10000100000000000000' UNION ALL
SELECT '00243105', '03', '10000100000000000000' UNION ALL
SELECT '00243105', '03', '10000100000000000000' UNION ALL
SELECT '00243105', '03', '10000100000000000000' UNION ALL
SELECT '00243105', '03', '00000000000000000000' UNION ALL
SELECT '00243105', '03', '00000000000000000000' UNION ALL
SELECT '00243105', '03', '00000000000000000000' UNION ALL
SELECT '00243105', '03', '00001000000000000000' UNION ALL
SELECT '00243105', '03', '00000000000000000010' UNION ALL
SELECT '00243105', '03', '00001000000100000000' UNION ALL
SELECT '00243105', '03', '00001000000000000000' UNION ALL
SELECT '00243105', '03', '00000000000000000000' UNION ALL
SELECT '00243105', '03', '00000000000000000000' UNION ALL
SELECT '00243105', '03', '00011000010000000000' UNION ALL
SELECT '00243105', '03', '00000000000000000010' UNION ALL
SELECT '00243105', '03', '00001000000100000000' UNION ALL
SELECT '00243105', '03', '10000100000000000000' UNION ALL
SELECT '00243105', '03', '00000010011000000000' UNION ALL
SELECT '00243105', '03', '00000010011000000000' UNION ALL
SELECT '00243105', '03', '10000100011000000000' UNION ALL
SELECT '00243105', '03', '00000000000000000000' UNION ALL
SELECT '00243105', '03', '00000000011000000000' UNION ALL
SELECT '00243105', '03', '10000100000000000000' UNION ALL
SELECT '00243105', '03', '10000100000000000000' UNION ALL
SELECT '00243105', '03', '10000100000000000000' UNION ALL
SELECT '00243105', '03', '00000000000000000010' UNION ALL
SELECT '00243105', '03', '00000000000000000000' UNION ALL
SELECT '00243105', '03', '00000000000000000000' UNION ALL
SELECT '00243105', '03', '00000000000000000000'

Desired Output:

LOCATOR R1 R2 R3 R4 R5 R6 R7 R8 R9 R10 R11 R12 R13 R14 R15 R16 R17 R18 R19 R20
======= == == == == == == == == == === === === === === === === === === === ===
03      14  0  0  1  8 13  2  0  0   8   7   0   0   0   0   0   0   0   3   0

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-24 : 13:46:25

Here you go

SELECT Locator
,SUM(CASE WHEN SUBSTRING(Comments,1, 1) = '1' THEN 1 ELSE 0 END) as Reason1
,SUM(CASE WHEN SUBSTRING(Comments,2, 1) = '1' THEN 1 ELSE 0 END) as Reason2
,SUM(CASE WHEN SUBSTRING(Comments,3, 1) = '1' THEN 1 ELSE 0 END) as Reason3
,SUM(CASE WHEN SUBSTRING(Comments,4, 1) = '1' THEN 1 ELSE 0 END) as Reason4
,SUM(CASE WHEN SUBSTRING(Comments,5, 1) = '1' THEN 1 ELSE 0 END) as Reason5
,SUM(CASE WHEN SUBSTRING(Comments,6, 1) = '1' THEN 1 ELSE 0 END) as Reason6
,SUM(CASE WHEN SUBSTRING(Comments,7, 1) = '1' THEN 1 ELSE 0 END) as Reason7
,SUM(CASE WHEN SUBSTRING(Comments,8, 1) = '1' THEN 1 ELSE 0 END) as Reason8
,SUM(CASE WHEN SUBSTRING(Comments,9, 1) = '1' THEN 1 ELSE 0 END) as Reason9
,SUM(CASE WHEN SUBSTRING(Comments,10, 1) = '1' THEN 1 ELSE 0 END) as Reason10
,SUM(CASE WHEN SUBSTRING(Comments,11, 1) = '1' THEN 1 ELSE 0 END) as Reason11
,SUM(CASE WHEN SUBSTRING(Comments,12, 1) = '1' THEN 1 ELSE 0 END) as Reason12
,SUM(CASE WHEN SUBSTRING(Comments,13, 1) = '1' THEN 1 ELSE 0 END) as Reason13
,SUM(CASE WHEN SUBSTRING(Comments,14, 1) = '1' THEN 1 ELSE 0 END) as Reason14
,SUM(CASE WHEN SUBSTRING(Comments,15, 1) = '1' THEN 1 ELSE 0 END) as Reason15
,SUM(CASE WHEN SUBSTRING(Comments,16, 1) = '1' THEN 1 ELSE 0 END) as Reason16
,SUM(CASE WHEN SUBSTRING(Comments,17, 1) = '1' THEN 1 ELSE 0 END) as Reason17
,SUM(CASE WHEN SUBSTRING(Comments,18, 1) = '1' THEN 1 ELSE 0 END) as Reason18
,SUM(CASE WHEN SUBSTRING(Comments,19, 1) = '1' THEN 1 ELSE 0 END) as Reason19
,SUM(CASE WHEN SUBSTRING(Comments,20, 1) = '1' THEN 1 ELSE 0 END) as Reason20
FROM #Claims
GROUP BY Locator

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-08-24 : 13:55:18

By Jove, I think you've got it! Thanks, JimF. I don't know what the heck I was doing wrong.

I was in the middle of posting a larger chunk of data and the last SQL I tried (based on your earlier post), which gave the wrong results, when I got the email saying you'd posted another reply. I appreciate the help, and the speed with which you gave it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources