Help with subquery - 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 2005 Forums

Transact-SQL (2005)

Help with subquery

Author

Topic

swsmoore
Starting Member

5 Posts

Posted - 2007-08-05 : 10:46:05

I have two queryies that I need to combine to give me proper aggregated values. The first query is:

select Employees.EmpId,
(Employees.FirstName + ' ' + LastName) as Salesperson,
count(distinct Transactions.TranID) as NumDesigns, sum(Transactions.MinDCWatts/1000) as TotalkW, avg(Transactions.MinDCWatts/1000) as AvgkW
from Employees inner join Transactions on
Employees.EmpId = Transactions.SalesPersonEmpId
inner join SysConfig on
Transactions.TranId = SysConfig.TranId
inner join SysConfigStatusHistory on
SysConfigStatusHistory.SysConfigID = SysConfig.SysConfigID
where Transactions.TranStatusID in (7,9)
and SysConfigStatusHistory.SysConfigStatusIDNew = 7
and SysConfigStatusHistory.StatusChangeDate > @startdate
and SysConfigStatusHistory.StatusChangeDate < dateadd(d, 1, @enddate)
and Transactions.SalesPersonEmpID in (2,3,4,9,12,15,16,18)
group by Employees.EmpId, Employees.FirstName, Employees.LastName

The problem is that for every Transactions.TranId there could be several SysConfigId's with a SysConfigStatusIDNew of 7. I only want the latest one or else the TranIds are double counted. I was able to write a subquery that pulls the latest one but I'm not sure how to add this subquery into the larger query. Here is the subquery:

select Top 1 * from dbo.SysConfigStatusHistory
inner join dbo.SysConfig on dbo.SysConfig.SysConfigID = dbo.SysConfigStatusHistory.SysConfigID
where SysConfig.TranId = Transactions.TranID
and SysConfigStatusIdNew = 7
order by SysConfigStatusHistory.StatusChangeDate desc

I appreciate any help you can offer.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-06 : 13:40:09

I'm not sure if this will work as I cannot test it. You may need to do a group by on the StatusChangeDate. If it doesn't work, post some sample data ande expected results and I'm sure we can help you out.

SELECT *
FROM
	(
	select 
		Employees.EmpId,
		(Employees.FirstName + ' ' + LastName) as Salesperson,
		count(distinct Transactions.TranID) as NumDesigns, 
		sum(Transactions.MinDCWatts/1000) as TotalkW, 
		avg(Transactions.MinDCWatts/1000) as AvgkW,
		ROW_NUMBER() OVER(PARTITION BY SysConfigStatusHistory.StatusChangeDate ORDER BY SysConfigStatusHistory.StatusChangeDate DESC) AS RowNumber
	from 
		Employees 
	inner join 
		Transactions 
		on Employees.EmpId = Transactions.SalesPersonEmpId
	inner join 
		SysConfig 
		on Transactions.TranId = SysConfig.TranId
	inner join 
		SysConfigStatusHistory 
		on SysConfigStatusHistory.SysConfigID = SysConfig.SysConfigID
	where 
		Transactions.TranStatusID in (7,9)
		and SysConfigStatusHistory.SysConfigStatusIDNew = 7
		and SysConfigStatusHistory.StatusChangeDate > @startdate
		and SysConfigStatusHistory.StatusChangeDate < dateadd(d, 1, @enddate)
		and Transactions.SalesPersonEmpID in (2,3,4,9,12,15,16,18)
	group by 
		Employees.EmpId, 
		Employees.FirstName, 
		Employees.LastName
	) AS T
WHERE
	RowNumber = 1

Additionally, without knowing your table size/structure, I'd think that pulling all the SysConfigStatusHistory.SysConfigIDs that you want to work with into a table variable or temp table would get you better performance.

-Ryan

swsmoore
Starting Member

5 Posts

Posted - 2007-08-10 : 15:14:40

I was able to get it to work using a correlated subquery:

select Employees.EmpId,
(Employees.FirstName + ' ' + LastName) as Salesperson,
count(distinct t.TranID) as NumDesigns,
sum(SysConfig.DCWatts/1000) as TotalkW,
avg(SysConfig.DCWatts/1000) as AvgkW
from Employees
inner join Transactions t on
Employees.EmpId = t.SalesPersonEmpId
inner join SysConfig on
t.TranId = SysConfig.TranId
inner join SysConfigStatusHistory on
SysConfigStatusHistory.SysConfigID = SysConfig.SysConfigID
where t.TranStatusID in (7,9)
and SysConfigStatusHistory.SysConfigStatusHistoryID =
(
select Top 1 SysConfigStatusHistoryID from SysConfigStatusHistory
inner join SysConfig on SysConfig.SysConfigID = SysConfigStatusHistory.SysConfigID
where SysConfig.TranId = t.TranId
and SysConfigStatusIdNew = 7
order by SysConfigStatusHistory.StatusChangeDate desc
)
and SysConfigStatusHistory.SysConfigStatusIDNew = 7
and SysConfigStatusHistory.StatusChangeDate between @startdate and dateadd(d, 1, @enddate)
and t.SalesPersonEmpID in (2,3,4,9,12,15,16,18)
group by Employees.EmpId, Employees.FirstName, Employees.LastName

I also got it to work by putting the correlated subquery into a UDF. That makes it usable in other queries. Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources