Aggravation over Aggregates ... arrrrrg …

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

General SQL Server Forums

New to SQL Server Programming

Aggravation over Aggregates ... arrrrrg …

Author

Topic

LaurieCox

158 Posts

Posted - 2007-02-13 : 16:27:31

Hi,

I have the following test data:


create table #TestData
(clt_num int,
 ins_num int)

Insert into #TestData
(clt_num, ins_num)
Select 16, 1 union all
Select 16, 90 union all
Select 16, 999999 union all
Select 16, 389 union all
Select 18, 1 union all
Select 18, 90 union all
Select 18, 999999 union all
Select 24, 999999 union all
Select 24, 1 union all
Select 31, 1 union all
Select 31, 999999 union all
Select 31, 90 union all
Select 31, 389 union all
Select 35, 999999 union all
Select 35, 389 union all
Select 283, 1 union all
Select 283, 90 union all
Select 283, 999999 union all
Select 283, 310 union all
Select 500, 1 union all
Select 100, 90

… which I then combine the ins_num into insurance types:


select clt_num,
	case
            when  ins_num = 1 then 'Caid'
	    when  ins_num = 90   then 'Care'
            when  ins_num in (189,195,310)  then 'HMO'
	    when  ins_num between 381 and 389 then 'TPO'
            when  ins_num = 999999 then 'TPO'
	end as InsuranceType
 from #TestData
order by clt_num

… and get the following results:


clt_num	InsuranceType
16	Caid
16	Care
16	TPO
16	TPO
18	Caid
18	Care
18	TPO
24	TPO
24	Caid
31	Caid
31	TPO
31	Care
31	TPO
35	TPO
35	TPO
100	Care
283	Caid
283	Care
283	TPO
283	HMO
500	Caid

But what I really want is only one line per clt_num and where if clients have more than 1 insurance type the insurance type should be replaced with dual:


clt_num	InsuranceType
16	Dual
18	Dual
24	Dual
31	Dual
35	TPO or Dual (?*)
100	Care
283	Dual
500	Caid

?* This is a special case and I do not know if the results should say Dual or not. I think TPO stands for Third Party Only (in which case they wouldn't care how many third parties the client has and the results should be TPO) … but if I am wrong then this should be Dual. I will ask my boss tomorrow to clarify, but it would be nice to have a solution for both ways.

I know I need to do something with count and stuff, but for some reason I have a mental block when it comes to agregate functions.

Thanks in advance for any help finishing up the query.

Laurie

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-02-13 : 16:34:06

[code]
Select clt_num, case when count(*) > 1 then 'Duel' else max(InsuranceType) end as InsuranceType
from
(
select clt_num,
case
when ins_num = 1 then 'Caid'
when ins_num = 90 then 'Care'
when ins_num in (189,195,310) then 'HMO'
when ins_num between 381 and 389 then 'TPO'
when ins_num = 999999 then 'TPO'
end as InsuranceType
from #TestData
) as X
group by clt_num
order by clt_num
[/code]

DavidM

Production is just another testing cycle

LaurieCox

158 Posts

Posted - 2007-02-14 : 11:31:23

Thanks David,

The query works great except for the twist with TPOs. If a given client has multiple third party insurance they should still be listed as TPO (not DUAL).

Using the following testdata (test condition given in comment for each client):


drop table #testdata
create table #TestData
(clt_num int,
 ins_num int)
Insert into #TestData
(clt_num, ins_num)
select 1, 381 union all -- multiple tpo's
select 1, 385 union all  
select 2, 1 union all --  multiple tpo and caid
select 2, 9999999 union all  
select 2, 381 union all  
select 3, 1 union all -- single caid
select 4, 384 union all -- single tpo
select 5, 1 union all -- caid/care
select 5, 90

David's query:


Select clt_num, case when count(*) > 1 then 'Dual' else max(InsuranceType) end as InsuranceType
from
(
select clt_num,
	case
            when  ins_num = 1 then 'Caid'
	    when  ins_num = 90   then 'Care'
            when  ins_num in (189,195,310)  then 'HMO'
	    when  ins_num between 381 and 389 then 'TPO'
            when  ins_num = 999999 then 'TPO'
	end as InsuranceType
 from #TestData
) as X
group by clt_num
order by clt_num

Returns this result
[code]
clt_num	InsuranceType
1	Dual
2	Dual
3	Caid
4	TPO
5	Dual

But I want this result:
[code]
clt_num	InsuranceType
1	TPO
2	Dual
3	Caid
4	TPO
5	Dual

Note: all output is the same except for client 1.

So is it possible to modify the query to give the requested results?

Thanks,

Laurie

LaurieCox

158 Posts

Posted - 2007-02-14 : 12:01:44

I just tried something ... I put a distinct on the inner select and got my desired output:


Select clt_num, case when count(*) > 1 then 'Dual' else max(InsuranceType) end as InsuranceType
from
(
select distinct clt_num,
	case
            when  ins_num = 1 then 'Caid'
	    when  ins_num = 90   then 'Care'
            when  ins_num in (189,195,310)  then 'HMO'
	    when  ins_num between 381 and 389 then 'TPO'
            when  ins_num = 999999 then 'TPO'
	end as InsuranceType
 from #TestData
) as X
group by clt_num
order by clt_num

Question: Could I have broken something else by this change? This might be a business question: If a person could have two Medicaids (does that even make sense? ... I will have to look at the real data) and if I want them as Dual then this would break it, wouldn't it? ...

What other questions should I ask?

I think I might have solved my problem ... but if there are any 'red flags' I should look at, think about or questions I should ask (myself or the user) that anybody knows I would appreciate hearing them.

Thanks,

Laurie

edited to fix irritating since/sense typo

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-14 : 12:14:04

[code]DECLARE @TestData TABLE (clt_num INT, ins_num INT)

INSERT @TestData
SELECT 16, 1 UNION ALL
SELECT 16, 90 UNION ALL
SELECT 16, 999999 UNION ALL
SELECT 16, 389 UNION ALL
SELECT 18, 1 UNION ALL
SELECT 18, 90 UNION ALL
SELECT 18, 999999 UNION ALL
SELECT 24, 999999 UNION ALL
SELECT 24, 1 UNION ALL
SELECT 31, 1 UNION ALL
SELECT 31, 999999 UNION ALL
SELECT 31, 90 UNION ALL
SELECT 31, 389 UNION ALL
SELECT 35, 999999 UNION ALL
SELECT 35, 389 UNION ALL
SELECT 283, 1 UNION ALL
SELECT 283, 90 UNION ALL
SELECT 283, 999999 UNION ALL
SELECT 283, 310 UNION ALL
SELECT 500, 1 UNION ALL
SELECT 100, 90

SELECT clt_num,
CASE
WHEN MIN(Offsite) + MAX(Offsite) = 1 THEN 'Dual'
ELSE MIN(InsuranceType)
END AS InsuranceType
FROM (
SELECT clt_num,
CASE
WHEN ins_num = 1 then 'Caid'
WHEN ins_num = 90 then 'Care'
WHEN ins_num in (189,195,310) then 'HMO'
WHEN ins_num between 381 and 389 then 'TPO'
WHEN ins_num = 999999 then 'TPO'
END AS InsuranceType,
CASE
WHEN ins_num between 381 and 389 then 1
WHEN ins_num = 999999 then 1
ELSE 0
END AS Offsite
FROM @TestData
) AS d
GROUP BY clt_num[/code]

Peter Larsson
Helsingborg, Sweden

LaurieCox

158 Posts

Posted - 2007-02-16 : 10:02:42

Hi Peso,

Thank you also for the help. That query looks kinda cool and when I have time I will enjoy figuring out how it works.

... Unfortunately they went and changed the specs for the report and decided that they wanted me to report on all insurance types and not bother with determining dual vs not dual.

So I changed it to a two step query with the second query being a pivot:


select distinct s.clt_num,
          f.fst_nme + ' ' + f.lst_nme as PrimaryClinician,
          p.pgm_nme as PrimaryProgram,
          i.ins_num,
          case
                when  i.ins_num in (1,594) then 'Caid'
                when  i.ins_num = 90   then 'Care'
                when  i.ins_num in (12,169,189,195,310,445)  then 'HMO'
                when  i.ins_num between 381 and 389 then 'OTHER'
                when  i.ins_num in (999999,578,433,577,48,596,541,540,491) then 'OTHER'
                else 'TPO'
          end as InsuranceType,
          c.ins_nme
   into #temp
  from v_autsvc s join cltdmo   d on s.clt_num = d.clt_num 
                  join facemp   f on d.priemp_num = f.facemp_num
                  join pgm      p on d.pripgm_num = p.pgm_num
                  join v_cltins i on s.clt_num = i.clt_num and i.exp_Dte > '10/1/06'
                  join cmh_ins_company c on i.ins_num = c.ins_num
 where s.pgm_num=1601 and autend_Dte > '10/1/06'


select clt_num,
       max(PrimaryClinician)as PrimaryClinician,
       max(PrimaryProgram)as PrimaryProgram,
       SUM(CASE WHEN InsuranceType = 'Caid'  THEN 1 ELSE 0 END)  AS 'Caid',
       SUM(CASE WHEN InsuranceType = 'Care'  THEN 1 ELSE 0  END) AS 'Care',
       SUM(CASE WHEN InsuranceType = 'HMO'   THEN 1 ELSE 0  END) AS 'HMO',
       SUM(CASE WHEN InsuranceType = 'TPO'   THEN 1 ELSE 0  END) AS 'TPO',
       SUM(CASE WHEN InsuranceType = 'OTHER' THEN 1 ELSE 0  END) AS 'OTHER'
  from #temp
 group by clt_num

As I am doing my best to learn how to write queries the 'right way', I wouldn't mind some constructive criticism of my code.

Thanks again to everyone here.

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources