more - Aggravation over Aggregates

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

more - Aggravation over Aggregates

Author

Topic

LaurieCox

158 Posts

Posted - 2007-02-20 : 16:31:57

I have data that looks like this


create table #TestData
(clt_num  int,
 proc_cde varchar(10))
insert #TestData (clt_num,proc_cde)
select 1000, 'H0017' union all
select 2000, 'T1016' union all
select 2000, 'H0036' union all
select 2000, 'T0017' union all
select 3000, '99999' union all
select 3000, 'AAAAA' union all
select 3000, 'H0039'
select 4000, '99999' union all
select 5000, 'H0017' union all
select 5000, 'H0066' union all
select 100, 'H0036;TT' union all
select 200, 'T1016;XX' union all
select 200, 'H0036' union all
select 300, '99999;HH' union all
select 300, 'AAAAA' union all
select 400, '99999' union all
select 500, 'H0017;15' union all
select 500, 'H0036;XX'

I want to select records for a given clt_num based on weights (precedence) given to the different proc_cde(s). The rules for selecting the rows are:

If clt_num only has one row select that row

Select row that has the highest proc_cde by precedence for given clt_num

If clt_num has two or more rows but no proc_cde with an assigned precedence select both rows.
Note that there is a twist with the proc_cds in that they can have garbage at the end in the data records and I don't care about the garbage H0036, H1036;XX will have the same weight.

Therefore my expected results are:


clt_num	proc_cde
100	H0036;TT
200	T1016;XX
300	99999;HH
300	AAAAA
400	99999
500	H0036;XX
1000	H0017
2000	T0017
3000	99999
3000	AAAAA
4000	99999
5000	H0039

In attempting to solve this problem I created a temp table called ProcCde_Weights:


create table #ProcCde_Weights
 (proc_cde varchar(10),
  weight int)
insert #ProcCde_Weights
(proc_cde,weight)
select 'H0039', 10 union all
select 'T1017', 20 union all
select 'T1016', 30 union all
select 'H0036', 40 union all
select 'T2011', 50 union all
select '90806', 60 union all
select '90862', 70 union all
select 'T1002', 80 union all
select 'H2031', 90 union all
select 'H2023', 100

And wrote this query:


select t.clt_num, 
       t.proc_cde,  
       case when p.weight is null then 1000
            else p.weight
       end as weight
  from #TestData t left join #ProcCde_Weights p on t.clt_num = t.clt_num 
                                                and p.proc_cde = left(t.proc_cde,5)

which gives me this:


clt_num	proc_cde	weight
1000	H0017	1000
2000	T1016	30
2000	H0036	40
2000	T0017	1000
3000	99999	1000
3000	AAAAA	1000
4000	99999	1000
5000	H0017	1000
5000	H0066	1000
100	H0017;TT	1000
200	T1016	30
200	H0036	40
300	99999;HH	1000
300	AAAAA	1000
400	99999	1000
500	H0017;15	1000
500	H0036;XX	40

… but now I need to modify the query with using the min function or max function (I think) to only select rows according to the rules I gave.

… and once again I seem to have a mental block when it comes to composing queries with aggregate functions.

Thanks in advance for any help.

Laurie

p.s. can anybody point me to any books or tutorials that can help me wrap my mind around aggregate functions.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-20 : 18:59:52

[code]
select t.clt_num,
t.proc_cde,
case when p.weight is null then 1000
else p.weight
end as weight
from #TestData t inner join
(
select clt_num, proc_cde = max(left(proc_cde, 5))
from #TestData
group by clt_num
) m
on t.clt_num = m.clt_num
and left(t.proc_cde, 5) = m.proc_cde
left join #ProcCde_Weights p
on t.clt_num = t.clt_num
and p.proc_cde = left(t.proc_cde,5)
[/code]

LaurieCox

158 Posts

Posted - 2007-02-21 : 11:27:01

Note: though this is a long, there is a question at the end…

Hi khtan,

Thanks for the reply; unfortunately it does not solve the problem, which is my fault as I was in a hurry to get the post made before quitting time and there are a number of errors in the OP both in grammar and in facts.

The first one being the ddl for the #TestData (there was a missing union all) it should have been this:


insert #TestData (clt_num,proc_cde)
select 1000, 'H0017' union all
select 2000, 'T1016' union all
select 2000, 'H0036' union all
select 2000, 'T0017' union all
select 3000, '99999' union all
select 3000, 'AAAAA' union all
select 3000, 'H0039' union all
select 4000, '99999' union all
select 5000, 'H0017' union all
select 5000, 'H0066' union all
select 100, 'H0036;TT' union all
select 200, 'T1016;XX' union all
select 200, 'H0036' union all
select 300, '99999;HH' union all
select 300, 'AAAAA' union all
select 400, '99999' union all
select 500, 'H0017;15' union all
select 500, 'H0036;XX'

… but more importantly I did not explain the purpose of the #ProcCde_Weights table very well. The proc_cde selected for a given client is based on the "precedence" of the code which does not translate to alphabetical order.

There are a three 'special cases' in the data:

Some proc_cdes have garbage after them in the #TestData table, but in selecting by weight I don't care about the garbage i.e. H0036, H0036;XX, H0036YYY will all have the same weight.

Some of the proc_cdes have not been given weights.

If a client is associated with multiple proc_cdes and none have a weight (i.e. the proc_cdes are not in #ProcCde_Weights) then all rows for that client should be in the result set.

Another point that I really messed up on, was using weight and precedence interchangeably and then talking about higher precedence and greater weight as if they meant the same thing. When you think of precedence you think that 1 has a higher precedence than 2 (or at least I do). When you talk about greater weight than 2 would have a greater weight than 1. So if you go with the former the function to use would be min and if with the latter it would be max. I meant the former.

… Anyway, khtan, even though your sql did not solve the problem it did let me move forward a little bit. I can now get the expected results with the following two queries:


select t.clt_num, 
       t.proc_cde,  
       case when p.weight is null then 1000
            else p.weight
       end as weight
  into #temp
  from #TestData t left join #ProcCde_Weights p on t.clt_num = t.clt_num 
                                                and p.proc_cde = left(t.proc_cde,5)

select t1.clt_num, t1.proc_cde
  from #temp t1
 where weight = (select min(weight)
                   from #temp t2
                  where t1.clt_num = t2.clt_num
                    )
order by t1.clt_num

… and now to the promised question:

How do I combine these into one query or is it better to not combine them?

Thanks,

Laurie

p.s.
There were also some errors in the expected results. The expected results should be:


clt_num	proc_cde
100	H0036;TT
200	T1016;XX
300	99999;HH
300	AAAAA
400	99999
500	H0036;XX
1000	H0017
2000	T1016
3000	H0039
4000	99999
5000	H0017
5000	H0066

edited: to change "couple of 'special cases'" to "three 'special cases'" … because I can't count.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-21 : 23:36:45

[code]
drop table #TestData
drop table #ProcCde_Weights

create table #TestData
(
clt_num int,
proc_cde varchar(10)
)

create table #ProcCde_Weights
(
proc_cde varchar(10),
weight int
)

insert #TestData (clt_num,proc_cde)
select 1000, 'H0017' union all
select 2000, 'T1016' union all
select 2000, 'H0036' union all
select 2000, 'T0017' union all
select 3000, '99999' union all
select 3000, 'AAAAA' union all
select 3000, 'H0039' union all
select 4000, '99999' union all
select 5000, 'H0017' union all
select 5000, 'H0066' union all
select 100, 'H0036;TT' union all
select 200, 'T1016;XX' union all
select 200, 'H0036' union all
select 300, '99999;HH' union all
select 300, 'AAAAA' union all
select 400, '99999' union all
select 500, 'H0017;15' union all
select 500, 'H0036;XX'

insert #ProcCde_Weights (proc_cde,weight)
select 'H0039', 10 union all
select 'T1017', 20 union all
select 'T1016', 30 union all
select 'H0036', 40 union all
select 'T2011', 50 union all
select '90806', 60 union all
select '90862', 70 union all
select 'T1002', 80 union all
select 'H2031', 90 union all
select 'H2023', 100

select t.clt_num,
t.proc_cde
from #TestData t inner join
(
select clt_num, proc_cde = max(left(proc_cde, 5))
from #TestData
group by clt_num
) m
on t.clt_num = m.clt_num
and left(t.proc_cde, 5) = m.proc_cde
inner join #ProcCde_Weights p
on p.proc_cde = left(t.proc_cde,5)

union all

select t.clt_num,
t.proc_cde
from #TestData t left join
(
select d.clt_num
from #TestData d inner join #ProcCde_Weights w
on left(d.proc_cde, 5) = w.proc_cde
) x
on t.clt_num = x.clt_num
where x.clt_num is null
order by t.clt_num

/*
clt_num proc_cde
----------- ----------
100 H0036;TT
200 T1016;XX
300 99999;HH
300 AAAAA
400 99999
500 H0036;XX
1000 H0017
2000 T1016
3000 H0039
4000 99999
5000 H0017
5000 H0066

(12 row(s) affected)
*/
[/code]

LaurieCox

158 Posts

Posted - 2007-02-22 : 08:18:22

Thanks khtan,

I will pour over your query and figure out how it works.

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources