October 16th, 2011, 12:32 PM
-
Select from two quereis
So I have two queries. The first one is this:
Code:
SELECT SUM(qq.ScrapPerContainer) as FinalSum FROM (
SELECT q.container, MAX(q.qty) AS ScrapPerContainer FROM (
SELECT t.containername, s.yieldcode, s.qty, s.container FROM (
SELECT containername FROM eqdb_insite.dbo.dc_mz00300r
WHERE txndate>='10/2/2011 12:00 AM' AND txndate<'10/9/2011 12:00 AM'
) as t
LEFT JOIN (
SELECT container, yieldcode, w.waferid, qty FROM eqdb_insite.dbo.dailyop_pareto
LEFT JOIN eqdb_insite.dbo.waferid w ON container = w.wafergroupid
WHERE operation IN ('MZ-00300', 'MZ-00302', 'MZ-00310', 'MZ-00320')
) AS s ON t.containername = s.container OR t.containername = s.waferid
) as q
GROUP BY q.container
) AS qq
and the second one is this:
Code:
SELECT COUNT(qqq.containername) as FinalCount FROM (
SELECT containername FROM eqdb_insite.dbo.dc_mz00300r WHERE txndate>='10/2/2011 12:00 AM' AND txndate<'10/9/2011 12:00 AM') as qqq
What I am trying to do now is get both of those select values in one query so my final goal will be:
Code:
SELECT blah.FinalCount - blah.FinalSum FROM ( ............ )
In the ....... i need to place both of those queries in such a way that i get back FinalCount and FinalSum of the independent queries.
Please help. 
Thanks!
October 17th, 2011, 06:44 AM
-
Code:
SELECT humpty.FinalSum
, dumpty.FinalCount
FROM ( SELECT SUM(qq.ScrapPerContainer) as FinalSum
FROM ( SELECT q.container
, MAX(q.qty) AS ScrapPerContainer
FROM ( SELECT t.containername
, s.yieldcode
, s.qty
, s.container
FROM ( SELECT containername
FROM eqdb_insite.dbo.dc_mz00300r
WHERE txndate>= '10/2/2011 12:00 AM'
AND txndate < '10/9/2011 12:00 AM' ) as t
LEFT OUTER
JOIN ( SELECT container
, yieldcode
, w.waferid
, qty
FROM eqdb_insite.dbo.dailyop_pareto
LEFT OUTER
JOIN eqdb_insite.dbo.waferid w
ON container = w.wafergroupid
WHERE operation IN ('MZ-00300', 'MZ-00302'
, 'MZ-00310', 'MZ-00320') ) AS s
ON t.containername = s.container
OR t.containername = s.waferid ) as q
GROUP
BY q.container ) AS qq
) AS humpty
CROSS
JOIN ( SELECT COUNT(qqq.containername) as FinalCount
FROM ( SELECT containername
FROM eqdb_insite.dbo.dc_mz00300r
WHERE txndate >= '10/2/2011 12:00 AM'
AND txndate < '10/9/2011 12:00 AM') as qqq
) AS dumpty
October 17th, 2011, 10:39 AM
-
Wow thanks so much 937!! I LOVE your choice of words :P what is that as parameter called in sql?
Thanks again!
October 17th, 2011, 12:28 PM
-
Originally Posted by pg300
what is that as parameter called in sql?
it's called a column alias or a table alias
October 17th, 2011, 02:01 PM
-
Thanks again!
I didn't get to test this but I was just wondering will this work in sql server?
The cross join function is not documented here:
http://www.w3schools.com/sql/sql_join_left.asp
Is there a place I can sql server documentation?
October 17th, 2011, 02:19 PM
-
Originally Posted by pg300
I was just wondering will this work in sql server?
yes
Originally Posted by pg300
Is there a place I can sql server documentation?
yes... google SQL Server Books Online
that will lead you to da manual
October 17th, 2011, 09:25 PM
-
ah i thought there were different versions of sql server. i am very new to all of this (just 2 weeks old) thanks so much again!