Aggregate Case Conditions

I have a table AAA which contains two columns fileid and typecd
for single fileid there can be either 1, 2 or 3 typecds home, host and
payroll
for e.g.
Fileid Typecd
100 Home
100 host
105 home
106 host
106 payroll
107 home
107 host
107 payroll
Now there is second table BBB which contains fileid(primary key) and
itemflag
Now i have to set itemflag for a fileid based on the above table.. like
if fileid contains only home then itemflag should be 1
only host then itemflag should be 2
only payroll then itemflag should be 3
home and host then itemflag should be 4
home and payroll then itemflag should be 5
host and payroll then itemflag should be 6
home host and payroll then itemflag should be 7
I tried this but this is not working giving error.
Insert into BBB
Select AAA.fileid,
(select
case sum(case AAA.typeCd when 'Home' then 1
when 'Host' then 2
when 'Payroll' then 4 end)
when 1 then 1
when 2 then 2
when 3 then 4
when 4 then 3
when 5 then 5
when 6 then 6
when 7 then 7 end
) as ItemFlg,
FROM AAA (nolock)
This is not working because white fetching first row from AAA, the
typecd can contain any of the three value,it is
not having all the three values at a single time.
Can any one help me with this.
Regards,
RajeevTry,
select a.fileid, sum(b.c1) + case when count(*) > 1 then 1 else 0 end as
ItemFlg
from t1 inner join (
select 'home' as Typecd, 1 as c1
union all
select 'host' as Typecd, 2 as c1
union all
select 'payroll' as Typecd, 3 as c1
) as t2
on a.Typecd = b.Typecd
group by a.fileid
go
AMB
"Rajeev" wrote:

> I have a table AAA which contains two columns fileid and typecd
> for single fileid there can be either 1, 2 or 3 typecds home, host and
> payroll
> for e.g.
> Fileid Typecd
> 100 Home
> 100 host
> 105 home
> 106 host
> 106 payroll
> 107 home
> 107 host
> 107 payroll
> Now there is second table BBB which contains fileid(primary key) and
> itemflag
> Now i have to set itemflag for a fileid based on the above table.. like
> if fileid contains only home then itemflag should be 1
> only host then itemflag should be 2
> only payroll then itemflag should be 3
> home and host then itemflag should be 4
> home and payroll then itemflag should be 5
> host and payroll then itemflag should be 6
> home host and payroll then itemflag should be 7
> I tried this but this is not working giving error.
> Insert into BBB
> Select AAA.fileid,
> (select
> case sum(case AAA.typeCd when 'Home' then 1
> when 'Host' then 2
> when 'Payroll' then 4 end)
> when 1 then 1
> when 2 then 2
> when 3 then 4
> when 4 then 3
> when 5 then 5
> when 6 then 6
> when 7 then 7 end
> ) as ItemFlg,
> FROM AAA (nolock)
> This is not working because white fetching first row from AAA, the
> typecd can contain any of the three value,it is
> not having all the three values at a single time.
> Can any one help me with this.
> Regards,
> Rajeev
>|||Try,
select a.fileid, sum(b.c1) + case when count(*) > 1 then 1 else 0 end as
ItemFlg
from t1 inner join (
select 'home' as Typecd, 1 as c1
union all
select 'host' as Typecd, 2 as c1
union all
select 'payroll' as Typecd, 3 as c1
) as t2
on a.Typecd = b.Typecd
group by a.fileid
go
AMB
"Rajeev" wrote:

> I have a table AAA which contains two columns fileid and typecd
> for single fileid there can be either 1, 2 or 3 typecds home, host and
> payroll
> for e.g.
> Fileid Typecd
> 100 Home
> 100 host
> 105 home
> 106 host
> 106 payroll
> 107 home
> 107 host
> 107 payroll
> Now there is second table BBB which contains fileid(primary key) and
> itemflag
> Now i have to set itemflag for a fileid based on the above table.. like
> if fileid contains only home then itemflag should be 1
> only host then itemflag should be 2
> only payroll then itemflag should be 3
> home and host then itemflag should be 4
> home and payroll then itemflag should be 5
> host and payroll then itemflag should be 6
> home host and payroll then itemflag should be 7
> I tried this but this is not working giving error.
> Insert into BBB
> Select AAA.fileid,
> (select
> case sum(case AAA.typeCd when 'Home' then 1
> when 'Host' then 2
> when 'Payroll' then 4 end)
> when 1 then 1
> when 2 then 2
> when 3 then 4
> when 4 then 3
> when 5 then 5
> when 6 then 6
> when 7 then 7 end
> ) as ItemFlg,
> FROM AAA (nolock)
> This is not working because white fetching first row from AAA, the
> typecd can contain any of the three value,it is
> not having all the three values at a single time.
> Can any one help me with this.
> Regards,
> Rajeev
>