Is this REALLY efficient? - 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

SQL Server Development (2000)

Is this REALLY efficient?

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-11-05 : 10:38:54

[code]
select parent_table.positionname, parent_table.lastdateadded, parent_table.totals, child1.phd, child2.cert, child3.hs

from

(select dbo.positionsappliedfor.positionname, max(dbo.resumes.dateadded) as lastdateadded,
count(dbo.positionsappliedfor.positionname) as totals
from dbo.resumes inner join
dbo.positionsappliedfor on dbo.resumes.resumeid = dbo.positionsappliedfor.resumeid
where (month(dbo.resumes.dateadded) = month(getdate()) - 1) and (year(dbo.resumes.dateadded) = year(getdate()))
group by dbo.positionsappliedfor.positionname, dbo.positionsappliedfor.positionname
) as parent_table

left join

(select dbo.positionsappliedfor.positionname, count(dbo.positionsappliedfor.positionname) as phd
from dbo.resumes inner join
dbo.positionsappliedfor on dbo.resumes.resumeid = dbo.positionsappliedfor.resumeid
where resumes.education = 'phd' and (month(dbo.resumes.dateadded) = month(getdate()) - 1) and (year(dbo.resumes.dateadded) = year(getdate()))
group by dbo.positionsappliedfor.positionname, dbo.positionsappliedfor.positionname
) as child1

on parent_table.positionname = child1.positionname

left join

(select dbo.positionsappliedfor.positionname, count(dbo.positionsappliedfor.positionname) as cert
from dbo.resumes inner join
dbo.positionsappliedfor on dbo.resumes.resumeid = dbo.positionsappliedfor.resumeid
where resumes.education = 'cert' and (month(dbo.resumes.dateadded) = month(getdate()) - 1) and (year(dbo.resumes.dateadded) = year(getdate()))
group by dbo.positionsappliedfor.positionname, dbo.positionsappliedfor.positionname
) as child2

on parent_table.positionname = child2.positionname

left join

(select dbo.positionsappliedfor.positionname, count(dbo.positionsappliedfor.positionname) as hs
from dbo.resumes inner join
dbo.positionsappliedfor on dbo.resumes.resumeid = dbo.positionsappliedfor.resumeid
where resumes.education = 'hs' and (month(dbo.resumes.dateadded) = month(getdate()) - 1) and (year(dbo.resumes.dateadded) = year(getdate()))
group by dbo.positionsappliedfor.positionname, dbo.positionsappliedfor.positionname
) as child3

on parent_table.positionname = child3.positionname

order by totals desc
[/code]

Alex Polajenko

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-05 : 10:44:19

Not for the eyes

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-11-05 : 10:45:20

I am doing what is mentioned here

http://www.sqlteam.com/item.asp?ItemID=6692

Alex Polajenko

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-05 : 14:08:41

unless you give us some sample data, your table structures, and what you are trying to return, I don't know how anyone can tell you if it's efficient or not.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources