Hi all, I have this code, I want to make reference to some of the created columns to calculate another column.
I want to use the "Cycle" and the "Idle" to display in a separate column where W.[Index] = 'A'.
I have tried using:
CASE WHEN a.[Index] = 'A' THEN Cycle ELSE NULL END AS [CycleTime (s)],
CASE WHEN a.[Index] = 'A' THEN Idle ELSE NULL END AS [IdleTime (s)],
But keeps throwing error, I guess i need to create a temp table which is where am challenged. please assist
temp As
(SELECT identityFaceDMC,
DATEDIFF(SECOND, MIN(UNITMeanTimeDiam), MAX(UNITMeanTimeDiam)) AS delay_UNIT,
DATEDIFF(SECOND, MIN(WrappingTimeDiam), MAX(WrappingTimeDiam)) AS delay_wrap,
DATEDIFF(SECOND, MIN(PushTimeDiam), MAX(PushTimeDiam)) AS delay_Push
FROM orig AS orig_1 GROUP BY identityFaceDMC)
SELECT TOP (100) a.UNITFaceDMC, a.identityFaceDMC, a.VariantID, a.[Index],
CASE WHEN a.identityFaceDMC IN (SELECT identityFaceDMC FROM orig GROUP BY identityFaceDMC HAVING COUNT(*) > 1) THEN 'Double' ELSE 'Single' END AS UNIT_Identifier,
CASE WHEN a.[Index] = 'A' THEN delay_UNIT ELSE NULL END AS [Delay Btw UNIT_MM A&B (s)],
CASE WHEN a.[Index] = 'A' THEN delay_wrap ELSE NULL END AS [Delay Btw UNIT_Wrap 1&2 (s)],
CASE WHEN a.[Index] = 'A' THEN delay_Push ELSE NULL END AS [Delay Btw UNIT_Push A&B (s)],
a.UNITMeanTimeDiam,
CASE WHEN UNITMeanTimeDiam < Time THEN 'True' ELSE 'False' END AS UNITMM_TimeCreated_Invstgtn,
a.Time,
a.WrappingTimeDiam, a.PushTimeDiam, a.DateTimeCompleted,
DATEDIFF(SECOND, a.Time, a.DateTimeCompleted) AS OperationTime,
a.DB_CycleTime,
DATEDIFF(SECOND, LAG(a.Time, 1) OVER (order by a.Time ), a.Time) AS Cycle,
CASE WHEN DATEDIFF(SECOND, LAG(a.DateTimeCompleted, 2) OVER (order by a.DateTimeCompleted ), a.Time) < 0 then 0 else DATEDIFF(SECOND, LAG(a.DateTimeCompleted, 2) OVER (order by a.DateTimeCompleted ), a.Time ) end AS Idle,
a.GUMMeanTimeDiam, a.UNITMeanDiam,a.UNITMeanIsPass, a.BeadWeight, a.GUMMeanGap, a.GUMMeanDiam, a.GUMMeanGUM, a.GUMMeanIsPass, a.PushSpringback, a.PushDisplacement, a.PushForce,
a.ShiftNumber, a.FinalIsPass
FROM orig AS a INNER JOIN
temp AS b ON a.identityFaceDMC = b.identityFaceDMC
ORDER BY a.identityFaceDMC