473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

how to join these two queries?

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.
Attached Images
File Type: jpg Capture.jpg (26.9 KB, 117 views)
Dec 8 '10 #1
1 1529
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
Dec 9 '10 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
4
by: Shahzad | last post by:
dear respected gurus, I would like to knew how to apply append,insert query for a self table where no primary keys issues. i do have problem say there are 5 rows of single record, this is data...
2
by: rockyptc | last post by:
greetings. first, i apologize for asking an old question. it appears that i'm looking for a solution that was already given but it don't seem to fit my scenario. so thanks for putting up with me. ...
2
by: CSN | last post by:
Is there much difference between using subqueries and separating out them into separate queries? __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building...
14
by: cjakeman | last post by:
Hi, Solved a little mystery yesterday when I built a form that combined 2 tables with a 1:M relationship and relational integrity. All the correct data was visible on the form but, if I tried to...
17
by: bobo420 | last post by:
Hi! I have 4 tables (table1, table2, table3, table 4) I need to do select * from all four table and get them sorted all the tables have field named id, so I figured that's the field I should...
2
by: gubbachchi | last post by:
Hi, How to use foreign key with join queries. I have used the query SELECT first_name,last_name,user_email from Info,Registry WHERE user_id='1'; where user_id = foreign key...
1
by: Rich P | last post by:
Greetings, Left Join (outer join) queries are generally for excluding stuff. select t1.* from tblx t1 Left Join tbly t2 on t1.ID = t2.ID and t1.fld1 = t2.fld1 and t1.fld2 = t2.fld2... Where...
2
by: manmadhan | last post by:
hey im new for the database so plz anybody send info abt join queries
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.