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.Store) AS [Stores Scheduled], RollOut.RolledRelease, RollOut.RolledDate, RollOut.[Group], Release.ChangeRequestNum, Release.[File Size], Release.Owner, Release.AutoNumber FROM Release INNER JOIN RollOut ON Release.Release = RollOut.RolledRelease WHERE (RollOut.RolledDate 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.RolledRelease, RollOut.RolledDate, RollOut.[Group], Release.ChangeRequestNum, Release.[File Size], Release.Owner, Release.AutoNumber ORDER BY RollOut.RolledDate
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.