problems with #tables

[mossbs]

hey guys,

right... trying to do a stored procedure to bring back a table showing a count of members who have certain attributes etc.....

i can't seem to figure out how to stick all the values in one row... the reason is at present i'm doing a different insert statment for each one.... each column has different 'where' criteria to match... example script below......

create table #table1 (
fm_world int,
bifm int,
third_party int,
fm_daily int,
east int,
home_counties int,
international int,
london int,
midlands int,
north int,
scotland int,
south int,
south_west int,
charity int,
education int,
healthcare int,
retail int,
building_serv int,
health_safety int,
info_manag int,
sig_international int,
procurement int,
rising_fm int,
security int,
sustain int,
workplace int
)

--FM World

insert into #table1 (fm_world)
select count(*) from individual i
left join member m on m.individual_ref = i.individual_ref
left join attribute a on a.individual_ref = i.individual_ref

where m.member_status = 33
and m.join_date is not null
and m.leave_date is null
and a.attr_code_ref = 1322

--BIFM

insert into #table1 (bifm)
select count(*) from individual i
left join member m on m.individual_ref = i.individual_ref
left join attribute a on a.individual_ref = i.individual_ref

where m.member_status = 33
and m.join_date is not null
and m.leave_date is null
and a.attr_code_ref = 2191

-- 3rd Party

insert into #table1 (third_party)
select count(*) from individual i
left join member m on m.individual_ref = i.individual_ref
left join attribute a on a.individual_ref = i.individual_ref

where m.member_status = 33
and m.join_date is not null
and m.leave_date is null
and a.attr_code_ref = 1326


-- FM Daily

insert into #table1 (fm_daily)
select count(*) from individual i
left join member m on m.individual_ref = i.individual_ref
left join attribute a on a.individual_ref = i.individual_ref

where m.member_status = 33
and m.join_date is not null
and m.leave_date is null
and a.attr_code_ref = 2843



select * from #table1


drop table #table1



apologies for massive script!!

any ideas how to get it into one row?

 

[gmmastros]

Sounds to me like you don't need a temp table at all. Instead, you could create variable within the stored procedure and then return the data at the end.

[!]Declare @fm_world int[/!]
Declare @bifm int
--FM World

select [!]@fm_world =[/!] count(*) from  individual i
left join member m on m.individual_ref = i.individual_ref
left join attribute a on a.individual_ref = i.individual_ref
where      m.member_status = 33
    and m.join_date is not null
    and m.leave_date is null
    and a.attr_code_ref = 1322

--BIFM

select @bifm = count(*) from  individual i
left join member m on m.individual_ref = i.individual_ref
left join attribute a on a.individual_ref = i.individual_ref
where      m.member_status = 33
    and m.join_date is not null
    and m.leave_date is null
    and a.attr_code_ref = 2191

Select [!]@fm_world As fm_world, [/!]
       @bifm As bifm

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mossbs]

awesome!!

cheers george!

knew i was going the long way around!

 

[gmmastros]

You know... I just took a closer look at the individual queries, and there's another way. A better way.

select count(Case When attr_code_ref = 1322 Then 1 End) As fm_world,
       count(Case When attr_code_ref = 2191 Then 1 End) As bifm,
       Count(Case When attr_code_ref = 1326 Then 1 End) As third_party,
       Count(Case When attr_code_ref = 2843 Then 1 End) As fm_daily
from  individual i
left join member m on m.individual_ref = i.individual_ref
left join attribute a on a.individual_ref = i.individual_ref
where      m.member_status = 33
    and m.join_date is not null
    and m.leave_date is null

Notice that I removed the where clause part for attr_code_ref and put that in the select part as a case when statement.

When you use the aggregate function Count, with a column name (or in this case, and expression), only non-null values are counted. The Case/When for each column will return a 1 if there is a match, and NULL otherwise.

This method is much better than the previous version because there only needs to be 1 pass through the table(s) instead of a separate query for each value. Your performance will improve.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mossbs]

yup gotcha on that one man!

cheers!

performance from 17 secs down to 2!!!

cheers dude!