May 7, 2009 at 7:06 am
I find the intellisense helpful in SS2008. But then, I haven't coded in T-SQL forever. I've gotten used to the intellisense in Visual Studio. Maybe that's helped me use it in SS2008.
The one thing that's hard for me to understand in the aliasing. Why is aliasing a table name easier for another coder to understand? It saves time typing but isn't a fully qualified name easier to read and understand?
May 7, 2009 at 7:14 am
skjoldtc (5/7/2009)
I find the intellisense helpful in SS2008. But then, I haven't coded in T-SQL forever. I've gotten used to the intellisense in Visual Studio. Maybe that's helped me use it in SS2008.The one thing that's hard for me to understand in the aliasing. Why is aliasing a table name easier for another coder to understand? It saves time typing but isn't a fully qualified name easier to read and understand?
Lets analyse a simple query...
SELECT insite.alzGradeGroup.alzGradeGroupName AS Groep,
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumber,
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumberDisplay,
insite.T_fromGMPSFA_Step_Feedback_Arch.OrderNumberDisplay AS Opdrachtnr,
insite.T_fromGMPSFA_Step_Feedback_Arch.GradeCode AS Type,
insite.T_fromGMPSFA_Step_Feedback_Arch.SlabThickness AS Gauge,
insite.T_fromGMPSFA_Step_Feedback_Arch.WidthTwin1 AS Width,
insite.T_fromGMPSFA_Step_Feedback_Arch.tsInserted AS tsInserted,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
ELSE NULL END) AS OVEN_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
ELSE NULL END) AS OVEN_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 2 ELSE 1 END)
WHEN 'OVEN2' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 1 ELSE 2 END) ELSE NULL END) AS OVEN,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
ELSE NULL END) AS Oven_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate)
WHEN 'OVEN2' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS Oven_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS OVEN_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait / 60
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 ELSE NULL END) AS OVEN_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap / 60
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS OVEN_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
ELSE NULL END) AS AOD1_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
ELSE NULL END) AS AOD1_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
ELSE NULL END) AS AOD1_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS AOD1_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS AOD1_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 ELSE NULL END) AS AOD1_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS AOD1_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
WHEN 'VOD2' THEN BackGroundColor ELSE NULL END)
AS VOD_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
WHEN 'VOD2' THEN FontStyle ELSE NULL END)
AS VOD_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 2 ELSE 1 END)
WHEN 'VOD2' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 1 ELSE 2 END) ELSE NULL END) AS VOD,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
WHEN 'VOD2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
ELSE NULL END) AS VOD_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate)
WHEN 'VOD2' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS VOD_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
WHEN 'VOD2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS VOD_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 WHEN 'VOD2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 ELSE NULL END) AS VOD_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 WHEN 'VOD2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS VOD_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
WHEN 'AFW2' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
WHEN 'AFW3' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
ELSE NULL END) AS AFW_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
WHEN 'AFW2' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
WHEN 'AFW3' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
ELSE NULL END) AS AFW_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 2 ELSE 1 END)
WHEN 'AFW3' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 1 ELSE 3 END)
WHEN 'AFW2' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 1 ELSE 2 END) ELSE NULL END) AS AFW,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
WHEN 'AFW2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
WHEN 'AFW3' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
ELSE NULL END) AS AFW_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate)
WHEN 'AFW3' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate)
WHEN 'AFW2' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS AFW_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
WHEN 'AFW2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
WHEN 'AFW3' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS AFW_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait/60
WHEN 'AFW3' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait/60
WHEN 'AFW2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 ELSE NULL END) AS AFW_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap / 60
WHEN 'AFW3' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap / 60
WHEN 'AFW2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS AFW_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
ELSE NULL END) AS KG1_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
ELSE NULL END) AS KG1_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndSetupDate
ELSE NULL END) AS KG1_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndSetupDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS KG1_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS KG1_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait /
60 ELSE NULL END) AS KG1_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS KG1_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN (CASE Upper(insite.T_fromGMPSFA_Step_Feedback_Arch.SetupTypeCC)
WHEN 'BREEDTE' THEN '40 BR' WHEN 'DIKTE' THEN '75 DI' WHEN
'TYPE' THEN '40 TY' WHEN 'TUSSENPAN' THEN '10 TP' WHEN
'TYPE/TUSSENPAN' THEN '10 TY+TP' WHEN 'PRODUCTGROEP'
THEN '75 DI+BR' ELSE insite.T_fromGMPSFA_Step_Feedback_Arch.SetupTypeCC
END) ELSE NULL END) AS KG1_Setup
FROM insite.T_fromGMPSFA_BackGroundColor INNER JOIN
insite.T_fromGMPSFA_Step_Feedback_Arch ON
insite.T_fromGMPSFA_BackGroundColor.Finished = insite.T_fromGMPSFA_Step_Feedback_Arch.Finished
INNER JOIN
insite.container ON
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumber = insite.container.containername
INNER JOIN
insite.mfgorder ON
insite.mfgorder.mfgorderid = insite.container.mfgorderid INNER JOIN
insite. Product ON
insite.mfgorder.inoxintnormbaseid = insite. product .productbaseid
INNER JOIN
insite.alzGradeGroup ON
insite.Product.GradeGroupID = insite.alzGradeGroup.alzGradeGroupID
GROUP BY insite.alzGradeGroup.alzGradeGroupName,
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumber,
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumberDisplay,
insite.T_fromGMPSFA_Step_Feedback_Arch.OrderNumberDisplay,
insite.T_fromGMPSFA_Step_Feedback_Arch.GradeCode,
insite.T_fromGMPSFA_Step_Feedback_Arch.slabthickness,
insite.T_fromGMPSFA_Step_Feedback_Arch.WidthTwin1,
insite.T_fromGMPSFA_Step_Feedback_Arch.tsInserted
I prefer to analyse this version:
SELECT GG.alzGradeGroupName AS Groep
, SFA.HeatNumber
, SFA.HeatNumberDisplay
, SFA.OrderNumberDisplay AS Opdrachtnr
, SFA.GradeCode AS Type
, SFA.SlabThickness AS Gauge
, SFA.WidthTwin1 AS Width
, SFA.tsInserted AS tsInserted
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN BC.BackGroundColor
WHEN 'OVEN2' THEN BC.BackGroundColor
ELSE NULL
END) AS OVEN_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN BC.FontStyle
WHEN 'OVEN2' THEN BC.FontStyle
ELSE NULL
END) AS OVEN_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 2
ELSE 1
END )
WHEN 'OVEN2' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 1
ELSE 2
END )
ELSE NULL
END) AS OVEN
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN SFA.StartDate
WHEN 'OVEN2' THEN SFA.StartDate
ELSE NULL
END) AS Oven_Start
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
WHEN 'OVEN2' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
ELSE NULL
END) AS Oven_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN SFA.EndDate
WHEN 'OVEN2' THEN SFA.EndDate
ELSE NULL
END) AS OVEN_End
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN SFA.Wait / 60
WHEN 'OVEN2' THEN SFA.Wait / 60
ELSE NULL
END) AS OVEN_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN SFA.Overlap / 60
WHEN 'OVEN2' THEN SFA.Overlap / 60
ELSE NULL
END) AS OVEN_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN BC.BackGroundColor
ELSE NULL
END) AS AOD1_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN BC.FontStyle
ELSE NULL
END) AS AOD1_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN SFA.StartDate
ELSE NULL
END) AS AOD1_Start
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
ELSE NULL
END) AS AOD1_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN SFA.EndDate
ELSE NULL
END) AS AOD1_End
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN SFA.Wait / 60
ELSE NULL
END) AS AOD1_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN SFA.Overlap / 60
ELSE NULL
END) AS AOD1_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN BC.BackGroundColor
WHEN 'VOD2' THEN BackGroundColor
ELSE NULL
END) AS VOD_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN BC.FontStyle
WHEN 'VOD2' THEN FontStyle
ELSE NULL
END) AS VOD_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 2
ELSE 1
END )
WHEN 'VOD2' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 1
ELSE 2
END )
ELSE NULL
END) AS VOD
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN SFA.StartDate
WHEN 'VOD2' THEN SFA.StartDate
ELSE NULL
END) AS VOD_Start
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
WHEN 'VOD2' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
ELSE NULL
END) AS VOD_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN SFA.EndDate
WHEN 'VOD2' THEN SFA.EndDate
ELSE NULL
END) AS VOD_End
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN SFA.Wait / 60
WHEN 'VOD2' THEN SFA.Wait / 60
ELSE NULL
END) AS VOD_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN SFA.Overlap / 60
WHEN 'VOD2' THEN SFA.Overlap / 60
ELSE NULL
END) AS VOD_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN BC.BackGroundColor
WHEN 'AFW2' THEN BC.BackGroundColor
WHEN 'AFW3' THEN BC.BackGroundColor
ELSE NULL
END) AS AFW_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN BC.FontStyle
WHEN 'AFW2' THEN BC.FontStyle
WHEN 'AFW3' THEN BC.FontStyle
ELSE NULL
END) AS AFW_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 2
ELSE 1
END )
WHEN 'AFW3' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 1
ELSE 3
END )
WHEN 'AFW2' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 1
ELSE 2
END )
ELSE NULL
END) AS AFW
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN SFA.StartDate
WHEN 'AFW2' THEN SFA.StartDate
WHEN 'AFW3' THEN SFA.StartDate
ELSE NULL
END) AS AFW_Start
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
WHEN 'AFW3' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
WHEN 'AFW2' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
ELSE NULL
END) AS AFW_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN SFA.EndDate
WHEN 'AFW2' THEN SFA.EndDate
WHEN 'AFW3' THEN SFA.EndDate
ELSE NULL
END) AS AFW_End
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN SFA.Wait / 60
WHEN 'AFW3' THEN SFA.Wait / 60
WHEN 'AFW2' THEN SFA.Wait / 60
ELSE NULL
END) AS AFW_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN SFA.Overlap / 60
WHEN 'AFW3' THEN SFA.Overlap / 60
WHEN 'AFW2' THEN SFA.Overlap / 60
ELSE NULL
END) AS AFW_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN BC.BackGroundColor
ELSE NULL
END) AS KG1_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN BC.FontStyle
ELSE NULL
END) AS KG1_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN SFA.EndSetupDate
ELSE NULL
END) AS KG1_Start
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN DATEDIFF(mi, SFA.EndSetupDate, SFA.EndDate)
ELSE NULL
END) AS KG1_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN SFA.EndDate
ELSE NULL
END) AS KG1_End
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN SFA.Wait / 60
ELSE NULL
END) AS KG1_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN SFA.Overlap / 60
ELSE NULL
END) AS KG1_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN ( CASE Upper(SFA.SetupTypeCC)
WHEN 'BREEDTE' THEN '40 BR'
WHEN 'DIKTE' THEN '75 DI'
WHEN 'TYPE' THEN '40 TY'
WHEN 'TUSSENPAN' THEN '10 TP'
WHEN 'TYPE/TUSSENPAN' THEN '10 TY+TP'
WHEN 'PRODUCTGROEP' THEN '75 DI+BR'
ELSE SFA.SetupTypeCC
END )
ELSE NULL
END) AS KG1_Setup
FROM insite.T_fromGMPSFA_BackGroundColor BC
INNER JOIN insite.T_fromGMPSFA_Step_Feedback_Arch SFA
ON BC.Finished = SFA.Finished
INNER JOIN insite.container C
ON SFA.HeatNumber = C.containername
INNER JOIN insite.mfgorder O
ON O.mfgorderid = C.mfgorderid
INNER JOIN insite.Product P
ON O.inoxintnormbaseid = P.productbaseid
INNER JOIN insite.alzGradeGroup GG
ON P.GradeGroupID = GG.alzGradeGroupID
GROUP BY GG.alzGradeGroupName
, SFA.HeatNumber
, SFA.HeatNumberDisplay
, SFA.OrderNumberDisplay
, SFA.GradeCode
, SFA.slabthickness
, SFA.WidthTwin1
, SFA.tsInserted
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 7, 2009 at 7:22 am
There are far more important things to make code readable than naming conventions on table aliasses.
Ive been in the SQL Server industry for many years, and to be honest using full table names as aliases is not just a few extra key strokes, its a royal pain. If you have 2 tables called People and Addresses how can it be said that using aliases of P and a are difficult to read? It cant infact it is more readable than referring to people and addresses everywhere because it avoids unnecessary clutter.
Imho the biggest no no in aliassing is wheat i see often is no aliassing at all. then you spend ages working out which column comes from which table in a complex query.
But to go back to what i said at the start. There are much more significant things than aliassing for readability. Indentation, white space and comments, to name a few.
May 7, 2009 at 7:22 am
ALZDBA (5/7/2009)
skjoldtc (5/7/2009)
I find the intellisense helpful in SS2008. But then, I haven't coded in T-SQL forever. I've gotten used to the intellisense in Visual Studio. Maybe that's helped me use it in SS2008.The one thing that's hard for me to understand in the aliasing. Why is aliasing a table name easier for another coder to understand? It saves time typing but isn't a fully qualified name easier to read and understand?
Lets analyse a simple query...
SELECT insite.alzGradeGroup.alzGradeGroupName AS Groep,
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumber,
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumberDisplay,
insite.T_fromGMPSFA_Step_Feedback_Arch.OrderNumberDisplay AS Opdrachtnr,
insite.T_fromGMPSFA_Step_Feedback_Arch.GradeCode AS Type,
insite.T_fromGMPSFA_Step_Feedback_Arch.SlabThickness AS Gauge,
insite.T_fromGMPSFA_Step_Feedback_Arch.WidthTwin1 AS Width,
insite.T_fromGMPSFA_Step_Feedback_Arch.tsInserted AS tsInserted,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
ELSE NULL END) AS OVEN_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
ELSE NULL END) AS OVEN_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 2 ELSE 1 END)
WHEN 'OVEN2' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 1 ELSE 2 END) ELSE NULL END) AS OVEN,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
ELSE NULL END) AS Oven_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate)
WHEN 'OVEN2' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS Oven_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS OVEN_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait / 60
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 ELSE NULL END) AS OVEN_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap / 60
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS OVEN_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
ELSE NULL END) AS AOD1_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
ELSE NULL END) AS AOD1_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
ELSE NULL END) AS AOD1_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS AOD1_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS AOD1_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 ELSE NULL END) AS AOD1_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS AOD1_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
WHEN 'VOD2' THEN BackGroundColor ELSE NULL END)
AS VOD_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
WHEN 'VOD2' THEN FontStyle ELSE NULL END)
AS VOD_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 2 ELSE 1 END)
WHEN 'VOD2' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 1 ELSE 2 END) ELSE NULL END) AS VOD,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
WHEN 'VOD2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
ELSE NULL END) AS VOD_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate)
WHEN 'VOD2' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS VOD_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
WHEN 'VOD2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS VOD_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 WHEN 'VOD2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 ELSE NULL END) AS VOD_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 WHEN 'VOD2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS VOD_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
WHEN 'AFW2' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
WHEN 'AFW3' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
ELSE NULL END) AS AFW_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
WHEN 'AFW2' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
WHEN 'AFW3' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
ELSE NULL END) AS AFW_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 2 ELSE 1 END)
WHEN 'AFW3' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 1 ELSE 3 END)
WHEN 'AFW2' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 1 ELSE 2 END) ELSE NULL END) AS AFW,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
WHEN 'AFW2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
WHEN 'AFW3' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
ELSE NULL END) AS AFW_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate)
WHEN 'AFW3' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate)
WHEN 'AFW2' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS AFW_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
WHEN 'AFW2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
WHEN 'AFW3' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS AFW_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait/60
WHEN 'AFW3' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait/60
WHEN 'AFW2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 ELSE NULL END) AS AFW_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap / 60
WHEN 'AFW3' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap / 60
WHEN 'AFW2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS AFW_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
ELSE NULL END) AS KG1_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
ELSE NULL END) AS KG1_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndSetupDate
ELSE NULL END) AS KG1_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndSetupDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS KG1_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS KG1_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait /
60 ELSE NULL END) AS KG1_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS KG1_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN (CASE Upper(insite.T_fromGMPSFA_Step_Feedback_Arch.SetupTypeCC)
WHEN 'BREEDTE' THEN '40 BR' WHEN 'DIKTE' THEN '75 DI' WHEN
'TYPE' THEN '40 TY' WHEN 'TUSSENPAN' THEN '10 TP' WHEN
'TYPE/TUSSENPAN' THEN '10 TY+TP' WHEN 'PRODUCTGROEP'
THEN '75 DI+BR' ELSE insite.T_fromGMPSFA_Step_Feedback_Arch.SetupTypeCC
END) ELSE NULL END) AS KG1_Setup
FROM insite.T_fromGMPSFA_BackGroundColor INNER JOIN
insite.T_fromGMPSFA_Step_Feedback_Arch ON
insite.T_fromGMPSFA_BackGroundColor.Finished = insite.T_fromGMPSFA_Step_Feedback_Arch.Finished
INNER JOIN
insite.container ON
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumber = insite.container.containername
INNER JOIN
insite.mfgorder ON
insite.mfgorder.mfgorderid = insite.container.mfgorderid INNER JOIN
insite. Product ON
insite.mfgorder.inoxintnormbaseid = insite. product .productbaseid
INNER JOIN
insite.alzGradeGroup ON
insite.Product.GradeGroupID = insite.alzGradeGroup.alzGradeGroupID
GROUP BY insite.alzGradeGroup.alzGradeGroupName,
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumber,
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumberDisplay,
insite.T_fromGMPSFA_Step_Feedback_Arch.OrderNumberDisplay,
insite.T_fromGMPSFA_Step_Feedback_Arch.GradeCode,
insite.T_fromGMPSFA_Step_Feedback_Arch.slabthickness,
insite.T_fromGMPSFA_Step_Feedback_Arch.WidthTwin1,
insite.T_fromGMPSFA_Step_Feedback_Arch.tsInserted
I prefer to analyse this version:
SELECT GG.alzGradeGroupName AS Groep
, SFA.HeatNumber
, SFA.HeatNumberDisplay
, SFA.OrderNumberDisplay AS Opdrachtnr
, SFA.GradeCode AS Type
, SFA.SlabThickness AS Gauge
, SFA.WidthTwin1 AS Width
, SFA.tsInserted AS tsInserted
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN BC.BackGroundColor
WHEN 'OVEN2' THEN BC.BackGroundColor
ELSE NULL
END) AS OVEN_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN BC.FontStyle
WHEN 'OVEN2' THEN BC.FontStyle
ELSE NULL
END) AS OVEN_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 2
ELSE 1
END )
WHEN 'OVEN2' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 1
ELSE 2
END )
ELSE NULL
END) AS OVEN
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN SFA.StartDate
WHEN 'OVEN2' THEN SFA.StartDate
ELSE NULL
END) AS Oven_Start
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
WHEN 'OVEN2' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
ELSE NULL
END) AS Oven_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN SFA.EndDate
WHEN 'OVEN2' THEN SFA.EndDate
ELSE NULL
END) AS OVEN_End
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN SFA.Wait / 60
WHEN 'OVEN2' THEN SFA.Wait / 60
ELSE NULL
END) AS OVEN_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN SFA.Overlap / 60
WHEN 'OVEN2' THEN SFA.Overlap / 60
ELSE NULL
END) AS OVEN_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN BC.BackGroundColor
ELSE NULL
END) AS AOD1_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN BC.FontStyle
ELSE NULL
END) AS AOD1_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN SFA.StartDate
ELSE NULL
END) AS AOD1_Start
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
ELSE NULL
END) AS AOD1_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN SFA.EndDate
ELSE NULL
END) AS AOD1_End
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN SFA.Wait / 60
ELSE NULL
END) AS AOD1_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN SFA.Overlap / 60
ELSE NULL
END) AS AOD1_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN BC.BackGroundColor
WHEN 'VOD2' THEN BackGroundColor
ELSE NULL
END) AS VOD_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN BC.FontStyle
WHEN 'VOD2' THEN FontStyle
ELSE NULL
END) AS VOD_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 2
ELSE 1
END )
WHEN 'VOD2' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 1
ELSE 2
END )
ELSE NULL
END) AS VOD
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN SFA.StartDate
WHEN 'VOD2' THEN SFA.StartDate
ELSE NULL
END) AS VOD_Start
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
WHEN 'VOD2' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
ELSE NULL
END) AS VOD_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN SFA.EndDate
WHEN 'VOD2' THEN SFA.EndDate
ELSE NULL
END) AS VOD_End
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN SFA.Wait / 60
WHEN 'VOD2' THEN SFA.Wait / 60
ELSE NULL
END) AS VOD_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN SFA.Overlap / 60
WHEN 'VOD2' THEN SFA.Overlap / 60
ELSE NULL
END) AS VOD_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN BC.BackGroundColor
WHEN 'AFW2' THEN BC.BackGroundColor
WHEN 'AFW3' THEN BC.BackGroundColor
ELSE NULL
END) AS AFW_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN BC.FontStyle
WHEN 'AFW2' THEN BC.FontStyle
WHEN 'AFW3' THEN BC.FontStyle
ELSE NULL
END) AS AFW_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 2
ELSE 1
END )
WHEN 'AFW3' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 1
ELSE 3
END )
WHEN 'AFW2' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 1
ELSE 2
END )
ELSE NULL
END) AS AFW
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN SFA.StartDate
WHEN 'AFW2' THEN SFA.StartDate
WHEN 'AFW3' THEN SFA.StartDate
ELSE NULL
END) AS AFW_Start
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
WHEN 'AFW3' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
WHEN 'AFW2' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
ELSE NULL
END) AS AFW_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN SFA.EndDate
WHEN 'AFW2' THEN SFA.EndDate
WHEN 'AFW3' THEN SFA.EndDate
ELSE NULL
END) AS AFW_End
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN SFA.Wait / 60
WHEN 'AFW3' THEN SFA.Wait / 60
WHEN 'AFW2' THEN SFA.Wait / 60
ELSE NULL
END) AS AFW_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN SFA.Overlap / 60
WHEN 'AFW3' THEN SFA.Overlap / 60
WHEN 'AFW2' THEN SFA.Overlap / 60
ELSE NULL
END) AS AFW_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN BC.BackGroundColor
ELSE NULL
END) AS KG1_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN BC.FontStyle
ELSE NULL
END) AS KG1_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN SFA.EndSetupDate
ELSE NULL
END) AS KG1_Start
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN DATEDIFF(mi, SFA.EndSetupDate, SFA.EndDate)
ELSE NULL
END) AS KG1_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN SFA.EndDate
ELSE NULL
END) AS KG1_End
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN SFA.Wait / 60
ELSE NULL
END) AS KG1_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN SFA.Overlap / 60
ELSE NULL
END) AS KG1_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN ( CASE Upper(SFA.SetupTypeCC)
WHEN 'BREEDTE' THEN '40 BR'
WHEN 'DIKTE' THEN '75 DI'
WHEN 'TYPE' THEN '40 TY'
WHEN 'TUSSENPAN' THEN '10 TP'
WHEN 'TYPE/TUSSENPAN' THEN '10 TY+TP'
WHEN 'PRODUCTGROEP' THEN '75 DI+BR'
ELSE SFA.SetupTypeCC
END )
ELSE NULL
END) AS KG1_Setup
FROM insite.T_fromGMPSFA_BackGroundColor BC
INNER JOIN insite.T_fromGMPSFA_Step_Feedback_Arch SFA
ON BC.Finished = SFA.Finished
INNER JOIN insite.container C
ON SFA.HeatNumber = C.containername
INNER JOIN insite.mfgorder O
ON O.mfgorderid = C.mfgorderid
INNER JOIN insite.Product P
ON O.inoxintnormbaseid = P.productbaseid
INNER JOIN insite.alzGradeGroup GG
ON P.GradeGroupID = GG.alzGradeGroupID
GROUP BY GG.alzGradeGroupName
, SFA.HeatNumber
, SFA.HeatNumberDisplay
, SFA.OrderNumberDisplay
, SFA.GradeCode
, SFA.slabthickness
, SFA.WidthTwin1
, SFA.tsInserted
Sure. The second one is easier to read because of the formatting. But, as I read the second query, I don't know what BC or SFA is? I would need to be familiar with the database and the naming convention to know it. I'm not convinced aliasing is easier to read. It is a lot easier to code.
May 7, 2009 at 7:25 am
To be fair...
SELECT insite.alzgradegroup.alzgradegroupname AS groep,
insite.t_fromgmpsfa_step_feedback_arch.heatnumber,
insite.t_fromgmpsfa_step_feedback_arch.heatnumberdisplay,
insite.t_fromgmpsfa_step_feedback_arch.ordernumberdisplay AS opdrachtnr,
insite.t_fromgmpsfa_step_feedback_arch.gradecode AS TYPE,
insite.t_fromgmpsfa_step_feedback_arch.slabthickness AS gauge,
insite.t_fromgmpsfa_step_feedback_arch.widthtwin1 AS width,
insite.t_fromgmpsfa_step_feedback_arch.tsinserted AS tsinserted,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'OVEN1'
THEN insite.t_fromgmpsfa_backgroundcolor.backgroundcolor
WHEN 'OVEN2'
THEN insite.t_fromgmpsfa_backgroundcolor.backgroundcolor
ELSE NULL
END ) AS oven_backgroundcolor,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'OVEN1'
THEN insite.t_fromgmpsfa_backgroundcolor.fontstyle
WHEN 'OVEN2'
THEN insite.t_fromgmpsfa_backgroundcolor.fontstyle
ELSE NULL
END ) AS oven_fontstyle,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'OVEN1'
THEN ( CASE
WHEN insite.t_fromgmpsfa_step_feedback_arch.startdate IS NULL
THEN 2
ELSE 1
END )
WHEN 'OVEN2'
THEN ( CASE
WHEN insite.t_fromgmpsfa_step_feedback_arch.startdate IS NULL
THEN 1
ELSE 2
END )
ELSE NULL
END ) AS oven,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'OVEN1'
THEN insite.t_fromgmpsfa_step_feedback_arch.startdate
WHEN 'OVEN2'
THEN insite.t_fromgmpsfa_step_feedback_arch.startdate
ELSE NULL
END ) AS oven_start,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'OVEN1'
THEN datediff ( mi,
insite.t_fromgmpsfa_step_feedback_arch.startdate,
insite.t_fromgmpsfa_step_feedback_arch.enddate
)
WHEN 'OVEN2'
THEN datediff ( mi,
insite.t_fromgmpsfa_step_feedback_arch.startdate,
insite.t_fromgmpsfa_step_feedback_arch.enddate
)
ELSE NULL
END ) AS oven_looptijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'OVEN1'
THEN insite.t_fromgmpsfa_step_feedback_arch.enddate
WHEN 'OVEN2'
THEN insite.t_fromgmpsfa_step_feedback_arch.enddate
ELSE NULL
END ) AS oven_end,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'OVEN1'
THEN insite.t_fromgmpsfa_step_feedback_arch.WAIT / 60
WHEN 'OVEN2'
THEN insite.t_fromgmpsfa_step_feedback_arch.WAIT / 60
ELSE NULL
END ) AS oven_wachttijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'OVEN1'
THEN insite.t_fromgmpsfa_step_feedback_arch.overlap / 60
WHEN 'OVEN2'
THEN insite.t_fromgmpsfa_step_feedback_arch.overlap / 60
ELSE NULL
END ) AS oven_tussentijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AOD1'
THEN insite.t_fromgmpsfa_backgroundcolor.backgroundcolor
ELSE NULL
END ) AS aod1_backgroundcolor,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AOD1'
THEN insite.t_fromgmpsfa_backgroundcolor.fontstyle
ELSE NULL
END ) AS aod1_fontstyle,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AOD1'
THEN insite.t_fromgmpsfa_step_feedback_arch.startdate
ELSE NULL
END ) AS aod1_start,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AOD1'
THEN datediff ( mi,
insite.t_fromgmpsfa_step_feedback_arch.startdate,
insite.t_fromgmpsfa_step_feedback_arch.enddate
)
ELSE NULL
END ) AS aod1_looptijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AOD1'
THEN insite.t_fromgmpsfa_step_feedback_arch.enddate
ELSE NULL
END ) AS aod1_end,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AOD1'
THEN insite.t_fromgmpsfa_step_feedback_arch.WAIT / 60
ELSE NULL
END ) AS aod1_wachttijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AOD1'
THEN insite.t_fromgmpsfa_step_feedback_arch.overlap / 60
ELSE NULL
END ) AS aod1_tussentijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'VOD1'
THEN insite.t_fromgmpsfa_backgroundcolor.backgroundcolor
WHEN 'VOD2'
THEN backgroundcolor
ELSE NULL
END ) AS vod_backgroundcolor,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'VOD1'
THEN insite.t_fromgmpsfa_backgroundcolor.fontstyle
WHEN 'VOD2'
THEN fontstyle
ELSE NULL
END ) AS vod_fontstyle,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'VOD1'
THEN ( CASE
WHEN insite.t_fromgmpsfa_step_feedback_arch.startdate IS NULL
THEN 2
ELSE 1
END )
WHEN 'VOD2'
THEN ( CASE
WHEN insite.t_fromgmpsfa_step_feedback_arch.startdate IS NULL
THEN 1
ELSE 2
END )
ELSE NULL
END ) AS vod,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'VOD1'
THEN insite.t_fromgmpsfa_step_feedback_arch.startdate
WHEN 'VOD2'
THEN insite.t_fromgmpsfa_step_feedback_arch.startdate
ELSE NULL
END ) AS vod_start,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'VOD1'
THEN datediff ( mi,
insite.t_fromgmpsfa_step_feedback_arch.startdate,
insite.t_fromgmpsfa_step_feedback_arch.enddate
)
WHEN 'VOD2'
THEN datediff ( mi,
insite.t_fromgmpsfa_step_feedback_arch.startdate,
insite.t_fromgmpsfa_step_feedback_arch.enddate
)
ELSE NULL
END ) AS vod_looptijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'VOD1'
THEN insite.t_fromgmpsfa_step_feedback_arch.enddate
WHEN 'VOD2'
THEN insite.t_fromgmpsfa_step_feedback_arch.enddate
ELSE NULL
END ) AS vod_end,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'VOD1'
THEN insite.t_fromgmpsfa_step_feedback_arch.WAIT / 60
WHEN 'VOD2'
THEN insite.t_fromgmpsfa_step_feedback_arch.WAIT / 60
ELSE NULL
END ) AS vod_wachttijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'VOD1'
THEN insite.t_fromgmpsfa_step_feedback_arch.overlap / 60
WHEN 'VOD2'
THEN insite.t_fromgmpsfa_step_feedback_arch.overlap / 60
ELSE NULL
END ) AS vod_tussentijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AFW1'
THEN insite.t_fromgmpsfa_backgroundcolor.backgroundcolor
WHEN 'AFW2'
THEN insite.t_fromgmpsfa_backgroundcolor.backgroundcolor
WHEN 'AFW3'
THEN insite.t_fromgmpsfa_backgroundcolor.backgroundcolor
ELSE NULL
END ) AS afw_backgroundcolor,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AFW1'
THEN insite.t_fromgmpsfa_backgroundcolor.fontstyle
WHEN 'AFW2'
THEN insite.t_fromgmpsfa_backgroundcolor.fontstyle
WHEN 'AFW3'
THEN insite.t_fromgmpsfa_backgroundcolor.fontstyle
ELSE NULL
END ) AS afw_fontstyle,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AFW1'
THEN ( CASE
WHEN insite.t_fromgmpsfa_step_feedback_arch.startdate IS NULL
THEN 2
ELSE 1
END )
WHEN 'AFW3'
THEN ( CASE
WHEN insite.t_fromgmpsfa_step_feedback_arch.startdate IS NULL
THEN 1
ELSE 3
END )
WHEN 'AFW2'
THEN ( CASE
WHEN insite.t_fromgmpsfa_step_feedback_arch.startdate IS NULL
THEN 1
ELSE 2
END )
ELSE NULL
END ) AS afw,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AFW1'
THEN insite.t_fromgmpsfa_step_feedback_arch.startdate
WHEN 'AFW2'
THEN insite.t_fromgmpsfa_step_feedback_arch.startdate
WHEN 'AFW3'
THEN insite.t_fromgmpsfa_step_feedback_arch.startdate
ELSE NULL
END ) AS afw_start,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AFW1'
THEN datediff ( mi,
insite.t_fromgmpsfa_step_feedback_arch.startdate,
insite.t_fromgmpsfa_step_feedback_arch.enddate
)
WHEN 'AFW3'
THEN datediff ( mi,
insite.t_fromgmpsfa_step_feedback_arch.startdate,
insite.t_fromgmpsfa_step_feedback_arch.enddate
)
WHEN 'AFW2'
THEN datediff ( mi,
insite.t_fromgmpsfa_step_feedback_arch.startdate,
insite.t_fromgmpsfa_step_feedback_arch.enddate
)
ELSE NULL
END ) AS afw_looptijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AFW1'
THEN insite.t_fromgmpsfa_step_feedback_arch.enddate
WHEN 'AFW2'
THEN insite.t_fromgmpsfa_step_feedback_arch.enddate
WHEN 'AFW3'
THEN insite.t_fromgmpsfa_step_feedback_arch.enddate
ELSE NULL
END ) AS afw_end,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AFW1'
THEN insite.t_fromgmpsfa_step_feedback_arch.WAIT / 60
WHEN 'AFW3'
THEN insite.t_fromgmpsfa_step_feedback_arch.WAIT / 60
WHEN 'AFW2'
THEN insite.t_fromgmpsfa_step_feedback_arch.WAIT / 60
ELSE NULL
END ) AS afw_wachttijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'AFW1'
THEN insite.t_fromgmpsfa_step_feedback_arch.overlap / 60
WHEN 'AFW3'
THEN insite.t_fromgmpsfa_step_feedback_arch.overlap / 60
WHEN 'AFW2'
THEN insite.t_fromgmpsfa_step_feedback_arch.overlap / 60
ELSE NULL
END ) AS afw_tussentijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'KG1'
THEN insite.t_fromgmpsfa_backgroundcolor.backgroundcolor
ELSE NULL
END ) AS kg1_backgroundcolor,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'KG1'
THEN insite.t_fromgmpsfa_backgroundcolor.fontstyle
ELSE NULL
END ) AS kg1_fontstyle,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'KG1'
THEN insite.t_fromgmpsfa_step_feedback_arch.endsetupdate
ELSE NULL
END ) AS kg1_start,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'KG1'
THEN datediff ( mi,
insite.t_fromgmpsfa_step_feedback_arch.endsetupdate,
insite.t_fromgmpsfa_step_feedback_arch.enddate
)
ELSE NULL
END ) AS kg1_looptijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'KG1'
THEN insite.t_fromgmpsfa_step_feedback_arch.enddate
ELSE NULL
END ) AS kg1_end,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'KG1'
THEN insite.t_fromgmpsfa_step_feedback_arch.WAIT / 60
ELSE NULL
END ) AS kg1_wachttijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'KG1'
THEN insite.t_fromgmpsfa_step_feedback_arch.overlap / 60
ELSE NULL
END ) AS kg1_tussentijd,
MAX ( CASE insite.t_fromgmpsfa_step_feedback_arch.resourcename
WHEN 'KG1'
THEN ( CASE UPPER ( insite.t_fromgmpsfa_step_feedback_arch.setuptypecc )
WHEN 'BREEDTE'
THEN '40 BR'
WHEN 'DIKTE'
THEN '75 DI'
WHEN 'TYPE'
THEN '40 TY'
WHEN 'TUSSENPAN'
THEN '10 TP'
WHEN 'TYPE/TUSSENPAN'
THEN '10 TY+TP'
WHEN 'PRODUCTGROEP'
THEN '75 DI+BR'
ELSE insite.t_fromgmpsfa_step_feedback_arch.setuptypecc
END
)
ELSE NULL
END ) AS kg1_setup
FROM insite.t_fromgmpsfa_backgroundcolor INNER JOIN insite.t_fromgmpsfa_step_feedback_arch
ON insite.t_fromgmpsfa_backgroundcolor.finished = insite.t_fromgmpsfa_step_feedback_arch.finished
INNER JOIN insite.container ON insite.t_fromgmpsfa_step_feedback_arch.heatnumber = insite.container.containername
INNER JOIN insite.mfgorder ON insite.mfgorder.mfgorderid = insite.container.mfgorderid
INNER JOIN insite.product ON insite.mfgorder.inoxintnormbaseid = insite.product.productbaseid
INNER JOIN insite.alzgradegroup ON insite.product.gradegroupid = insite.alzgradegroup.alzgradegroupid
GROUP BY insite.alzgradegroup.alzgradegroupname,
insite.t_fromgmpsfa_step_feedback_arch.heatnumber,
insite.t_fromgmpsfa_step_feedback_arch.heatnumberdisplay,
insite.t_fromgmpsfa_step_feedback_arch.ordernumberdisplay,
insite.t_fromgmpsfa_step_feedback_arch.gradecode,
insite.t_fromgmpsfa_step_feedback_arch.slabthickness,
insite.t_fromgmpsfa_step_feedback_arch.widthtwin1,
insite.t_fromgmpsfa_step_feedback_arch.tsinserted
May 7, 2009 at 7:40 am
skjoldtc (5/7/2009)
ALZDBA (5/7/2009)
skjoldtc (5/7/2009)
I find the intellisense helpful in SS2008. But then, I haven't coded in T-SQL forever. I've gotten used to the intellisense in Visual Studio. Maybe that's helped me use it in SS2008.The one thing that's hard for me to understand in the aliasing. Why is aliasing a table name easier for another coder to understand? It saves time typing but isn't a fully qualified name easier to read and understand?
Lets analyse a simple query...
SELECT insite.alzGradeGroup.alzGradeGroupName AS Groep,
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumber,
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumberDisplay,
insite.T_fromGMPSFA_Step_Feedback_Arch.OrderNumberDisplay AS Opdrachtnr,
insite.T_fromGMPSFA_Step_Feedback_Arch.GradeCode AS Type,
insite.T_fromGMPSFA_Step_Feedback_Arch.SlabThickness AS Gauge,
insite.T_fromGMPSFA_Step_Feedback_Arch.WidthTwin1 AS Width,
insite.T_fromGMPSFA_Step_Feedback_Arch.tsInserted AS tsInserted,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
ELSE NULL END) AS OVEN_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
ELSE NULL END) AS OVEN_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 2 ELSE 1 END)
WHEN 'OVEN2' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 1 ELSE 2 END) ELSE NULL END) AS OVEN,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
ELSE NULL END) AS Oven_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate)
WHEN 'OVEN2' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS Oven_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS OVEN_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait / 60
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 ELSE NULL END) AS OVEN_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'OVEN1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap / 60
WHEN 'OVEN2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS OVEN_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
ELSE NULL END) AS AOD1_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
ELSE NULL END) AS AOD1_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
ELSE NULL END) AS AOD1_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS AOD1_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS AOD1_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 ELSE NULL END) AS AOD1_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS AOD1_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
WHEN 'VOD2' THEN BackGroundColor ELSE NULL END)
AS VOD_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
WHEN 'VOD2' THEN FontStyle ELSE NULL END)
AS VOD_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 2 ELSE 1 END)
WHEN 'VOD2' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 1 ELSE 2 END) ELSE NULL END) AS VOD,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
WHEN 'VOD2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
ELSE NULL END) AS VOD_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate)
WHEN 'VOD2' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS VOD_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
WHEN 'VOD2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS VOD_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 WHEN 'VOD2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 ELSE NULL END) AS VOD_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'VOD1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 WHEN 'VOD2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS VOD_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
WHEN 'AFW2' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
WHEN 'AFW3' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
ELSE NULL END) AS AFW_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
WHEN 'AFW2' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
WHEN 'AFW3' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
ELSE NULL END) AS AFW_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 2 ELSE 1 END)
WHEN 'AFW3' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 1 ELSE 3 END)
WHEN 'AFW2' THEN (CASE WHEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
IS NULL THEN 1 ELSE 2 END) ELSE NULL END) AS AFW,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
WHEN 'AFW2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
WHEN 'AFW3' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate
ELSE NULL END) AS AFW_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate)
WHEN 'AFW3' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate)
WHEN 'AFW2' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.StartDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS AFW_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
WHEN 'AFW2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
WHEN 'AFW3' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS AFW_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait/60
WHEN 'AFW3' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait/60
WHEN 'AFW2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait
/ 60 ELSE NULL END) AS AFW_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'AFW1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap / 60
WHEN 'AFW3' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap / 60
WHEN 'AFW2' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS AFW_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_BackGroundColor.BackGroundColor
ELSE NULL END) AS KG1_BackGroundColor,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_BackGroundColor.FontStyle
ELSE NULL END) AS KG1_FontStyle,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndSetupDate
ELSE NULL END) AS KG1_Start,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN DATEDIFF(mi,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndSetupDate,
insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate) ELSE NULL
END) AS KG1_Looptijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.EndDate
ELSE NULL END) AS KG1_End,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Wait /
60 ELSE NULL END) AS KG1_Wachttijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN insite.T_fromGMPSFA_Step_Feedback_Arch.Overlap
/ 60 ELSE NULL END) AS KG1_TussenTijd,
MAX(CASE insite.T_fromGMPSFA_Step_Feedback_Arch.ResourceName
WHEN 'KG1' THEN (CASE Upper(insite.T_fromGMPSFA_Step_Feedback_Arch.SetupTypeCC)
WHEN 'BREEDTE' THEN '40 BR' WHEN 'DIKTE' THEN '75 DI' WHEN
'TYPE' THEN '40 TY' WHEN 'TUSSENPAN' THEN '10 TP' WHEN
'TYPE/TUSSENPAN' THEN '10 TY+TP' WHEN 'PRODUCTGROEP'
THEN '75 DI+BR' ELSE insite.T_fromGMPSFA_Step_Feedback_Arch.SetupTypeCC
END) ELSE NULL END) AS KG1_Setup
FROM insite.T_fromGMPSFA_BackGroundColor INNER JOIN
insite.T_fromGMPSFA_Step_Feedback_Arch ON
insite.T_fromGMPSFA_BackGroundColor.Finished = insite.T_fromGMPSFA_Step_Feedback_Arch.Finished
INNER JOIN
insite.container ON
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumber = insite.container.containername
INNER JOIN
insite.mfgorder ON
insite.mfgorder.mfgorderid = insite.container.mfgorderid INNER JOIN
insite. Product ON
insite.mfgorder.inoxintnormbaseid = insite. product .productbaseid
INNER JOIN
insite.alzGradeGroup ON
insite.Product.GradeGroupID = insite.alzGradeGroup.alzGradeGroupID
GROUP BY insite.alzGradeGroup.alzGradeGroupName,
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumber,
insite.T_fromGMPSFA_Step_Feedback_Arch.HeatNumberDisplay,
insite.T_fromGMPSFA_Step_Feedback_Arch.OrderNumberDisplay,
insite.T_fromGMPSFA_Step_Feedback_Arch.GradeCode,
insite.T_fromGMPSFA_Step_Feedback_Arch.slabthickness,
insite.T_fromGMPSFA_Step_Feedback_Arch.WidthTwin1,
insite.T_fromGMPSFA_Step_Feedback_Arch.tsInserted
I prefer to analyse this version:
SELECT GG.alzGradeGroupName AS Groep
, SFA.HeatNumber
, SFA.HeatNumberDisplay
, SFA.OrderNumberDisplay AS Opdrachtnr
, SFA.GradeCode AS Type
, SFA.SlabThickness AS Gauge
, SFA.WidthTwin1 AS Width
, SFA.tsInserted AS tsInserted
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN BC.BackGroundColor
WHEN 'OVEN2' THEN BC.BackGroundColor
ELSE NULL
END) AS OVEN_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN BC.FontStyle
WHEN 'OVEN2' THEN BC.FontStyle
ELSE NULL
END) AS OVEN_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 2
ELSE 1
END )
WHEN 'OVEN2' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 1
ELSE 2
END )
ELSE NULL
END) AS OVEN
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN SFA.StartDate
WHEN 'OVEN2' THEN SFA.StartDate
ELSE NULL
END) AS Oven_Start
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
WHEN 'OVEN2' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
ELSE NULL
END) AS Oven_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN SFA.EndDate
WHEN 'OVEN2' THEN SFA.EndDate
ELSE NULL
END) AS OVEN_End
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN SFA.Wait / 60
WHEN 'OVEN2' THEN SFA.Wait / 60
ELSE NULL
END) AS OVEN_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'OVEN1' THEN SFA.Overlap / 60
WHEN 'OVEN2' THEN SFA.Overlap / 60
ELSE NULL
END) AS OVEN_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN BC.BackGroundColor
ELSE NULL
END) AS AOD1_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN BC.FontStyle
ELSE NULL
END) AS AOD1_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN SFA.StartDate
ELSE NULL
END) AS AOD1_Start
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
ELSE NULL
END) AS AOD1_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN SFA.EndDate
ELSE NULL
END) AS AOD1_End
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN SFA.Wait / 60
ELSE NULL
END) AS AOD1_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'AOD1' THEN SFA.Overlap / 60
ELSE NULL
END) AS AOD1_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN BC.BackGroundColor
WHEN 'VOD2' THEN BackGroundColor
ELSE NULL
END) AS VOD_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN BC.FontStyle
WHEN 'VOD2' THEN FontStyle
ELSE NULL
END) AS VOD_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 2
ELSE 1
END )
WHEN 'VOD2' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 1
ELSE 2
END )
ELSE NULL
END) AS VOD
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN SFA.StartDate
WHEN 'VOD2' THEN SFA.StartDate
ELSE NULL
END) AS VOD_Start
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
WHEN 'VOD2' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
ELSE NULL
END) AS VOD_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN SFA.EndDate
WHEN 'VOD2' THEN SFA.EndDate
ELSE NULL
END) AS VOD_End
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN SFA.Wait / 60
WHEN 'VOD2' THEN SFA.Wait / 60
ELSE NULL
END) AS VOD_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'VOD1' THEN SFA.Overlap / 60
WHEN 'VOD2' THEN SFA.Overlap / 60
ELSE NULL
END) AS VOD_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN BC.BackGroundColor
WHEN 'AFW2' THEN BC.BackGroundColor
WHEN 'AFW3' THEN BC.BackGroundColor
ELSE NULL
END) AS AFW_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN BC.FontStyle
WHEN 'AFW2' THEN BC.FontStyle
WHEN 'AFW3' THEN BC.FontStyle
ELSE NULL
END) AS AFW_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 2
ELSE 1
END )
WHEN 'AFW3' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 1
ELSE 3
END )
WHEN 'AFW2' THEN ( CASE WHEN SFA.StartDate IS NULL THEN 1
ELSE 2
END )
ELSE NULL
END) AS AFW
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN SFA.StartDate
WHEN 'AFW2' THEN SFA.StartDate
WHEN 'AFW3' THEN SFA.StartDate
ELSE NULL
END) AS AFW_Start
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
WHEN 'AFW3' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
WHEN 'AFW2' THEN DATEDIFF(mi, SFA.StartDate, SFA.EndDate)
ELSE NULL
END) AS AFW_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN SFA.EndDate
WHEN 'AFW2' THEN SFA.EndDate
WHEN 'AFW3' THEN SFA.EndDate
ELSE NULL
END) AS AFW_End
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN SFA.Wait / 60
WHEN 'AFW3' THEN SFA.Wait / 60
WHEN 'AFW2' THEN SFA.Wait / 60
ELSE NULL
END) AS AFW_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'AFW1' THEN SFA.Overlap / 60
WHEN 'AFW3' THEN SFA.Overlap / 60
WHEN 'AFW2' THEN SFA.Overlap / 60
ELSE NULL
END) AS AFW_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN BC.BackGroundColor
ELSE NULL
END) AS KG1_BackGroundColor
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN BC.FontStyle
ELSE NULL
END) AS KG1_FontStyle
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN SFA.EndSetupDate
ELSE NULL
END) AS KG1_Start
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN DATEDIFF(mi, SFA.EndSetupDate, SFA.EndDate)
ELSE NULL
END) AS KG1_Looptijd
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN SFA.EndDate
ELSE NULL
END) AS KG1_End
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN SFA.Wait / 60
ELSE NULL
END) AS KG1_Wachttijd
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN SFA.Overlap / 60
ELSE NULL
END) AS KG1_TussenTijd
, MAX(CASE SFA.ResourceName
WHEN 'KG1' THEN ( CASE Upper(SFA.SetupTypeCC)
WHEN 'BREEDTE' THEN '40 BR'
WHEN 'DIKTE' THEN '75 DI'
WHEN 'TYPE' THEN '40 TY'
WHEN 'TUSSENPAN' THEN '10 TP'
WHEN 'TYPE/TUSSENPAN' THEN '10 TY+TP'
WHEN 'PRODUCTGROEP' THEN '75 DI+BR'
ELSE SFA.SetupTypeCC
END )
ELSE NULL
END) AS KG1_Setup
FROM insite.T_fromGMPSFA_BackGroundColor BC
INNER JOIN insite.T_fromGMPSFA_Step_Feedback_Arch SFA
ON BC.Finished = SFA.Finished
INNER JOIN insite.container C
ON SFA.HeatNumber = C.containername
INNER JOIN insite.mfgorder O
ON O.mfgorderid = C.mfgorderid
INNER JOIN insite.Product P
ON O.inoxintnormbaseid = P.productbaseid
INNER JOIN insite.alzGradeGroup GG
ON P.GradeGroupID = GG.alzGradeGroupID
GROUP BY GG.alzGradeGroupName
, SFA.HeatNumber
, SFA.HeatNumberDisplay
, SFA.OrderNumberDisplay
, SFA.GradeCode
, SFA.slabthickness
, SFA.WidthTwin1
, SFA.tsInserted
Sure. The second one is easier to read because of the formatting. But, as I read the second query, I don't know what BC or SFA is? I would need to be familiar with the database and the naming convention to know it. I'm not convinced aliasing is easier to read. It is a lot easier to code.
Some time Aliasing is easier to read when you are extracting a set of records from a common table like extracting Sales records from Common transaction table, like...
SELECT Sales.Date, Sales.DocumentNo, Customers.Name AS CustomerName, Sales.Amount
FROM CommonTransactions AS Sales
INNER JOIN Customers ON Customers.ID = Sales.CustomerID
WHERE Sales.TransactionType = 'SALES'
Yes i do agree aliasing tables like 'BC or SFA' are not a good practice. 🙂
May 7, 2009 at 7:41 am
I find the example disingenuous. You CANNOT use a sample query of "select.. from titles join authors" as an example of a place not to use table aliases. Those are tiny little baby table names. You need to look at real world examples where your tables will have multiple meaningful words in the table name, and will be named ATTRIBUTE_MAPPING_PROFILE_SYNCHRONIZATION_VENDOR or COMPANY_GROUP_CREDIT_CARD_BIN_NUMBER_RESTRICTIONS or something like that. Are you seriously going to type a 50 character table name as your prefix for every column? Heck no. You will abbreviate it as CGCCBNR or cardBin or something (depending on your style). There is room for debate as to whether the proper style is an acronym of the full table name, or a shorthand semantic name that captures the business purpose of the table name, definitely. But you would drive yourself nuts trying to use the full table name everywhere.
May 7, 2009 at 7:47 am
For the record, I'm a leading commas guy, Jeff is wrong, and it's easier to read. All you trailing comma folk need to change!:-D:-D:w00t:
However, I do think that in your group, you need to be consistent. I'd hate to upper case SQL, or go with trailing commas, but I would for consistency's sake.
My preference is to camel case tables, go with 2-4 letter aliases that are meaningful (as in ALZDBA's example), and stick with lower case for most typing.
Also, you don't need to quote those huge code blocks. We'll figure out who you're replying to.
May 7, 2009 at 7:48 am
A bit of aliasing when quoting other people posts might help extended the life of my scroll wheel 😀
May 7, 2009 at 7:52 am
[/quote]
Yes, but V for Vendor and VA for VendorAddress would make it more meaningful and understandable (if you know the schema), wouldn't it?
[/quote]
I agree, but.....
I always found it easier to visualize the execution plan by referring to the driving table as "a". Plus, I wouldn't have to think about things like "well, I've got two tables that start with 'cl', so I'll call one 'cla' and other one 'cli' and...", well, pretty soon the syntax becomes cluttered again.
So for me it's always been abc. Simple.
What else I find interesting that, in the Oracle world, I got used to underscores and either all upper-case for keywords and all lower-case for indentifiers. I call it "Oracle style".
But now I work for a company that uses SQL Server and it's all mixed case.
So I no longer use "abc" and I code in SQL Server style. I tend to go with the flow.
May 7, 2009 at 7:52 am
We use a, b, c, etc as table aliases all the time in my SQL shop and we have never had trouble reading queries. Longer named aliases can make code look impossibly cluttered. Calling this way "wrong" is ridiculous.
May 7, 2009 at 7:54 am
... Jeff is wrong...
Wow! :Wow:
No beating aound the bush eh Steve 😉
A poke in the eye, that'll trump his Pork Chops :w00t:
Far away is close at hand in the images of elsewhere.
Anon.
May 7, 2009 at 7:57 am
David Burrows (5/7/2009)
... Jeff is wrong...
Wow! :Wow:
No beating aound the bush eh Steve 😉
A poke in the eye, that'll trump his Pork Chops :w00t:
He'll start an anti leading comma campaign soon:
CBAC (Pronounced cee-bac), Comma By Agonising Comma 😀
May 7, 2009 at 8:10 am
I tend to agree with careful use of aliases (make them readable and cogent) because:
I have experienced the ambiguous reference reference error and reacted with "What do mean 'a' is ambiguous? Oh, I used 'a' twice. Need more coffee."
I have used a lot of monitor space to spread out someone else's code to understand their references (a, t, tl, m, st all in one query wiht some for external references).
It would almost be worth the inclusion of aliasing in a form of declare statement at the beginning of the code. (I know, I'll probably burn for that. But at least you would know where to look.)
This goes along with the use of Temp, yes that makes it quick but not clear. I've shot myself in the foot often enough to now spend a moment or to more to use a more descriptive name.
As I gain more experience and practice I find myself reviewing my own and others code with a more critical eye. The more we as a community write code the more we will be having to fix/modify/convert or in some way dig into someone elses code. Some form of standardization would be helpful as long as it is not restrictive to productivity.
With all that said "How 'bout those synonyms?" Is any one using them? If you are using them would you alias them and if so would you do something special to identify the aliasing of a synonym?
Al Kessler
It's too bad that
Common Sense Isn't
May 7, 2009 at 8:13 am
" I would need to be familiar with the database and the naming convention to know it."
Maybe, im being harsh (no thing persona to anyone) but... To be able to be completely effective (as opposed to superficially) on complex queries an understanding of the database and data is essential. I never allow people the scope to change things they dont understand. Which is another reason why long winded aliassing is a waste of time.
Viewing 15 posts - 31 through 45 (of 79 total)
You must be logged in to reply to this topic. Login to reply