Enhancing the readability of your code: Table aliasing in sql

  • 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?

  • 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

  • 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.

  • 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.

  • 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

  • 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. 🙂

  • 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.

  • 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.

  • A bit of aliasing when quoting other people posts might help extended the life of my scroll wheel 😀

  • [/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.

  • 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.

  • ... 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.

  • 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 😀

  • 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

  • " 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