Adding a COUNT subquery into main query

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

General SQL Server Forums

New to SQL Server Programming

Adding a COUNT subquery into main query

Author

Topic

jcp_mp2admin
Starting Member

1 Post

Posted - 2010-06-03 : 17:36:36

I've worked up a query to get information on work orders out of our system, but I want to add in a subquery to provide a historical count as well.

Here is the existing query:


WITH woqry AS (
SELECT empcode, wo.wonum, wodate, taskdesc, wotype, eqnum, rfocode, status, 
sum(isnull(reghrs,0)) AS reghrs, sum(isnull(othrs,0)) AS othrs 
FROM wo, woe 
WHERE wo.wonum=woe.wonum AND wodate='5/19/10' --user to insert date 
GROUP BY empcode, wo.wonum, wodate, taskdesc, wotype, eqnum, rfocode, status
)
SELECT empcode, woqry.wonum, wodate, taskdesc, wotype, woqry.eqnum, rfocode, status, reghrs, othrs, 
itemnum, description, qty, texts 
FROM (woqry LEFT JOIN woc ON woqry.wonum=woc.wonum) LEFT JOIN issrec ON woqry.wonum=numchargedto
ORDER BY empcode, woqry.wonum

The part I want to add in is:


--return this query for each line of previous query; eqnum is the link
SELECT count(wo.wonum) AS wo_count FROM wo, woe 
WHERE wotype='Repair' AND eqnum='401-Dematic' AND wo.wonum=woe.wonum AND wodate>getdate()-30

Any help or suggestions would be appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-06-05 : 06:43:11

something like


WITH woqry AS (
SELECT empcode, wo.wonum, wodate, taskdesc, wotype, eqnum, rfocode, status, 
sum(isnull(reghrs,0)) AS reghrs, sum(isnull(othrs,0)) AS othrs 
FROM wo, woe
OUTER APPLY(SELECT count(wo1.wonum) AS wo_count FROM wo1, woe1 
WHERE wotype='Repair' AND eqnum='401-Dematic' AND wo1.wonum=woe1.wonum AND wodate>getdate()-30
AND wo1.empcode=wo.empcode
)t 
WHERE wo.wonum=woe.wonum AND wodate='5/19/10' --user to insert date 
GROUP BY empcode, wo.wonum, wodate, taskdesc, wotype, eqnum, rfocode, status
)
SELECT empcode, woqry.wonum, wodate, taskdesc, wotype, woqry.eqnum, rfocode, status, reghrs, othrs, 
itemnum, description, qty, texts 
FROM (woqry LEFT JOIN woc ON woqry.wonum=woc.wonum) LEFT JOIN issrec ON woqry.wonum=numchargedto
ORDER BY empcode, woqry.wonum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources