how to join these two queries?

[Jeremy Haggard]

Hi all,

I have a query issue I can not figure out.

I have a internal website that we use to display "release schedules" for software updates. On one of the pages I have a weekly overview to show the releases for the past week, current week and next week. Below is the query I use for "current week:"

SELECT COUNT(RollOut.S tore) AS [Stores Scheduled], RollOut.RolledR elease, RollOut.RolledD ate, RollOut.[Group], Release.ChangeR equestNum, Release.[File Size], Release.Owner, Release.AutoNum ber FROM Release INNER JOIN RollOut ON Release.Release = RollOut.RolledR elease WHERE (RollOut.Rolled Date BETWEEN DATEADD(d, - DATEPART(dw, GETDATE()) + 1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND DATEADD(d, - DATEPART(dw, GETDATE()) + 1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) + 6)) GROUP BY RollOut.RolledR elease, RollOut.RolledD ate, RollOut.[Group], Release.ChangeR equestNum, Release.[File Size], Release.Owner, Release.AutoNum ber ORDER BY RollOut.RolledD ate

What I would like to do is add a column to the query that tells me how many stores as of the date in the results above have the release. I attached a screenshot to this post as well so you can see what the current query returns... The problem is, I can't figure out how to do the second count and join. The query above is only returning the records for a one week period and I need the count to be like (select count(Store) from rollout where rolledrelease = ??release name above?? and rolleddate < GetDate().

Any guidance would be much appreciated. I'm a beginner to queries and have been working on this for over a week now and no amount of google searching appears to be helping me.

I'm running my database from a sql server 2005 machine.

[David Gluth]

Your best bet is to use a derived table. Enter this query into your SQL pane of query manager. The in the diagram pane right click and select add new derived table. It will add
CROSS JOIN
(SELECT
FROM ) AS derivedtbl_1
Where it belongs you then replace the SELECT FROM with you query to count stores. when you add the query the diagram pane will add the columsn to the derivedtbl - you can then ling the two tables and select the column(s) from the derived tabel that you want included in your query