using - 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)

using

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-06 : 10:09:37

Lance writes "What happens when you use "CASE" in the "GROUP BY"
create table #mem(
mem char(15),
min_date smalldatetime,
max_date smalldatetime,
iol char(8))
insert into #mem
select pe.insured_id,
min(l.entry_chron), --min(l.entry_chron),
max(l.entry_chron), --max(l.entry_chron),
CASE WHEN l.living_situation in ('L25','L28','L75','L85') then 'HOMELESS' else l.living_situation end as 'iol'
FROM PsyConsProd..Member_Living_Situation l,
PsyConsProd..Eligibility pe
where l.member_id in (SELECT distinct l1.member_id
FROM PsyConsProd..Member_Living_Situation l1
where l1.living_situation in ('L25','L28','L75','L85')
and l1.entry_chron < '01/01/2002')
and l.member_id = pe.member_id
and l.living_situation <> 'POSNET IN'
and l.entry_chron < '01/01/2002'
and pe.benefit_plan_id = 'HCAL'
group by pe.insured_id, CASE WHEN l.living_situation in ('L25','L28','L75','L85') then 'HOMELESS' else l.living_situation end

It seems to me that it drops duplicalte "HOMELESS" records but I don't understand why?"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-06 : 11:04:40

this will consider 'L25','L28','L75','L85' as one group.
It's always worth trying a few examples with a simple temp table if you're not suer about something.

create table #a (i int, j int)
insert #a select 1,2
insert #a select 1,3
insert #a select 1,2
insert #a select 1,4

select case when j in (2,3) then 9 else j end, count(*) from #a
group by case when j in (2,3) then 9 else j end
drop table #a

----------- -----------
4 1
9 3

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources