Database

Mike654321 wrote:

55 is excluded because it has a link of 4 and 5...and 77 is excluded because it has a link of 4 in it.

SELECT  Table1.field2
FROM    Table1 join Table2
    ON  Table1.field1 = Table2.field1
WHERE   Table2.field2 = 9
AND Table1.field2 NOT IN 
    (
    SELECT  DISTINCT Table1.field2
    FROM    Table1 join Table2
        ON  Table1.field1 = Table2.field1
    WHERE   Table2.field2 <> 9
    )
;

"Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int."

SELECT mt.TransDate, CASE ISNULL(h.Hardware,0)
                      WHEN 0 Then 0
                      ELSE h.Hardware
                      End As Hardware, 
                     CASE ISNULL(d.Datastrip, 0)
                      WHEN 0 Then 0
                      ELSE d.Datastrip
                      END AS Datastrip
  FROM MaterialTransaction mt
    LEFT JOIN (SELECT mtt.TransDate,SUM(mtt.Qty) As Hardware
            FROM MaterialTransaction mtt
              JOIN Production prr
                ON mtt.Prodn_ID = prr.Prodn_ID
              JOIN WorkOrder woo
                ON prr.WO_ID = woo.WO_ID
              JOIN Product pp
                ON woo.Product_ID = pp.ID
              JOIN ProductCategory pcc
                ON pp.ProdCat_ID = pcc.ID
            WHERE pcc.Name In ('Hardware','Custom', 'Cellular')    
            GROUP BY mtt.TransDate) h
      ON mt.TransDate = h.TransDate
    LEFT JOIN (SELECT mtt.TransDate, SUM(mtt.Qty) As Datastrip
            FROM MaterialTransaction mtt
              JOIN Production prr
                ON mtt.Prodn_ID = prr.Prodn_ID
              JOIN WorkOrder woo
                ON prr.WO_ID = woo.WO_ID
              JOIN Product pp
                ON woo.Product_ID = pp.ID
              JOIN ProductCategory pcc
                ON pp.ProdCat_ID = pcc.ID
            WHERE pcc.Name In ('DS PVC','DS PETG')    
            GROUP BY mtt.TransDate) d
          ON h.TransDate = d.TransDate
    WHERE mt.TransDate BETWEEN '11/24/2009' And '11/25/2009'
    GROUP BY mt.TransDate, h.Hardware, d.Datastrip  

Luc Pattyn wrote:

AFAIK joins don't need explicit "ON" clauses if the corresponding fields carry the same names

Andy_L_J wrote:

Can this be optimized further?

SELECT mt.TransDate,
         SUM(CASE WHEN pc.Name IN('Hadware','Custom')
              THEN mt.Qty
              ELSE 0 END) AS 'Hardware',
         SUM(CASE WHEN pc.Name LIKE 'DS %'
              THEN mt.Qty
              ELSE 0 End) As 'DataStrip'
  From MaterialTransaction mt
    JOIN Production pr
      ON mt.Prodn_ID = pr.Prodn_ID
    JOIN WorkOrder wo
      ON pr.WO_ID = wo.WO_ID
    JOIN Product p
      ON wo.Product_ID = p.ID
    JOIN ProductCategory pc
      ON p.ProdCat_ID = pc.ID
    WHERE Date Between '11/24/2009' And '11/25/2009'
  GROUP BY mt.TransDate