[pg300]

So I have two queries. The first one is this:

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:

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:

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!

[r937]

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

[pg300]

Wow thanks so much 937!! I LOVE your choice of words :P what is that as parameter called in sql?

Thanks again!

[r937]

Originally Posted by pg300

what is that as parameter called in sql?

it's called a column alias or a table alias

[pg300]

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?

[r937]

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

[pg300]

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!