Tukukkk

I have created an SP which builds and then runs a SQL query. This query is pulling in data from several tables and 2 DBs for creating certificates for shipping. Right now this is taking between 50 to 110 seconds to run. This is too long as we have a lot of shipments that go out and need to run this a lot. I would like to try getting it down to about 15 seconds if that is possible, but I have run out of ideas on what else I can try.

I have played around with Indexes and have added (and removed) clustered and non-clustered indexes on most of the tables involved. I've reorganized that building of the query and created the #TEMP table at the top to help with running faster, but nothing has made a big enough difference.

Here is the SP query:

@PackingSlipId varchar(25) = '',

@PackSales int = 0, -- 0 = Packingslip, 1 = SalesId

@PrintSQL int = 0

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;



-- Insert statements for procedure here



declare @tsql varchar(max)

declare @SalesName varchar(150)



SELECT ProductionId, Max(ID) AS Id INTO #TEMP

FROM sdiProductionChemistry GROUP BY ProductionId 

--Set the SalesName for use in the dynamic sql below

set @SalesName = case when @PackSales = 0 THEN (

        select distinct st.SALESNAME

        FROM InventTrans                        AS IT

        LEFT OUTER JOIN InventTable             AS I    ON IT.ItemId            = I.ItemId

        LEFT OUTER JOIN InventDim               AS ID   ON IT.INVENTDIMID       = ID.InventDimId

        LEFT OUTER JOIN SalesTable              AS ST   ON IT.TransRefId        = ST.SalesId

        LEFT OUTER JOIN SDICustomerSpecs        AS SCS  ON ST.CustAccount       = SCS.CustomerId    AND IT.ItemId = SCS.ItemId

        LEFT OUTER JOIN SDIInventory            AS SI   ON ID.InventBatchId     = SI.BatchId

        LEFT OUTER JOIN SDIProduction           AS P    ON SI.ProductionId      = P.Id

        LEFT OUTER JOIN #Temp                           ON P.Id                 = #Temp.PRODUCTIONID

        LEFT OUTER JOIN SDIPRODUCTIONCHEMISTRY  AS SPC  ON #Temp.PRODUCTIONID   = SPC.ProductionId  AND SPC.Id = #Temp.Id

        WHERE IT.PackingSlipId = @PackingSlipId)

    when @PackSales = 1 then (

        select distinct st.SALESNAME

        FROM InventTrans                        AS IT

        LEFT OUTER JOIN InventTable             AS I    ON IT.ItemId            = I.ItemId

        LEFT OUTER JOIN InventDim               AS ID   ON IT.INVENTDIMID       = ID.InventDimId

        LEFT OUTER JOIN SalesTable              AS ST   ON IT.TransRefId        = ST.SalesId

        LEFT OUTER JOIN SDICustomerSpecs        AS SCS  ON ST.CustAccount       = SCS.CustomerId    AND IT.ItemId = SCS.ItemId

        LEFT OUTER JOIN SDIInventory            AS SI   ON ID.InventBatchId     = SI.BatchId

        LEFT OUTER JOIN SDIProduction           AS P    ON SI.ProductionId      = P.Id

        LEFT OUTER JOIN #Temp                           ON P.Id                 = #Temp.PRODUCTIONID

        LEFT OUTER JOIN SDIPRODUCTIONCHEMISTRY  AS SPC  ON #Temp.PRODUCTIONID   = SPC.ProductionId  AND SPC.Id = #Temp.Id

        WHERE IT.TransRefId = @PackingSlipId) end



set @tsql = '

SELECT DISTINCT P.Id,

    ID.InventBatchId AS CoilId, IT.DatePhysical AS DlvDate,'

        IF (@PackSales) = 1

            SET @tsql = @tsql   +  '''' + @PackingSlipId + ''' AS SalesOrderId, IT.PackingSlipId As PackingSlipId,'

        ELSE

            SET @tsql = @tsql   + 'IT.TransRefId AS SalesOrderId, ''' + @PackingSlipId + ''' As PackingSlipId,'

SET @tsql = @tsql   + ' 

    CASE

        WHEN NOT SI.Diameter IS NULL THEN SI.Diameter

        ELSE xSI.Diameter END AS Diameter,

    SI.Leco, SI.Tensilestrength, 

    CASE WHEN NOT SI.E200 IS NULL AND SI.E200 > 0 THEN convert(varchar,convert(numeric(10,1),SI.E200))

        WHEN NOT xSI.Elongation IS NULL AND xSI.Elongation > 0 THEN convert(varchar,convert(numeric(10,1),xSI.Elongation))

        ELSE ''> 35'' END AS E200, 

    CASE WHEN NOT P.HeatNumber IS NULL THEN P.HeatNumber

        ELSE xSI.BreakDownId END AS HeatNumber, 



    --xSA.Heatnumber as SpectroHeatNumber,



    CASE WHEN NOT SI.NetWeight IS NULL THEN SI.NetWeight

        ELSE xSI.GrossWeight - xSI.TareWeight END AS NetWeight, 

    CASE 

        WHEN SI.CertConductivity = 0 THEN

            SI.IACS_REAL

        WHEN SI.CertConductivity > 0 THEN

            SI.CertConductivity 

    END AS IACS, (SPC.CU + (SPC.AG / 10000)) AS CUAG,

    ST.SalesName, ST.PurchOrderFormNum AS CustomerPO,

    xSI.Grm,

    -- Customer Spec Min/Max Fields

    SCS.CUAGMin, SCS.CUAGMax, SCS.DiameterMin, SCS.DiameterMax, SCS.ElongMin, SCS.ElongMax,

    SCS.StrengthMin, SCS.StrengthMax, SCS.OxygenMin, SCS.OxygenMax, SCS.ConductivityMin, SCS.ConductivityMax,

    SCS.GrmMin, SCS.GrmMax, SCS.PopMin AS OxideMin, SCS.PopMax AS OxideMax,

    SCS.ZnMax, SCS.ZnMin, SCS.PbMax, SCS.PbMin, SCS.SnMax, SCS.SnMin, SCS.PMax, SCS.PMin, SCS.MnMax, SCS.MnMin,

    SCS.FeMax, SCS.FeMin, SCS.NiMax, SCS.NiMin, SCS.SiMax, SCS.SiMin, SCS.MgMax, SCS.MgMin, SCS.CrMax, SCS.CrMin,

    SCS.TeMax, SCS.TeMin, SCS.AsMax, SCS.AsMin, SCS.SeMax, SCS.SeMin, SCS.SbMax, SCS.SbMin, SCS.CdMax, SCS.CdMin,

    SCS.BiMax, SCS.BiMin, SCS.AgMax, SCS.AgMin, SCS.CoMax, SCS.CoMin, SCS.AlMax, SCS.AlMin, SCS.SMax, SCS.SMin,

    SCS.BeMax, SCS.BeMin, SCS.HRFMax, SCS.HRFMin,



    I.ItemName

    -- Element values to show

    ' 

    --/*

if(SELECT ZnShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Zn'

ELSE

    SET @tsql = @tsql + ', 0 Zn'

if(SELECT PbShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Pb'

ELSE

    SET @tsql = @tsql + ', 0 Pb'

if(SELECT SnShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Sn'

ELSE

    SET @tsql = @tsql + ', 0 Sn'

if(SELECT PShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.P'

ELSE

    SET @tsql = @tsql + ', 0 P'

if(SELECT MnShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Mn'

ELSE

    SET @tsql = @tsql + ', 0 Mn'

if(SELECT FeShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Fe'

ELSE

    SET @tsql = @tsql + ', 0 Fe'

if(SELECT NiShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Ni'

ELSE

    SET @tsql = @tsql + ', 0 Ni'

if(SELECT SiShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Si'

ELSE

    SET @tsql = @tsql + ', 0 Si'

if(SELECT MgShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Mg'

ELSE

    SET @tsql = @tsql + ', 0 Mg'

if(SELECT CrShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Cr'

ELSE

    SET @tsql = @tsql + ', 0 Cr'

if(SELECT TeShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Te'

ELSE

    SET @tsql = @tsql + ', 0 Te'

if(SELECT AsShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.As'

ELSE

    SET @tsql = @tsql + ', 0 [As]'

if(SELECT SeShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Se'

ELSE

    SET @tsql = @tsql + ', 0 Se'

if(SELECT SbShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Sb'

ELSE

    SET @tsql = @tsql + ', 0 Sb'

if(SELECT CdShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Cd'

ELSE

    SET @tsql = @tsql + ', 0 Cd'

if(SELECT BiShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Bi'

ELSE

    SET @tsql = @tsql + ', 0 Bi'

if(SELECT AgShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Ag'

ELSE

    SET @tsql = @tsql + ', 0 Ag'

if(SELECT CoShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Co'

ELSE

    SET @tsql = @tsql + ', 0 Co' 

if(SELECT AlShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Al'

ELSE

    SET @tsql = @tsql + ', 0 Al'

if(SELECT SShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.S'

ELSE

    SET @tsql = @tsql + ', 0 S'

if(SELECT BeShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', xSA.Be'

ELSE

    SET @tsql = @tsql + ', 0 Be'

if(SELECT HRFShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ', 0 HRF'

ELSE

    SET @tsql = @tsql + ', 0 HRF'

if(SELECT OxideShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295

    SET @tsql = @tsql + ',  CASE WHEN SI.POP_EXTERNAL < SI.POP_INTERNAL THEN SI.POP_EXTERNAL

                                WHEN SI.POP_INTERNAL < SI.POP_EXTERNAL THEN SI.POP_INTERNAL

                                ELSE 0 END AS SurfaceOxide'

ELSE

    SET @tsql = @tsql + ', 0 SurfaceOxide'



set @tsql = @tsql + '

-- What to show

, case SCSS.ZnShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as ZnShow

, case SCSS.PbShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as PbShow

, case SCSS.SnShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SnShow

, case SCSS.PShow               when -1561783295 then 1 when -1561783296 then 0 else 0 end as PShow

, case SCSS.MnShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as MnShow

, case SCSS.FeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as FeShow

, case SCSS.NiShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as NiShow

, case SCSS.SiShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SiShow

, case SCSS.MgShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as MgShow

, case SCSS.CrShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as CrShow

, case SCSS.TeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as TeShow

, case SCSS.AsShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as AsShow

, case SCSS.SeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SeShow

, case SCSS.SbShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SbShow

, case SCSS.CdShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as CdShow

, case SCSS.BiShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as BiShow

, case SCSS.AgShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as AgShow

, case SCSS.CoShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as CoShow

, case SCSS.AlShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as AlShow

, case SCSS.SShow               when -1561783295 then 1 when -1561783296 then 0 else 0 end as SShow

, case SCSS.BeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as BeShow

, case SCSS.HRFShow             when -1561783295 then 1 when -1561783296 then 0 else 0 end as HRFShow

, case SCSS.OxideShow           when -1561783295 then 1 when -1561783296 then 0 else 0 end as OxideShow

, case SCSS.CuAgShow            when -1561783295 then 1 when -1561783296 then 0 else 0 end as CuAgShow

, case SCSS.DiameterShow        when -1561783295 then 1 when -1561783296 then 0 else 0 end as DiameterShow

, case SCSS.ElongationShow      when -1561783295 then 1 when -1561783296 then 0 else 0 end as ElongationShow

, case SCSS.StrengthShow        when -1561783295 then 1 when -1561783296 then 0 else 0 end as StrengthShow

, case SCSS.OxygenShow          when -1561783295 then 1 when -1561783296 then 0 else 0 end as OxygenShow

, case SCSS.ConductivityShow    when -1561783295 then 1 when -1561783296 then 0 else 0 end as ConductivityShow

, case SCSS.GRMShow             when -1561783295 then 1 when -1561783296 then 0 else 0 end as GRMShow'



    --*/

    set @tsql = @tsql + '

FROM InventTrans AS IT

LEFT OUTER JOIN InventTable                             AS I    ON IT.ItemId            = I.ItemId

LEFT OUTER JOIN InventDim                               AS ID   ON IT.INVENTDIMID       = ID.InventDimId

LEFT OUTER JOIN SalesTable                              AS ST   ON IT.TransRefId        = ST.SalesId

LEFT OUTER JOIN SDICustomerSpecs                        AS SCS  ON ST.CustAccount       = SCS.CustomerId AND IT.ItemId = SCS.ItemId

LEFT OUTER JOIN SDIInventory                            AS SI   ON ID.InventBatchId     = SI.BatchId

LEFT OUTER JOIN SDICustomerSpecSheets                   AS SCSS ON SCSS.CustomerName    = ST.SalesName

LEFT OUTER JOIN LAFARGA.LaFargaProd.dbo.BreakdownItem   AS xSI  ON ID.InventBatchId     = xSI.BatchId

LEFT OUTER JOIN SDIProduction                           AS P    ON SI.ProductionId      = P.Id

LEFT OUTER JOIN #Temp                                   AS T    ON P.Id                 = T.PRODUCTIONID

LEFT OUTER JOIN SDIPRODUCTIONCHEMISTRY                  AS SPC  ON T.PRODUCTIONID   = SPC.ProductionId AND SPC.Id = T.Id

LEFT OUTER JOIN LAFARGA.LaFargaProd.dbo.vSpectroAssays  AS xSA  ON xSA.BatchID          = ID.InventBatchId

WHERE '

IF (@PackSales) = 1

    SET @tsql = @tsql   + 'IT.TransRefId = ''' + @PackingSlipId + ''''

ELSE

    SET @tsql = @tsql   + 'IT.PackingSlipId = ''' + @PackingSlipId  + ''''



SET @tsql = @tsql   + '

ORDER BY ID.InventBatchId'



IF (@PrintSQL = 1)

BEGIN 

    print @tsql

END

IF (@PrintSQL = 0)

BEGIN

    execute (@tsql)

END



Drop Table #Temp

END

Then here is what this creates and runs at the end (or prints if I'm testing):

SELECT DISTINCT P.Id,

    ID.InventBatchId AS CoilId, IT.DatePhysical AS DlvDate,IT.TransRefId AS SalesOrderId, 'RPS115898' As PackingSlipId, 

    CASE

        WHEN NOT SI.Diameter IS NULL THEN SI.Diameter

        ELSE xSI.Diameter END AS Diameter,

    SI.Leco, SI.Tensilestrength, 

    CASE WHEN NOT SI.E200 IS NULL AND SI.E200 > 0 THEN convert(varchar,convert(numeric(10,1),SI.E200))

        WHEN NOT xSI.Elongation IS NULL AND xSI.Elongation > 0 THEN convert(varchar,convert(numeric(10,1),xSI.Elongation))

        ELSE '> 35' END AS E200, 

    CASE WHEN NOT P.HeatNumber IS NULL THEN P.HeatNumber

        ELSE xSI.BreakDownId END AS HeatNumber, 



    --xSA.Heatnumber as SpectroHeatNumber,



    CASE WHEN NOT SI.NetWeight IS NULL THEN SI.NetWeight

        ELSE xSI.GrossWeight - xSI.TareWeight END AS NetWeight, 

    CASE 

        WHEN SI.CertConductivity = 0 THEN

            SI.IACS_REAL

        WHEN SI.CertConductivity > 0 THEN

            SI.CertConductivity 

    END AS IACS, (SPC.CU + (SPC.AG / 10000)) AS CUAG,

    ST.SalesName, ST.PurchOrderFormNum AS CustomerPO,

    xSI.Grm,

    -- Customer Spec Min/Max Fields

    SCS.CUAGMin, SCS.CUAGMax, SCS.DiameterMin, SCS.DiameterMax, SCS.ElongMin, SCS.ElongMax,

    SCS.StrengthMin, SCS.StrengthMax, SCS.OxygenMin, SCS.OxygenMax, SCS.ConductivityMin, SCS.ConductivityMax,

    SCS.GrmMin, SCS.GrmMax, SCS.PopMin AS OxideMin, SCS.PopMax AS OxideMax,

    SCS.ZnMax, SCS.ZnMin, SCS.PbMax, SCS.PbMin, SCS.SnMax, SCS.SnMin, SCS.PMax, SCS.PMin, SCS.MnMax, SCS.MnMin,

    SCS.FeMax, SCS.FeMin, SCS.NiMax, SCS.NiMin, SCS.SiMax, SCS.SiMin, SCS.MgMax, SCS.MgMin, SCS.CrMax, SCS.CrMin,

    SCS.TeMax, SCS.TeMin, SCS.AsMax, SCS.AsMin, SCS.SeMax, SCS.SeMin, SCS.SbMax, SCS.SbMin, SCS.CdMax, SCS.CdMin,

    SCS.BiMax, SCS.BiMin, SCS.AgMax, SCS.AgMin, SCS.CoMax, SCS.CoMin, SCS.AlMax, SCS.AlMin, SCS.SMax, SCS.SMin,

    SCS.BeMax, SCS.BeMin, SCS.HRFMax, SCS.HRFMin,



    I.ItemName

    -- Element values to show

    , 0 Zn, xSA.Pb, 0 Sn, 0 P, 0 Mn, 0 Fe, 0 Ni, 0 Si, 0 Mg, 0 Cr, 0 Te, 0 [As], 0 Se, 0 Sb, 0 Cd, 0 Bi, 0 Ag, 0 Co, 0 Al, 0 S, 0 Be, 0 HRF, 0 SurfaceOxide

-- What to show

, case SCSS.ZnShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as ZnShow

, case SCSS.PbShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as PbShow

, case SCSS.SnShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SnShow

, case SCSS.PShow               when -1561783295 then 1 when -1561783296 then 0 else 0 end as PShow

, case SCSS.MnShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as MnShow

, case SCSS.FeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as FeShow

, case SCSS.NiShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as NiShow

, case SCSS.SiShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SiShow

, case SCSS.MgShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as MgShow

, case SCSS.CrShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as CrShow

, case SCSS.TeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as TeShow

, case SCSS.AsShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as AsShow

, case SCSS.SeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SeShow

, case SCSS.SbShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SbShow

, case SCSS.CdShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as CdShow

, case SCSS.BiShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as BiShow

, case SCSS.AgShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as AgShow

, case SCSS.CoShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as CoShow

, case SCSS.AlShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as AlShow

, case SCSS.SShow               when -1561783295 then 1 when -1561783296 then 0 else 0 end as SShow

, case SCSS.BeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as BeShow

, case SCSS.HRFShow             when -1561783295 then 1 when -1561783296 then 0 else 0 end as HRFShow

, case SCSS.OxideShow           when -1561783295 then 1 when -1561783296 then 0 else 0 end as OxideShow

, case SCSS.CuAgShow            when -1561783295 then 1 when -1561783296 then 0 else 0 end as CuAgShow

, case SCSS.DiameterShow        when -1561783295 then 1 when -1561783296 then 0 else 0 end as DiameterShow

, case SCSS.ElongationShow      when -1561783295 then 1 when -1561783296 then 0 else 0 end as ElongationShow

, case SCSS.StrengthShow        when -1561783295 then 1 when -1561783296 then 0 else 0 end as StrengthShow

, case SCSS.OxygenShow          when -1561783295 then 1 when -1561783296 then 0 else 0 end as OxygenShow

, case SCSS.ConductivityShow    when -1561783295 then 1 when -1561783296 then 0 else 0 end as ConductivityShow

, case SCSS.GRMShow             when -1561783295 then 1 when -1561783296 then 0 else 0 end as GRMShow

FROM InventTrans AS IT

LEFT OUTER JOIN InventTable                             AS I    ON IT.ItemId            = I.ItemId

LEFT OUTER JOIN InventDim                               AS ID   ON IT.INVENTDIMID       = ID.InventDimId

LEFT OUTER JOIN SalesTable                              AS ST   ON IT.TransRefId        = ST.SalesId

LEFT OUTER JOIN SDICustomerSpecs                        AS SCS  ON ST.CustAccount       = SCS.CustomerId AND IT.ItemId = SCS.ItemId

LEFT OUTER JOIN SDIInventory                            AS SI   ON ID.InventBatchId     = SI.BatchId

LEFT OUTER JOIN SDICustomerSpecSheets                   AS SCSS ON SCSS.CustomerName    = ST.SalesName

LEFT OUTER JOIN LAFARGA.LaFargaProd.dbo.BreakdownItem   AS xSI  ON ID.InventBatchId     = xSI.BatchId

LEFT OUTER JOIN SDIProduction                           AS P    ON SI.ProductionId      = P.Id

LEFT OUTER JOIN #Temp                                   AS T    ON P.Id                 = T.PRODUCTIONID

LEFT OUTER JOIN SDIPRODUCTIONCHEMISTRY                  AS SPC  ON T.PRODUCTIONID   = SPC.ProductionId AND SPC.Id = T.Id

LEFT OUTER JOIN LAFARGA.LaFargaProd.dbo.vSpectroAssays  AS xSA  ON xSA.BatchID          = ID.InventBatchId

WHERE IT.PackingSlipId = 'RPS115898'

ORDER BY ID.InventBatchId