sql join help - 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)

sql join help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-10-22 : 10:13:24

i have a table called

participants

with a

participantid
nameofprogram

then i have a table called
placements with a

placementid
participantid
status

there can be a number of placements per participant

how can i query the total number of placements per nameofprogram taking the nameofprogram from participant and looking at the latest record for that participant in placements and if it is a status of 1 or 2 then count it - if the latest record has any other status then don't count it.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-22 : 10:40:09

How do you determine the latest record?

select nameofprogram, count(distinct pl.participantid)
from participants p
left join placements pl
on p.participantid = pl.participantid
and pl.status in ((1,2)
group by nameofprogram

maybe
select nameofprogram, count(distinct pl.participantid)
from participants p
left join (select participantid, placementid = max(placementid) from placements group by participantid) plmax
on p.participantid = pl.participantid
left join placements p
on plmax.participantid = pl.participantid
and plmax.placementid = pl.placementid
and pl.status in ((1,2)
group by nameofprogram

==========================================
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