sql server – Merge Query in Stored Procedure SQL

I’ll admit I don’t have a lot of DB experience as my last job had a DBA but this place has none. I have an existing MASSIVE stored procedure that I need to modify (I noticed a lot of duplication and cut that out). Right now, it pulls so much data they used a cache table or the page crashes. They just want a smaller amount of data now for a separate page. I’m trying to edit this store procedure and merge in my new query to it. The existing sp

ALTER PROCEDURE [dbo].[PurchasingSupplyChainNeeds](@maxAgeInHours INT = 24)
AS
  BEGIN
      SET NOCOUNT ON;

      DECLARE @sqlStmt NVARCHAR(max)
      DECLARE @myName NVARCHAR(max) = Object_name(@@PROCID)
      DECLARE @needsRefresh INT

      EXEC @needsRefresh=[cache].[NeedsRefresh]
        @myName,
        @maxAgeInHours

      IF @needsRefresh = 1
        BEGIN
            DECLARE @CurrentMonth INT
            DECLARE @DayOfWeek INT
            DECLARE @EndOfWeekDate DATETIME
            DECLARE @StartOfNextWeek DATETIME
            DECLARE @WorkDaysInMonth INT
            DECLARE @RunningDate DATE
            DECLARE @WorkDaysLeftInMonth INT
            DECLARE @DayOffset INT

            SELECT @CurrentMonth = Datepart(month, Getdate())

            SELECT @DayOfWeek = Datepart(weekday, Getdate())

            IF ( @DayOfWeek = 1 )
              BEGIN
                  SET @StartOfNextWeek = Getdate()
              END
            ELSE
              BEGIN
                  SET @StartOfNextWeek = Dateadd(day, 8 - @DayOfWeek, Getdate())
              END

            SELECT @DayOfWeek = Datepart(weekday, Getdate())

            IF ( @DayOfWeek = 7 )
              BEGIN
                  SET @EndOfWeekDate = Getdate()
              END
            ELSE
              BEGIN
                  SET @EndOfWeekDate = Dateadd(day, -@DayOfWeek, Getdate())
              END

            SET @WorkDaysInMonth = 0
            SET @RunningDate = CONVERT(DATE, CONVERT(VARCHAR, @CurrentMonth) + '/1/' + CONVERT(VARCHAR, Datepart(year, Getdate())))

            WHILE ( Datepart(month, @RunningDate) = @CurrentMonth )
              BEGIN
                  SET @DayOfWeek = Datepart(weekday, @RunningDate)

                  IF ( @DayOfWeek != 1
                       AND @DayOfWeek != 7 )
                    BEGIN
                        SET @WorkDaysInMonth = @WorkDaysInMonth + 1
                    END

                  SET @RunningDate = Dateadd(day, 1, @RunningDate)
              END

            SET @WorkDaysLeftInMonth = 0
            SET @DayOffset = 0

            WHILE ( Datepart(month, Dateadd(day, @DayOffset, Getdate())) = @CurrentMonth )
              BEGIN
                  SET @DayOfWeek = Datepart(weekday, Dateadd(day, @DayOffset, Getdate()))

                  IF ( @DayOfWeek != 1
                       AND @DayOfWeek != 7 )
                    BEGIN
                        SET @WorkDaysLeftInMonth = @WorkDaysLeftInMonth + 1
                    END

                  SET @DayOffset = @DayOffset + 1
              END;

            WITH j
                 AS (SELECT Rtrim(Ltrim(imitmidx_sql.item_no))                  AS ItemNo,
                            Rtrim(Ltrim(ISNULL(imitmidx_sql.item_desc_1, '')))  AS ItemDescription1,
                            Rtrim(Ltrim(ISNULL(imitmidx_sql.item_desc_2, '')))  AS ItemDescription2,
                            Rtrim(Ltrim(ISNULL(imitmidx_sql.activity_cd, '-'))) AS ActivityCode,
                            Rtrim(Ltrim(imitmidx_sql.pur_or_mfg))               AS PurchasedOrMfg,
                            Rtrim(Ltrim(ISNULL(imitmidx_sql.uom, '-')))         AS UOM,
                            /* 
                            case statements ensure the price, avg_cost & price
                            are only observed from the item's primary location;
                            not secondary locations such as Amazon warehouses
                            */
                            CASE
                              WHEN iminvloc_sql.loc = imitmidx_sql.loc THEN Rtrim(Ltrim(ISNULL(cicmpy.cmp_name, '')))
                              ELSE ''
                            END                                                 AS VendorName,
                            CASE
                              WHEN iminvloc_sql.loc = imitmidx_sql.loc THEN iminvloc_sql.avg_cost
                              ELSE 0
                            END                                                 AS AverageCost,
                            CASE
                              WHEN iminvloc_sql.loc = imitmidx_sql.loc THEN iminvloc_sql.price
                              ELSE 0
                            END                                                 AS Price,
                            iminvloc_sql.qty_on_hand                            AS QtyOnHand,
                            iminvloc_sql.qty_allocated                          AS QtyAllocated,
                            (SELECT ISNULL(qty_on_hand, 0)
                             FROM   iminvloc_sql WITH (NOLOCK)
                             WHERE  item_no = imitmidx_sql.item_no
                                    AND loc="3IV")                            AS QtyIn3IV,
                            (SELECT ISNULL(qty_on_hand, 0)
                             FROM   iminvloc_sql WITH (NOLOCK)
                             WHERE  item_no = imitmidx_sql.item_no
                                    AND loc="3TI")                            AS QtyIn3TI,
                            (SELECT ISNULL(qty_on_hand, 0)
                             FROM   iminvloc_sql WITH (NOLOCK)
                             WHERE  item_no = imitmidx_sql.item_no
                                    AND loc="3FA")                            AS QtyIn3FA,
                            (SELECT ISNULL(Sum(qty_ordered), 0)
                             FROM   (SELECT y.qty_ordered
                                     FROM   oeordhdr_sql x WITH (NOLOCK)
                                            INNER JOIN oeordlin_sql y WITH (NOLOCK)
                                                    ON x.ord_type = y.ord_type
                                                       AND x.ord_no = y.ord_no
                                     WHERE  x.ord_type="O"
                                            AND x.entered_dt BETWEEN Dateadd(day, 1, Dateadd(week, -52, Cast(@EndOfWeekDate AS DATE))) AND Dateadd(week, -51, Cast(@EndOfWeekDate AS DATE))
                                            AND y.item_no = imitmidx_sql.item_no
                                            AND y.loc = iminvloc_sql.loc
                                            AND x.status != 'L'
                                     UNION ALL
                                     SELECT y.qty_to_ship
                                     FROM   (SELECT DISTINCT ord_type,
                                                             ord_no,
                                                             entered_dt
                                             FROM   oehdrhst_sql WITH (NOLOCK)
                                             WHERE  ord_type="O"
                                                    AND entered_dt BETWEEN Dateadd(day, 1, Dateadd(week, -52, Cast(@EndOfWeekDate AS DATE))) AND Dateadd(week, -51, Cast(@EndOfWeekDate AS DATE))) x
                                            INNER JOIN oelinhst_sql y WITH (NOLOCK)
                                                    ON x.ord_type = y.ord_type
                                                       AND x.ord_no = y.ord_no
                                     WHERE  y.item_no = imitmidx_sql.item_no
                                            AND y.loc = iminvloc_sql.loc) s)    AS SalesWeek1,
                            ---Sales week 2 to 51 here
                            (SELECT ISNULL(Sum(qty_ordered), 0)
                             FROM   (SELECT y.qty_ordered
                                     FROM   oeordhdr_sql x WITH (NOLOCK)
                                            INNER JOIN oeordlin_sql y WITH (NOLOCK)
                                                    ON x.ord_type = y.ord_type
                                                       AND x.ord_no = y.ord_no
                                     WHERE  x.ord_type="O"
                                            AND x.entered_dt BETWEEN Dateadd(day, 1, Dateadd(week, -1, Cast(@EndOfWeekDate AS DATE))) AND @EndOfWeekDate
                                            AND y.item_no = imitmidx_sql.item_no
                                            AND y.loc = iminvloc_sql.loc
                                            AND x.status != 'L'
                                     UNION ALL
                                     SELECT y.qty_to_ship
                                     FROM   (SELECT DISTINCT ord_type,
                                                             ord_no,
                                                             entered_dt
                                             FROM   oehdrhst_sql WITH (NOLOCK)
                                             WHERE  ord_type="O"
                                                    AND entered_dt BETWEEN Dateadd(day, 1, Dateadd(week, -1, Cast(@EndOfWeekDate AS DATE))) AND @EndOfWeekDate) x
                                            INNER JOIN oelinhst_sql y WITH (NOLOCK)
                                                    ON x.ord_type = y.ord_type
                                                       AND x.ord_no = y.ord_no
                                     WHERE  y.item_no = imitmidx_sql.item_no
                                            AND y.loc = iminvloc_sql.loc) s)    AS SalesWeek52,
                            (SELECT ISNULL(Sum(qty_ordered), 0)
                             FROM   (SELECT y.qty_ordered
                                     FROM   oeordhdr_sql x WITH (NOLOCK)
                                            INNER JOIN oeordlin_sql y WITH (NOLOCK)
                                                    ON x.ord_type = y.ord_type
                                                       AND x.ord_no = y.ord_no
                                     WHERE  x.ord_type="O"
                                            AND x.entered_dt BETWEEN Cast('1/1/' + CONVERT(VARCHAR, Datepart(year, Dateadd(year, -2, Getdate()))) AS DATE) AND Cast('1/1/' + CONVERT(VARCHAR, Datepart(year, Dateadd(year, -1, Getdate()))) AS DATE)
                                            AND y.item_no = imitmidx_sql.item_no
                                            AND y.loc = iminvloc_sql.loc
                                            AND x.status != 'L'
                                     UNION ALL
                                     SELECT y.qty_to_ship
                                     FROM   (SELECT DISTINCT ord_type,
                                                             ord_no,
                                                             entered_dt
                                             FROM   oehdrhst_sql WITH (NOLOCK)
                                             WHERE  ord_type="O"
                                                    AND entered_dt BETWEEN Cast('1/1/' + CONVERT(VARCHAR, Datepart(year, Dateadd(year, -2, Getdate()))) AS DATE) AND Cast('1/1/' + CONVERT(VARCHAR, Datepart(year, Dateadd(year, -1, Getdate()))) AS DATE)) x
                                            INNER JOIN oelinhst_sql y WITH (NOLOCK)
                                                    ON x.ord_type = y.ord_type
                                                       AND x.ord_no = y.ord_no
                                     WHERE  y.item_no = imitmidx_sql.item_no
                                            AND y.loc = iminvloc_sql.loc) s)    AS SalesYear1,
                            (SELECT ISNULL(Sum(qty_ordered), 0)
                             FROM   (SELECT y.qty_ordered
                                     FROM   oeordhdr_sql x WITH (NOLOCK)
                                            INNER JOIN oeordlin_sql y WITH (NOLOCK)
                                                    ON x.ord_type = y.ord_type
                                                       AND x.ord_no = y.ord_no
                                     WHERE  x.ord_type="O"
                                            AND x.entered_dt BETWEEN Cast('1/1/' + CONVERT(VARCHAR, Datepart(year, Dateadd(year, -1, Getdate()))) AS DATE) AND Cast('1/1/' + CONVERT(VARCHAR, Datepart(year, Getdate())) AS DATE)
                                            AND y.item_no = imitmidx_sql.item_no
                                            AND y.loc = iminvloc_sql.loc
                                            AND x.status != 'L'
                                     UNION ALL
                                     SELECT y.qty_to_ship
                                     FROM   (SELECT DISTINCT ord_type,
                                                             ord_no,
                                                             entered_dt
                                             FROM   oehdrhst_sql WITH (NOLOCK)
                                             WHERE  ord_type="O"
                                                    AND entered_dt BETWEEN Cast('1/1/' + CONVERT(VARCHAR, Datepart(year, Dateadd(year, -1, Getdate()))) AS DATE) AND Cast('1/1/' + CONVERT(VARCHAR, Datepart(year, Getdate())) AS DATE)) x
                                            INNER JOIN oelinhst_sql y WITH (NOLOCK)
                                                    ON x.ord_type = y.ord_type
                                                       AND x.ord_no = y.ord_no
                                     WHERE  y.item_no = imitmidx_sql.item_no
                                            AND y.loc = iminvloc_sql.loc) s)    AS SalesYear2,
                            (SELECT ISNULL(Sum(qty_ordered), 0)
                             FROM   (SELECT y.qty_ordered
                                     FROM   oeordhdr_sql x WITH (NOLOCK)
                                            INNER JOIN oeordlin_sql y WITH (NOLOCK)
                                                    ON x.ord_type = y.ord_type
                                                       AND x.ord_no = y.ord_no
                                     WHERE  x.ord_type="O"
                                            AND x.entered_dt BETWEEN Cast('1/1/' + CONVERT(VARCHAR, Datepart(year, Getdate())) AS DATE) AND Cast(Getdate() AS DATE)
                                            AND y.item_no = imitmidx_sql.item_no
                                            AND y.loc = iminvloc_sql.loc
                                            AND x.status != 'L'
                                     UNION ALL
                                     SELECT y.qty_to_ship
                                     FROM   (SELECT DISTINCT ord_type,
                                                             ord_no,
                                                             entered_dt
                                             FROM   oehdrhst_sql WITH (NOLOCK)
                                             WHERE  ord_type="O"
                                                    AND entered_dt BETWEEN Cast('1/1/' + CONVERT(VARCHAR, Datepart(year, Getdate())) AS DATE) AND Cast(Getdate() AS DATE)) x
                                            INNER JOIN oelinhst_sql y WITH (NOLOCK)
                                                    ON x.ord_type = y.ord_type
                                                       AND x.ord_no = y.ord_no
                                     WHERE  y.item_no = imitmidx_sql.item_no
                                            AND y.loc = iminvloc_sql.loc) s)    AS SalesYTD,
                            CASE
                              WHEN Datediff(week, imitmidx_sql.activity_dt, Getdate()) > 52 THEN 52
                              ELSE Datediff(week, imitmidx_sql.activity_dt, Getdate())
                            END                                                 AS ActiveWeeks,
                            (SELECT ( ISNULL(ForecastQty, 0) / @WorkDaysInMonth ) * @WorkDaysLeftInMonth
                             FROM   HP_PurchasingForecast
                             WHERE  ItemNo = imitmidx_sql.item_no
                                    AND [Month] = Datepart(month, Getdate())
                                    AND [Year] = Datepart(year, Getdate())
                                    AND Active = 1)                             AS RemainingForecastQty,
                            (SELECT ISNULL(ForecastQty, 0)
                             FROM   HP_PurchasingForecast
                             WHERE  ItemNo = imitmidx_sql.item_no
                                    AND [Month] = Datepart(month, Getdate())
                                    AND [Year] = Datepart(year, Getdate())
                                    AND Active = 1)                             AS ForecastQty1,
                            ---ForecastQty 2 to 11
                            (SELECT ISNULL(ForecastQty, 0)
                             FROM   HP_PurchasingForecast
                             WHERE  ItemNo = imitmidx_sql.item_no
                                    AND [Month] = Datepart(month, Dateadd(month, 11, Getdate()))
                                    AND [Year] = Datepart(year, Dateadd(month, 11, Getdate()))
                                    AND Active = 1)                             AS ForecastQty12,
                            ( (SELECT ISNULL(Sum(CASE
                                                   WHEN receipt_dt IS NULL THEN qty_ordered
                                                   ELSE qty_remaining
                                                 END), 0)
                               FROM   poordlin_sql WITH (NOLOCK)
                               WHERE  ord_status IN ( 'P', 'R' )
                                      AND item_no = imitmidx_sql.item_no
                                      AND promise_dt IS NOT NULL
                                      AND promise_dt <= CONVERT(DATE, Dateadd(day, 6, @StartOfNextWeek))
                                      AND
                                      (
                                        receipt_dt IS NULL
                                         OR ISNULL(qty_remaining, 0) > 0
                                       )
                              )
                              + (SELECT ISNULL(Sum(y.quantity), 0)
                                 FROM   imtrnhdr_sql x WITH (NOLOCK)
                                        INNER JOIN imtrndtl_sql y WITH (NOLOCK)
                                                ON x.transit_no = y.transit_no
                                 WHERE  x.status IN ( 'A', 'S' )
                                        AND y.item_no = imitmidx_sql.item_no
                                        AND x.to_loc = iminvloc_sql.loc
                                        AND x.due_date <= CONVERT(DATE, Dateadd(day, 6, @StartOfNextWeek))
                                        AND NOT
                                            (
                                              x.from_loc="3IV"
                                              AND x.temp_loc="3TI"
                                              AND x.to_loc = iminvloc_sql.loc
                                             )
                                ) )                                             AS POQty1,
                          ---POQty 2 to 52
                            ( (SELECT ISNULL(Sum(CASE
                                                   WHEN receipt_dt IS NULL THEN qty_ordered
                                                   ELSE qty_remaining
                                                 END), 0)
                               FROM   poordlin_sql WITH (NOLOCK)
                               WHERE  ord_status IN ( 'P', 'R' )
                                      AND item_no = imitmidx_sql.item_no
                                      AND promise_dt IS NOT NULL
                                      AND promise_dt BETWEEN CONVERT(DATE, Dateadd(day, 357, @StartOfNextWeek)) AND CONVERT(DATE, Dateadd(day, 363, @StartOfNextWeek))
                                      AND
                                      (
                                        receipt_dt IS NULL
                                         OR ISNULL(qty_remaining, 0) > 0
                                       )
                              )
                              + (SELECT ISNULL(Sum(y.quantity), 0)
                                 FROM   imtrnhdr_sql x WITH (NOLOCK)
                                        INNER JOIN imtrndtl_sql y WITH (NOLOCK)
                                                ON x.transit_no = y.transit_no
                                 WHERE  x.status IN ( 'A', 'S' )
                                        AND y.item_no = imitmidx_sql.item_no
                                        AND x.to_loc = iminvloc_sql.loc
                                        AND x.due_date BETWEEN CONVERT(DATE, Dateadd(day, 357, @StartOfNextWeek)) AND CONVERT(DATE, Dateadd(day, 363, @StartOfNextWeek))
                                        AND NOT
                                            (
                                              x.from_loc="3IV"
                                              AND x.temp_loc="3TI"
                                              AND x.to_loc = iminvloc_sql.loc
                                             )
                                ) )                                             AS POQty52
                     FROM   imitmidx_sql imitmidx_sql WITH (NOLOCK)
                            LEFT OUTER JOIN iminvloc_sql iminvloc_sql WITH (NOLOCK)
                                         ON imitmidx_sql.item_no = iminvloc_sql.item_no
                                            AND
                                            (
                                              imitmidx_sql.loc = iminvloc_sql.loc /* Primary location for the item */
                                               OR iminvloc_sql.loc="3FA"/*Amazon Fullfillment Center*/
                                             )
                            LEFT OUTER JOIN cicmpy cicmpy
                                         ON iminvloc_sql.vend_no = cicmpy.cmp_code
                                            AND cicmpy.cmp_type="S"),
                 i
                 AS (SELECT [ItemNo],
                            [ItemDescription1],
                            [ItemDescription2],
                            [ActivityCode],
                            [PurchasedOrMfg],
                            [UOM],
                            Max([VendorName])           [VendorName],
                            Max([AverageCost])          [AverageCost],
                            Max([Price])                [Price],
                            Sum([QtyOnHand])            [QtyOnHand],
                            Sum([QtyAllocated])         [QtyAllocated],
                            Max([QtyIn3IV])             [QtyIn3IV],
                            Max([QtyIn3TI])             [QtyIn3TI],
                            Max([QtyIn3FA])             [QtyIn3FA],
                            Sum([SalesWeek1])           [SalesWeek1],
                         ---SalesWeek2 to 51
                     
                            Sum([SalesWeek52])          [SalesWeek52],
                            Sum([SalesYear1])           [SalesYear1],
                            Sum([SalesYear2])           [SalesYear2],
                            Sum([SalesYTD])             [SalesYTD],
                            Avg([ActiveWeeks])          [ActiveWeeks],
                            Avg([RemainingForecastQty]) [RemainingForecastQty],
                            Avg([ForecastQty1])         [ForecastQty1],
                          ---ForecastQty2 to 11
                            Avg([ForecastQty12])        [ForecastQty12],
                            Avg([POQty1])               [POQty1],
                           ---POQty2 to 51
                            Avg([POQty52])              [POQty52]
                     FROM   j
                     WHERE [PurchasedOrMfg] = 'M'
                     GROUP  BY [ItemNo],
                               [ItemDescription1],
                               [ItemDescription2],
                               [ActivityCode],
                               [PurchasedOrMfg],
                               [UOM])


            SELECT i.*,
                   CASE i.ActiveWeeks
                     WHEN 0 THEN 0
                     ELSE ( i.SalesWeek52 + i.SalesWeek51 + i.SalesWeek50 + i.SalesWeek49 + i.SalesWeek48 + i.SalesWeek47 ) /
                          (
                            CASE
                              WHEN i.ActiveWeeks < 6 THEN i.ActiveWeeks
                              ELSE 6
                            END
                          )
                   END                                                                                                                                                                                                                                                                                                                                                               AS Last6WeeksAvg,
                   CASE i.ActiveWeeks
                     WHEN 0 THEN 0
                     ELSE ( i.SalesWeek52 + i.SalesWeek51 + i.SalesWeek50 + i.SalesWeek49 + i.SalesWeek48 + i.SalesWeek47 + i.SalesWeek46 + i.SalesWeek45 + i.SalesWeek44 + i.SalesWeek43 + i.SalesWeek42 + i.SalesWeek41 ) /
                          (
                            CASE
                              WHEN i.ActiveWeeks < 12 THEN i.ActiveWeeks
                              ELSE 12
                            END
                          )
                   END                                                                                                                                                                                                                                                                                                                                                               AS Last12WeeksAvg,
                   CASE i.ActiveWeeks
                     WHEN 0 THEN 0
                     ELSE ( i.SalesWeek52 + i.SalesWeek51 + i.SalesWeek50 + i.SalesWeek49 + i.SalesWeek48 + i.SalesWeek47 + i.SalesWeek46 + i.SalesWeek45 + i.SalesWeek44 + i.SalesWeek43 + i.SalesWeek42 + i.SalesWeek41 + i.SalesWeek40 + i.SalesWeek39 + i.SalesWeek38 + i.SalesWeek37 + i.SalesWeek36 + i.SalesWeek35 + i.SalesWeek34 + i.SalesWeek33 + i.SalesWeek32 + i.SalesWeek31 + i.SalesWeek30 + i.SalesWeek29 + i.SalesWeek28 + i.SalesWeek27 + i.SalesWeek26 + i.SalesWeek25 + i.SalesWeek24 + i.SalesWeek23 + i.SalesWeek22 + i.SalesWeek21 + i.SalesWeek20 + i.SalesWeek19 + i.SalesWeek18 + i.SalesWeek17 + i.SalesWeek16 + i.SalesWeek15 + i.SalesWeek14 + i.SalesWeek13 + i.SalesWeek12 + i.SalesWeek11 + i.SalesWeek10 + i.SalesWeek9 + i.SalesWeek8 + i.SalesWeek7 + i.SalesWeek6 + i.SalesWeek5 + i.SalesWeek4 + i.SalesWeek3 + i.SalesWeek2 + i.SalesWeek1 ) / i.ActiveWeeks
                   END                                                                                                                                                                                                                                                                                                                                                               AS Last52WeeksAvg,
                   ( ISNULL(i.RemainingForecastQty, 0) + ISNULL(i.ForecastQty2, 0) + ISNULL(i.ForecastQty3, 0) + ISNULL(i.ForecastQty4, 0) + ISNULL(i.ForecastQty5, 0) + ISNULL(i.ForecastQty6, 0) + ISNULL(i.ForecastQty7, 0) + ISNULL(i.ForecastQty8, 0) + ISNULL(i.ForecastQty9, 0) + ISNULL(i.ForecastQty10, 0) + ISNULL(i.ForecastQty11, 0) + ISNULL(i.ForecastQty12, 0) ) / 52 AS AvgWeeklyForecast,
                   ISNULL(i.QtyIn3FA, 0) + ISNULL(i.QtyOnHand, 0) + ISNULL(i.QtyIn3TI, 0) - ISNULL(i.QtyAllocated, 0)                                                                                                                                                                                                                                                                AS QtyAvailable
            INTO   #outputToCache
            FROM   i

            SET @sqlStmt="select * INTO [cache].[" + @myName + '] from #outputToCache'

            EXEC sp_executesql
              @sqlStmt
        END

      SET @sqlStmt="select * from [cache].[" + @myName + ']'

      EXEC sp_executesql
        @sqlStmt
  END 

My Query

SELECT 
tb_1.SubPart AS 'Sub Part',
SUM(tb_1.FinalItemSubPartQuantity) 'Sub Part Quantity Needed',
SUM(tb_1.FinalItemSubPartQuantity * tb_2.SalesWeek1) 'Total Sales Week 1',
--- Total Sales Week 2 to 51 here
SUM(tb_1.FinalItemSubPartQuantity * tb_2.SalesWeek52) 'Total Sales Week 52'
FROM [009Reports].[dbo].[ANC Parts] tb_1
JOIN [555].[cache].[PurchasingSupplyChainNeeds] tb_2 ON tb_1.FinalPartNo = tb_2.ItemNo
GROUP BY tb_1.SubPart

Inserting my query at the part that has:

AS (SELECT [ItemNo],
                            [ItemDescription1],
                            [ItemDescription2],

Error “Msg 208, Level 16, State 1, Line 3168 Invalid object name ‘i’.” My query has 2 different tables in 2 different databases.

Leave a Comment