SQL Syntax

[NotSQL]

Hi guys,

I was just wondering if there was a better way of writing this bit of code.. It not the fastest


Select t1.PWO,t1.OrderLine, t1.Item,t1.OrderQty, t1.TotalComponentCost, t2.ActLabourHrs, t2.LABOH
From
(Select PWO,OrderLine,Item, OrderQty, TotalComponentCost
from table1
Where FirstOperation = '0000')t1
Inner Join
(SELECT PWO, OrderLine, SUM(ISNULL(ActLabourHrs, 0)) AS ActLabourHrs,
SUM(ISNULL(ActLabourHrs, 0) * (VariableOHPct / 100)) AS LABOH
FROM table2
GROUP BY PWO, OrderLine) t2
on t1.PWO = t2.PWO and t1.Orderline = t2.OrderLine

 

[bborissov]

Select t1.PWO, t1.OrderLine,
       t1.Item,
       t1.OrderQty,
       t1.TotalComponentCost,
       SUM(t2.ActLabourHrs) AS ActLabourHrs,
       SUM(t2.LABOH)        AS LABOH
From table1 t1
Inner Join (SELECT Pwo,
                   OrderLine,
                   SUM(ActLabourHrs) AS ActLabourHrs,
                   SUM(LABOH)        AS LABOH
            FROM table2
            GROUP BY Pwo, OrderLine) T2
       on t1.PWO = t2.PWO and t1.Orderline = t2.OrderLine
Where T1.FirstOperation = '0000'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

OOPS,

Select t1.PWO, t1.OrderLine,
       t1.Item,
       t1.OrderQty,
       t1.TotalComponentCost,
/* NOT this
       SUM(t2.ActLabourHrs) AS ActLabourHrs,
       SUM(t2.LABOH)        AS LABOH
*/
-- But this
       t2.ActLabourHrs AS ActLabourHrs,
       t2.LABOH        AS LABOH

From table1 t1
Inner Join (SELECT Pwo,
                   OrderLine,
                   SUM(ActLabourHrs) AS ActLabourHrs,
                   SUM(LABOH)        AS LABOH
            FROM table2
            GROUP BY Pwo, OrderLine) T2
       on t1.PWO = t2.PWO and t1.Orderline = t2.OrderLine
Where T1.FirstOperation = '0000'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.