Hi,
I have the following query but it keeps coming up with invalid column name
( the ones that are defined as 'AS' columnname when not a SUM) - I assume it
is something to do with the group by clause - what am I doing wrong?
SELECT DISTINCT
TX.orderno AS [JobNo],
CO.CustomerID AS Customer,
TX.mfgresid AS Machine,
TX.opno AS [Op],
TX.workcentre,
SUM(TX.qty) AS [Qty],
impJOB.IExpUnitRunTim AS [CycleTime],
SUM((TX.qty*impJOB.IExpUnitRunTim)/3600) AS [StdProdHrs],
case when tblOverrideCycleTime.ORCycleTime is null
then impJOB.IExpUnitRunTim
else tblOverrideCycleTime.ORCycleTime end as [Override],
case when tblOverrideCycleTime.ORCycleTime is null then
SUM(WC.[Effeciency]*TX.[qty]*[impJOB].IExpUnitRunTim/3600) else
SUM(tblOverrideCycleTime.ORCycleTime * WC.Effeciency * TX.qty) end AS
[BonusHours],
case when patindex('%[^0-9]%',TX.[userid]) = 0 then
right('000' + TX.[userid],3) else
TX.userid end as Operator
FROM MIS.dbo.artxnlog TX INNER JOIN MIS.dbo.CellOrder CO ON TX.fopno =
CO.FOpNo AND
TX.orderno = CO.OrderNo AND TX.batchid = CO.BatchId INNER JOIN
APS_A.dbo.WorkCentre WC ON TX.workcentre = WC.WorkCentre INNER
JOIN Sys.dbo.WipJobAllLab impJOB ON TX.orderno = impJOB.Job collate
Latin1_General_CI_AS AND
cast(TX.opno as varchar) = cast(impJOB.Operation as varchar) collate
Latin1_General_CI_AS
LEFT JOIN tblOverrideCycleTime ON
TX.mfgresid = tblOverrideCycleTime.Resid collate Latin1_General_CI_AS AND
cast(TX.opno as varchar) =
cast(tblOverrideCycleTime.OpNo as varchar) collate Latin1_General_CI_AS AND
TX.orderno = tblOverrideCycleTime.OrderNo collate Latin1_General_CI_AS
WHERE TX.txntype = 'CCOPDONEP' and TX.qty<>0 and TX.lastupdated >= @DateFrom
and TX.lastupdated <= @DateTo
GROUP BY TX.orderno, CO.CustomerID, TX.mfgresid, TX.opno, TX.workcentre,
CycleTime, Override,Operator
ORDER BY Operator, TX.orderno, TX.opno, TX.PassportNo
GO
