Joining two queries - 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)

Joining two queries

Author

Topic

foxprorawks
Starting Member

17 Posts

Posted - 2009-08-24 : 10:12:24

I have two queries which I wish to join, but I can't work out how to do it.

The first query is:

SELECT * FROM
(SELECT dept,
COUNT(*) AS TotalRAs,
SUM(CASE WHEN assess > DATEADD(YY,-1,GetDate()) THEN 1 ELSE 0 END) AS NewRAs,
SUM(CASE WHEN assess > DATEADD(yy,-2,GetDate()) AND reassess > DATEADD(mm,3,GetDate()) THEN 1 ELSE 0 END) AS Updated,
SUM(CASE WHEN assess > DATEADD(yy,-2,GetDate()) AND reassess < DATEADD(mm,3,GetDate()) THEN 1 ELSE 0 END) AS DueReview,
SUM(CASE WHEN assess < DATEADD(yy,-2,GetDate()) AND reassess < GetDate() THEN 1 ELSE 0 END) AS Overdue
FROM newactrisk
GROUP BY dept) activityRiskByDept

The second query is:

SELECT * FROM
(SELECT dept, SUM(Complete) AS aComplete, SUM(InProgress) AS aInProgress, SUM(Overdue) AS aOverdue, SUM(Total) AS aTotal
FROM
(SELECT dept,
(SELECT COUNT(*) AS complete FROM action WHERE(status = 'Completed') AND (parentid = newactrisk.idnewactrisk) AND (tablename = 'newactrisk')) AS Complete,
(SELECT COUNT(*) AS inprogress FROM action WHERE (status <> 'Completed') AND (dueon > GetDate()) AND (parentid = newactrisk.idnewactrisk) AND (tablename = 'newactrisk')) AS InProgress,
(SELECT COUNT(*) AS overdue FROM action WHERE(status <> 'Completed') AND (dueon < GetDate()) AND (parentid = newactrisk.idnewactrisk) AND (tablename = 'newactrisk')) AS Overdue,
(SELECT COUNT(*) AS total FROM action WHERE (parentid = newactrisk.idnewactrisk) AND (tablename = 'newactrisk')) AS Total
FROM newactrisk) actionSummary
GROUP BY dept) actionSummaryByDept

The first query returns the fields: dept, TotalRAs, NewRAs, Updated, DueReview and Overdue.

The second query returns the fields: dept, aComplete, aInProgress, aOverdue and aTotal.

I'd like to join on the dept field, giving me the result:

dept, TotalRAs, NewRAs, Updated, DueReview, Overdue, aComplete, aInProgress, aOverdue and aTotal.

Can anyone help?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-24 : 10:17:47

select dept, TotalRAs, NewRAs, Updated, DueReview, Overdue, aComplete, aInProgress, aOverdue and aTotal from

(SELECT * FROM
(SELECT dept,
COUNT(*) AS TotalRAs,
SUM(CASE WHEN assess > DATEADD(YY,-1,GetDate()) THEN 1 ELSE 0 END) AS NewRAs,
SUM(CASE WHEN assess > DATEADD(yy,-2,GetDate()) AND reassess > DATEADD(mm,3,GetDate()) THEN 1 ELSE 0 END) AS Updated,
SUM(CASE WHEN assess > DATEADD(yy,-2,GetDate()) AND reassess < DATEADD(mm,3,GetDate()) THEN 1 ELSE 0 END) AS DueReview,
SUM(CASE WHEN assess < DATEADD(yy,-2,GetDate()) AND reassess < GetDate() THEN 1 ELSE 0 END) AS Overdue
FROM newactrisk
GROUP BY dept) activityRiskByDept) tab1

inner join

(SELECT * FROM
(SELECT dept, SUM(Complete) AS aComplete, SUM(InProgress) AS aInProgress, SUM(Overdue) AS aOverdue, SUM(Total) AS aTotal
FROM
(SELECT dept,
(SELECT COUNT(*) AS complete FROM action WHERE(status = 'Completed') AND (parentid = newactrisk.idnewactrisk) AND (tablename = 'newactrisk')) AS Complete,
(SELECT COUNT(*) AS inprogress FROM action WHERE (status <> 'Completed') AND (dueon > GetDate()) AND (parentid = newactrisk.idnewactrisk) AND (tablename = 'newactrisk')) AS InProgress,
(SELECT COUNT(*) AS overdue FROM action WHERE(status <> 'Completed') AND (dueon < GetDate()) AND (parentid = newactrisk.idnewactrisk) AND (tablename = 'newactrisk')) AS Overdue,
(SELECT COUNT(*) AS total FROM action WHERE (parentid = newactrisk.idnewactrisk) AND (tablename = 'newactrisk')) AS Total
FROM newactrisk) actionSummary
GROUP BY dept) actionSummaryByDept)

tab2 on tab1.dept=tab2.dept

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2009-08-24 : 10:21:10

declare each as a subquery, and join them. like this:


select * from 
(
SELECT dept, TotalRAs, NewRAs, Updated, DueReview, Overdue FROM
(SELECT dept,
COUNT(*) AS TotalRAs,
SUM(CASE WHEN assess > DATEADD(YY,-1,GetDate()) THEN 1 ELSE 0 END) AS NewRAs,
SUM(CASE WHEN assess > DATEADD(yy,-2,GetDate()) AND reassess > DATEADD(mm,3,GetDate()) THEN 1 ELSE 0 END) AS Updated,
SUM(CASE WHEN assess > DATEADD(yy,-2,GetDate()) AND reassess < DATEADD(mm,3,GetDate()) THEN 1 ELSE 0 END) AS DueReview,
SUM(CASE WHEN assess < DATEADD(yy,-2,GetDate()) AND reassess < GetDate() THEN 1 ELSE 0 END) AS Overdue
FROM newactrisk
GROUP BY dept) activityRiskByDept
) s1
join 
(
SELECT dept, aComplete, aInProgress, aOverdue, aTotal FROM
(SELECT dept, SUM(Complete) AS aComplete, SUM(InProgress) AS aInProgress, SUM(Overdue) AS aOverdue, SUM(Total) AS aTotal
FROM
(SELECT dept,
(SELECT COUNT(*) AS complete FROM action WHERE(status = 'Completed') AND (parentid = newactrisk.idnewactrisk) AND (tablename = 'newactrisk')) AS Complete,
(SELECT COUNT(*) AS inprogress FROM action WHERE (status <> 'Completed') AND (dueon > GetDate()) AND (parentid = newactrisk.idnewactrisk) AND (tablename = 'newactrisk')) AS InProgress,
(SELECT COUNT(*) AS overdue FROM action WHERE(status <> 'Completed') AND (dueon < GetDate()) AND (parentid = newactrisk.idnewactrisk) AND (tablename = 'newactrisk')) AS Overdue,
(SELECT COUNT(*) AS total FROM action WHERE (parentid = newactrisk.idnewactrisk) AND (tablename = 'newactrisk')) AS Total
FROM newactrisk) actionSummary
GROUP BY dept) actionSummaryByDept
) s2 on s1.dept.s2.dept

elsasoft.org

foxprorawks
Starting Member

17 Posts

Posted - 2009-08-24 : 10:22:37

Thanks for the quick reply.

Had to make a couple of minor changes to the first line:

select tab1.dept, TotalRAs, NewRAs, Updated, DueReview, Overdue, aComplete, aInProgress, aOverdue, aTotal from

but that works great.

Thanks again.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-24 : 10:23:46

quote:
Originally posted by foxprorawks

Thanks for the quick reply.

Had to make a couple of minor changes to the first line:

select tab1.dept, TotalRAs, NewRAs, Updated, DueReview, Overdue, aComplete, aInProgress, aOverdue, aTotal from

but that works great.

Thanks again.

Ya i for get while edit. Welcome

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources