Combining 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 2000 Forums

SQL Server Development (2000)

Combining Queries

Author

Topic

dlwilson007
Starting Member

5 Posts

Posted - 2006-11-16 : 16:14:53

Hello,

I'm using SQL 2005 and very new to developing. I have two queries that produce the data I want, and I want to combine the queries. Query 1 provides the number of milestones schedule and completed, and groups them by managers. Query 2 provides the number of times a date was changed on a milestone (history is stored in the milestone list). Based on the grouping, I get the managers name repeated for each date change (which I can sum in a report). However, what I would like to do is to combine the queries. Ideally, in query 2, I would SUM the count and group by manager. But, I have found that I cannot SUM an aggregate. What are my other options?

The queries are as follows:

Query 1:
SELECT I.tp_title,
COUNT(DISTINCT S.nvarchar3) Scheduled,
COUNT(DISTINCT M.nvarchar3) Completed
FROM UserData M JOIN
UserData S ON (S.int6 = M.int6) LEFT JOIN
UserInfo I ON (I.tp_ID = M.int6)
WHERE (M.tp_ListId = 'D2F8226D-8981-4206-9654-7EB997707F7F') AND (M.tp_IsCurrent = 1) AND (M.nvarchar1 = '(4) Completed') AND (S.tp_ListId = 'D2F8226D-8981-4206-9654-7EB997707F7F')
GROUP BY I.tp_title

Query 2:
SELECT I.tp_title,
COUNT(DISTINCT DATEADD("d",0,DATEDIFF("d",M.datetime2,0)))-1 DateChanged
FROM UserData M JOIN
UserInfo I ON (I.tp_ID = M.int6)
WHERE (M.tp_ListId = 'D2F8226D-8981-4206-9654-7EB997707F7F')
GROUP BY nvarchar3, I.tp_title
HAVING COUNT(DISTINCT DATEADD("d",0,DATEDIFF("d",M.datetime2,0))) > 1

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 17:13:40

Try this - I haven't tested it because you didn't give data, but I think it's close

SELECT Milestones.tp_title, Scheduled, Completed, DateChanged
FROM
(SELECT I.tp_title,
COUNT(DISTINCT S.nvarchar3) Scheduled, 
COUNT(DISTINCT M.nvarchar3) Completed
FROM UserData M JOIN
UserData S ON (S.int6 = M.int6) LEFT JOIN
UserInfo I ON (I.tp_ID = M.int6) 
WHERE (M.tp_ListId = 'D2F8226D-8981-4206-9654-7EB997707F7F') 
   AND (M.tp_IsCurrent = 1) AND (M.nvarchar1 = '(4) Completed') 
   AND (S.tp_ListId = 'D2F8226D-8981-4206-9654-7EB997707F7F')
GROUP BY I.tp_title) AS Milestones
INNER JOIN
(SELECT I.tp_title, SUM(DateChanged) AS DateChanged
FROM
(SELECT I.tp_title,
COUNT(DISTINCT DATEADD("d",0,DATEDIFF("d",M.datetime2,0)))-1 DateChanged
FROM UserData M JOIN
UserInfo I ON (I.tp_ID = M.int6)
WHERE (M.tp_ListId = 'D2F8226D-8981-4206-9654-7EB997707F7F')
GROUP BY nvarchar3, I.tp_title
HAVING COUNT(DISTINCT DATEADD("d",0,DATEDIFF("d",M.datetime2,0))) > 1) AS Changed) AS ChangedSum
ON Milestones.tp_title = ChangedSum.tp_title

dlwilson007
Starting Member

5 Posts

Posted - 2006-11-17 : 10:41:09

quote:
Originally posted by snSQL

Try this - I haven't tested it because you didn't give data, but I think it's close

Yes, that is very close to what I needed. I was able to tweak it a little to get the results I needed. I really appreciate this help! Now I have a better understanding of how I can imbed queries. Being a novice developer, this seems somewhat complex, but I suspect to a seasoned developer it is not.

Thanks again for your help!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-17 : 11:25:48

quote:
Now I have a better understanding of how I can imbed queries

Just for closure - those queries are called subqueries.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources