how to combine sql statement - 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)

how to combine sql statement

Author

Topic

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-01-18 : 15:05:22

Hi, I'm trying to clean up my code by combining the following 2 statements. Any help is appreciated.

select measurename, dptcount=nullif(sum(DptCount),0)
into #Counts
from #F1
where delaycode='total' and delaygroup='total'
group by measurename

select dtStartDate=@startdate, dtEndDate=@enddate,
a.DelayGroup,
a.DelayCode,
NDelays=isnull(sum(a.NDelays),0),
b.DptCount,
Percentage=cast(isnull(sum(a.NDelays),0) as dec(18,10) ) / b.dptcount*100,
metric=a.MeasureName
from #F2 as a
JOIN #Counts as b
ON a.measurename=b.measurename
group by a.measureName,a.DelayGroup,a.DelayCode,b.dptcount

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-01-18 : 15:26:34

You can write the first part as a correlated subquery.

Select .... from ...
where fieldname in (Select ... from ...)

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-01-18 : 17:21:12

Thanks for the quick reply. A co-worker pointed out I could just paste the first statement into the second as a derived table. DUH... I think I lost it. Thanks again.

select dtStartDate=@startdate, dtEndDate=@enddate,
a.DelayGroup,
a.DelayCode,
NDelays=isnull(sum(a.NDelays),0),
b.DptCount,
Percentage=cast(isnull(sum(a.NDelays),0) as dec(18,10) ) / b.dptcount*100,
metric=a.MeasureName
from #F2 as a
JOIN
(
select measurename, dptcount=nullif(sum(DptCount),0)
from #F1
where delaycode='total' and delaygroup='total'
group by measurename
) as b
ON a.measurename=b.measurename
group by a.measureName,a.DelayGroup,a.DelayCode,b.dptcount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources