Stored Procedure error when changing compatibility mode to 90 (2005)

  • I am testing in anticipation of changing our sql compatibility mode from 80 to 90. I have a stored procedure that generates the following error when trying to add it to the database in 90 compatibility mode but work under 80 compatibility mode. any help would be greatly appreciated.

    Thanks

    JW

    error message:

    Msg 164, Level 15, State 1, Procedure SP_X_Performance_Data, Line 437

    Each GROUP BY expression must contain at least one column that is not an outer reference.

  • i encountered that same error way back when i upgraded.

    the error message is pretty clear.

    if you post the statement we can show you a lot better, but basically , as i remember it, the current group by

    does not feature any columns form one of the tables involved in the join; it's been a while, so i'd love to see the details.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Here's the code for the Stored Procedure.

    Create procedure [dbo].[SP_X_Performance_Data] @BU char(3), @PLT char(2), @StartDate char(8), @EndDate char(8)

    AS

    declare @StartYR Bigint

    declare @StartWK Bigint

    declare @EndYR Bigint

    declare @EndWK Bigint

    declare @EndRPTWK Bigint

    set @StartYR = (select [Year] from X_Week_Control_Table

    where @StartDate >= Week_StartDT

    and @StartDate <= week_EndDT)

    set @StartWK = (select WeekNumber from X_Week_Control_Table

    where @StartYR = [Year] and @StartDate >= Week_StartDT

    and @StartDate <= week_EndDT)

    set @EndYR = (select [Year] from X_Week_Control_Table

    where @EndDate >= Week_StartDT

    and @EndDate <= week_EndDT)

    set @EndWK = (select WeekNumber from X_Week_Control_Table

    where @EndYR = [Year] and @EndDate >= Week_StartDT

    and @EndDate <= week_EndDT)

    set @EndRPTWK = (select WeekNumber from X_Week_Control_Table

    where @EndYR = [Year] and @EndDate >= Week_StartDT

    and @EndDate <= week_EndDT)

    ----------------------------------------------------------------------------------

    -- drop temp table if it exists

    if exists (select * from dbo.sysobjects where id = object_id(N'[tempdb..##Perf_Report_Weeks_table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [tempdb..##Perf_Report_Weeks_table]

    -- create temp table to hold Produced Quality Totals

    create table [tempdb..##Perf_Report_Weeks_table]

    (

    [Year] [decimal](4,0) NOT NULL,

    [WeekNumber] [decimal](2,0) NOT NULL,

    [Week_StartDT] [Datetime] NOT NULL,

    [Week_EndDT] [Datetime] NOT NULL)

    -- LOAD Perf_Report_Weeks_table

    BEGIN TRANSACTION

    INSERT INTO [tempdb..##Perf_Report_Weeks_table]

    ([Year], WeekNumber, Week_StartDT, Week_EndDT)

    select [Year], [WeekNumber], [Week_StartDT], [Week_EndDT] from X_Week_Control_Table as a

    where (a.[Week_StartDT] <= @StartDate and a.[Week_EndDT] >= @StartDate)

    or (a.[Week_StartDT] >= @StartDate and a.[Week_EndDT] <= @EndDate)

    or (a.[Week_StartDT] <= @EndDate and a.[Week_EndDT] >= @EndDate)

    Commit

    ---------------------

    -- New

    -- Get the last date of the reporting period

    --

    declare @End_RPT_Date [Datetime]

    SET @End_RPT_Date = (SELECT max(Week_EndDT) FROM [tempdb..##Perf_Report_Weeks_table])

    ---------------------

    -- drop temp table if it exists

    if exists (select * from dbo.sysobjects where id = object_id(N'[tempdb..##Performance_Data_table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [tempdb..##Performance_Data_table]

    -- create temp table to hold Produced Quality Totals

    create table [tempdb..##Performance_Data_table]

    (BU char(3),

    PLT char(2),

    KST char(5),

    BLN VARCHAR(10),

    BLA VARcHAR(2),

    DAT DECIMAL (11,0),

    KTR VARCHAR(20),

    MGL DECIMAL(11,5),

    MGS DECIMAL(11,5),

    MGD DECIMAL(11,5),

    UMACHINE Char (10),

    ANR CHAR(20),

    SEL1 VARCHAR(5),

    SEL3 VARCHAR(5),

    PR1 DECIMAL(11,5),

    PR4 DECIMAL(11,5),

    DUR DECIMAL(11,5),

    SDUR DECIMAL(11,5),

    START_DATE DATETIME,

    END_DATE DATETIME,

    SDATFDATETIME,

    EDATF DATETIME,

    [Est Time (Min)] decimal(11,5),

    Act_Time_MIN decimal(11,5),

    Adj_Act_Time_MIN decimal(11,5),

    Produced decimal (11,5),

    Year_Number Bigint,

    WK_Number BigInt,

    WK_StartDateDATETIME)

    -- LOAD Performance_Data_table

    BEGIN TRANSACTION

    INSERT INTO [tempdb..##Performance_Data_table]

    (BU, PLT, KST, BLN, BLA, DAT, KTR, MGL, MGS, MGD,UMACHINE,

    ANR, SEL1, SEL3, PR1, PR4, DUR, SDUR, START_DATE, END_DATE,

    SDATF, EDATF, [Est Time (Min)], Act_Time_MIN,

    Adj_Act_Time_MIN, Produced, Year_Number, WK_Number, WK_StartDate)

    -------------------------------

    -- declare @StartDate varchar (8)

    -- declare @EndDate varchar (8)

    -- set @StartDate = '20130101'

    -- set @endDate = '20131231'

    select top 100 Percent M1BNR, M1PLT, M1KST, M1BLN, M1BLA, M1DAT, M1KTR, M1MGL, M1MGS,

    M1MGD, M1MNM, M1ITEM, M1SEL1, M1SEL3, M1PR1, M1PR4, M1DUR, M1SDUR,

    start_date = case when M1SDATF < @StartDate then @StartDate

    else M1SDATF end,

    end_date = case when M1EDATF > dateadd(Day,1,Cast(@EndDate as datetime)) then dateadd(Day,1,Cast(@EndDate as datetime)) -- @EndDate

    else M1EDATF end,

    M1SDATF, M1EDATF,

    [Est Time (Min)] = datediff (Mi, case when M1SDATF < @StartDate then @StartDate

    else M1SDATF end, case when M1EDATF > dateadd(Day,1,Cast(@EndDate as datetime)) then dateadd(Day,1,Cast(@EndDate as datetime)) --@EndDate

    else M1EDATF end),0,0,0, [Year],WeekNumber, Week_StartDT

    from MCPMAS00 inner join X_Week_Control_Table on M1SDATF >= Week_StartDT and M1SDATF <= week_EndDT

    where M1SDATF < = @EndDate and M1EDATF >= @StartDate and M1ANR LIKE '%Activity'

    and M1BNR = @BU and M1PLT = @PLT

    union all

    select M1BNR, M1PLT, M1KST, M1BLN, M1BLA, M1DAT, M1KTR, M1MGL, M1MGS,

    M1MGD, M1MNM, M1ITEM, M1SEL1, M1SEL3, M1PR1, M1PR4, M1DUR, M1SDUR ,

    start_date = case when M1SDATF < @StartDate then @StartDate

    else M1SDATF end,

    end_date = case when M1EDATF > dateadd(Day,1,Cast(@EndDate as datetime)) then dateadd(Day,1,Cast(@EndDate as datetime)) -- @EndDate

    else M1EDATF end,

    M1SDATF, M1EDATF,

    [Est Time (Min)] = datediff (Mi, case when M1SDATF < @StartDate then @StartDate

    else M1SDATF end, case when M1EDATF > dateadd(Day,1,Cast(@EndDate as datetime)) then dateadd(Day,1,Cast(@EndDate as datetime)) --@EndDate

    else M1EDATF end),0,0,0, [Year], WeekNumber, Week_StartDT

    from MCPMAS90 inner join X_Week_Control_Table on M1SDATF >= Week_StartDT and M1SDATF <= week_EndDT

    where M1SDATF < = @EndDate and M1EDATF >= @StartDate and M1ANR LIKE '%Activity'

    and M1BNR = @BU and M1PLT = @PLT

    -- ORDER BY M1BNR, M1PLT, M1BLN

    commit

    -- GO

    --------------------------------------------------------------------------

    -- -- Create Detail Performance Data file

    -- drop temp table if it exists

    if exists (select * from dbo.sysobjects where id = object_id(N'[tempdb..##Performance_Detail_Data_table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [tempdb..##Performance_Detail_Data_table]

    -- create temp table to hold Produced Detail Data Totals

    create table [tempdb..##Performance_Detail_Data_table]

    (BU char(3),

    PLT char(2),

    KST char(5),

    BLN VARCHAR(10),

    BLA VARcHAR(2),

    DAT DECIMAL (11,0),

    KTR VARCHAR(20),

    MGL DECIMAL(11,5),

    MGS DECIMAL(11,5),

    MGD DECIMAL(11,5),

    UMACHINE Char (10),

    ANR CHAR(20),

    SEL1 VARCHAR(5),

    SEL3 VARCHAR(5),

    PR1 DECIMAL(11,5),

    PR4 DECIMAL(11,5),

    DUR DECIMAL(11,5),

    SDUR DECIMAL(11,5),

    START_DATE DATETIME,

    END_DATE DATETIME,

    SDATFDATETIME,

    EDATF DATETIME,

    [Est Time (Min)] decimal(11,5),

    Est_Time_Hrs decimal(11,5),

    Act_Time_MIN decimal(11,5),

    Adj_Act_Time_MIN decimal(11,5),

    Produced decimal (11,5),

    Year_Number BigInt,

    WK_Number BigInt,

    WK_StartDateDATETIME,

    RecCount BigInt)

    -- LOAD Performance_Detail_Data_table

    BEGIN TRANSACTION

    declare @BU_VAR char(3)

    declare @PLT_VAR char(2)

    declare @KST_VAR char(5)

    declare @BLN_VAR VARCHAR(10)

    declare @BLA_VAR VARcHAR(2)

    declare @DAT_VAR DECIMAL (11,0)

    declare @KTR_VAR VARCHAR(20)

    declare @MGL_VAR DECIMAL(11,5)

    declare @MGS_VAR DECIMAL(11,5)

    declare @MGD_VAR DECIMAL(11,5)

    declare @UMACHINE_VAR Char (10)

    declare @ANR_VAR CHAR(20)

    declare @SEL1_VAR VARCHAR(5)

    declare @SEL3_VAR VARCHAR(5)

    declare @PR1_VAR DECIMAL(11,5)

    declare @PR4_VAR DECIMAL(11,5)

    declare @DUR_VAR DECIMAL(11,5)

    declare @SDUR_VAR DECIMAL(11,5)

    declare @START_DATE_VAR DATETIME

    declare @END_DATE_VAR DATETIME

    declare @SDATF_VAR DATETIME

    declare @EDATF_VAR DATETIME

    declare @Est_Time_Min_VAR decimal(11,5)

    declare @Act_Time_MIN_VAR decimal(11,5)

    declare @Adj_Act_Time_MIN_VAR decimal(11,5)

    declare @Produced_VAR decimal (11,5)

    declare @Year_Number_VAR BigInt

    declare @WK_Number_VAR BigInt

    declare @WK_StartDate DATETIME

    --

    -- NEW

    declare @EndRecWK BigInt

    --

    declare perf_data cursor

    for select BU, PLT, KST, BLN, BLA, DAT, KTR, MGL, MGS, MGD,UMACHINE,

    ANR, SEL1, SEL3, PR1, PR4, DUR, SDUR, START_DATE, END_DATE,

    SDATF, EDATF, [Est Time (Min)], Act_Time_MIN,

    Adj_Act_Time_MIN, Produced, Year_number, WK_Number, WK_StartDate

    from [tempdb..##Performance_Data_table]

    order by START_DATE, END_DATE

    open perf_data

    FETCH NEXT from perf_data

    into @BU_VAR, @PLT_VAR, @KST_VAR, @BLN_VAR, @BLA_VAR, @DAT_VAR, @KTR_VAR,

    @MGL_VAR, @MGS_VAR, @MGD_VAR, @UMACHINE_VAR, @ANR_VAR,

    @SEL1_VAR, @SEL3_VAR, @PR1_VAR, @PR4_VAR, @DUR_VAR, @SDUR_VAR,

    @START_DATE_VAR, @END_DATE_VAR, @SDATF_VAR, @EDATF_VAR,

    @Est_Time_Min_VAR, @Act_Time_MIN_VAR, @Adj_Act_Time_MIN_VAR,

    @Produced_VAR, @Year_Number_VAR, @WK_Number_VAR, @WK_StartDate

    WHILE @@FETCH_STATUS = 0

    begin

    set @StartYR = convert(char(4),datepart(year,@START_DATE_VAR))

    set @StartWK = (select WeekNumber from X_Week_Control_Table

    where @StartYR = [Year] and @START_DATE_VAR >= Week_StartDT

    and @START_DATE_VAR <= week_EndDT)

    set @EndYR = (select convert(char(4),datepart(year,@END_DATE_VAR)))

    -- NEW

    set @EndRecWK = (select WeekNumber from X_Week_Control_Table

    where @StartYR = [Year] and @END_DATE_VAR >= Week_StartDT

    and @END_DATE_VAR <= week_EndDT)

    --set @EndWK = (select WeekNumber from X_Week_Control_Table

    -- where @EndYR = [Year] and @END_DATE_VAR >= Week_StartDT

    -- and @END_DATE_VAR <= week_EndDT)

    ----------

    -- Check for different years

    if (@EndYR > @StartYR and @EndWK < @StartWK) set @EndWK = (@EndWK + 52)

    -- ip records

    -- select @BLN_VAR, @UMACHINE_VAR, @StartWK,@EndWK

    while not (@StartWK > @EndWK or @StartWK > @EndRecWK)

    begin

    -- op records

    INSERT INTO [tempdb..##Performance_Detail_Data_table]

    select @BU_VAR, @PLT_VAR, @KST_VAR, @BLN_VAR, @BLA_VAR, @DAT_VAR, @KTR_VAR,

    @MGL_VAR, @MGS_VAR, @MGD_VAR, @UMACHINE_VAR, @ANR_VAR,

    @SEL1_VAR, @SEL3_VAR, @PR1_VAR, @PR4_VAR, @DUR_VAR, @SDUR_VAR, @START_DATE_VAR,

    @END_DATE_VAR, @SDATF_VAR, @EDATF_VAR, @Est_Time_Min_VAR, 0,

    @Act_Time_MIN_VAR, @Adj_Act_Time_MIN_VAR, @Produced_VAR,

    @StartYR, @StartWK, @WK_StartDate, 0

    set @StartWK = (@StartWK + 1)

    -- Check for different years

    if (@EndYR > @StartYR and @StartWK = '53')

    begin

    set @StartWK = 1

    set @EndWK = (@EndWK - 52)

    set @StartYR = (@StartYR + 1)

    end

    end

    FETCH NEXT from perf_data

    into @BU_VAR, @PLT_VAR, @KST_VAR, @BLN_VAR, @BLA_VAR, @DAT_VAR, @KTR_VAR,

    @MGL_VAR, @MGS_VAR, @MGD_VAR, @UMACHINE_VAR, @ANR_VAR,

    @SEL1_VAR, @SEL3_VAR, @PR1_VAR, @PR4_VAR, @DUR_VAR, @SDUR_VAR, @START_DATE_VAR,

    @END_DATE_VAR, @SDATF_VAR, @EDATF_VAR, @Est_Time_Min_VAR,

    @Act_Time_MIN_VAR, @Adj_Act_Time_MIN_VAR, @Produced_VAR,

    @Year_Number_VAR, @WK_Number_VAR, @WK_StartDate

    end

    commit

    CLOSE perf_data

    DEALLOCATE perf_data

    --------------------------------------------------------------------------

    -- modified 9/16/14

    -- Update Start/End Dates to be correct for Weekly Records

    Update [tempdb..##Performance_Detail_Data_table]

    set START_DATE = case when Week_StartDT > START_DATE then Week_StartDT else START_DATE end,

    END_DATE = case when Week_EndDT < END_DATE then Week_EndDT else END_DATE end,

    WK_StartDate = case when Week_StartDT > WK_StartDate then Week_StartDT else WK_StartDate end

    from X_Week_Control_Table

    where Year_Number = [Year] and WK_Number = WeekNumber

    --------------------------------------------------------------------------

    -- Fix est_hrs for jobs that span multiple weeks (7/22/14)

    Update [tempdb..##Performance_Detail_Data_table]

    set [Est Time (Min)] = datediff (Mi, START_DATE, END_DATE)

    --------------------------------------------------------------------------

    -- -- Create Actual Data file

    EXEC SP_X_Create_Actual_Times_File @BU, @PLT, @StartDate, @EndDate

    --------------------------------------------------------------------------

    -- update performance Detail data with actual data

    --

    -- update Act_Time_MIN field

    update [tempdb..##Performance_Data_table] set Act_Time_MIN = ( select SUM([Actual Time (Min)])

    from [tempdb..##Actual_Times_table] as a

    where a.BU = [tempdb..##Performance_Data_table].BU and a.PLT = [tempdb..##Performance_Data_table].PLT

    and a.KST = [tempdb..##Performance_Data_table].KST and a.UMACHINE = [tempdb..##Performance_Data_table].UMACHINE

    and a.BLN = [tempdb..##Performance_Data_table].BLN)

    -- update Adj_Act_Time_MIN field

    update [tempdb..##Performance_Data_table] set Adj_Act_Time_MIN = ( select SUM([Adj_Actual Time (Min)])

    from [tempdb..##Actual_Times_table] as a

    where a.BU = [tempdb..##Performance_Data_table].BU and a.PLT = [tempdb..##Performance_Data_table].PLT

    and a.KST = [tempdb..##Performance_Data_table].KST and a.UMACHINE = [tempdb..##Performance_Data_table].UMACHINE

    and a.BLN = [tempdb..##Performance_Data_table].BLN)

    ----------------------------------------------------------------------------

    Print 'we got here'

    ----------------------------------------------------------------------------

    --- 7/22/14

    -- Update Performance_Detail_Data_table to get correct estimated time in Hours

    update [tempdb..##Performance_Detail_Data_table]

    set Est_Time_Hrs = (select [dbo].[Capacity_Calc_SB_TS] (BU,PLT,SEL3,KST, UMACHINE,START_DATE,END_DATE))

    from [tempdb..##Performance_Detail_Data_table] where bln > '0'

    -- ??????????????????????????????????

    ----------------------------------------------------------------------------

    -- Insert zero records ino Performance_Detail_Data_table for machines

    --without any activity

    --------------------------------------------temp

    insert into [tempdb..##Performance_Detail_Data_table]

    select @BU, @PLT, MCSTC, 0, 'IV', CAST(convert(varchar,Week_StartDT,112) as Bigint), '', 0,0,0, MANR, '','',0,0,0,0,0,

    Week_StartDT, Week_EndDT, Week_StartDT, Week_EndDT,0,0,0,0,0, [Year], WeekNumber, Week_StartDT,0

    from TXBYAT00, [tempdb..##Perf_Report_Weeks_table]

    where mbnr = @BU and mplt = @PLT and not exists (select * from [tempdb..##Performance_Detail_Data_table]

    where MANR = UMACHINE and [Year] = Year_Number and WK_Number = WeekNumber)

    ----------------------------------------------------------------------------------------------------

    ----------------------------------------------------------------------------------------------------

    ----------------------------------------------------------------------------------------------------

    --- create summary detail data file (summarize split jobs into one record)

    -- -- Create Summary Detail Performance Data file

    -- drop temp table if it exists

    if exists (select * from dbo.sysobjects where id = object_id(N'[tempdb..##Performance_SUM_Detail_Data_table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [tempdb..##Performance_SUM_Detail_Data_table]

    create table [tempdb..##Performance_SUM_Detail_Data_table]

    (BU char(3),

    PLT char(2),

    KST char(5),

    BLN VARCHAR(10),

    BLA VARcHAR(2),

    DAT DECIMAL (11,0),

    KTR VARCHAR(20),

    MGL DECIMAL(11,5),

    MGS DECIMAL(11,5),

    MGD DECIMAL(11,5),

    UMACHINE Char (10),

    ANR CHAR(20),

    SEL1 VARCHAR(5),

    SEL3 VARCHAR(5),

    PR1 DECIMAL(11,5),

    PR4 DECIMAL(11,5),

    DUR DECIMAL(11,5),

    SDUR DECIMAL(11,5),

    START_DATE DATETIME,

    END_DATE DATETIME,

    SDATFDATETIME,

    EDATF DATETIME,

    [Est Time (Min)] decimal(11,5),

    Est_Time_Hrs decimal(11,5),

    Act_Time_MIN decimal(11,5),

    Adj_Act_Time_MIN decimal(11,5),

    Produced decimal (11,5),

    Year_Number BigInt,

    WK_Number BigInt,

    WK_StartDateDATETIME,

    RecCount BigInt,

    BaseLine BigInt,

    Est_Produced DECIMAL (11,5),

    Split_RecCount BigInt)

    -- LOAD Performance_SUM_Detail_Data_table

    BEGIN TRANSACTION

    INSERT INTO [tempdb..##Performance_SUM_Detail_Data_table]

    select BU, PLT, KST, BLN, BLA, DAT, min(KTR),

    sum(MGL), sum(MGS) , MGD, UMACHINE, ANR,

    SEL1, SEL3, min(PR1) as PR1, sum(PR4) AS PR4,

    sum(DUR) AS DUR, max(SDUR) AS SDUR,

    min(START_DATE) as START_DATE,

    Max(END_DATE) AS END_DATE, MIN(SDATF) AS SDATF,

    MAX(EDATF) AS EDATF, SUM([Est Time (Min)]),

    SUM(Est_Time_Hrs), SUM(Act_Time_MIN),

    Adj_Act_Time_MIN, sum(Produced),

    Year_Number, WK_Number, WK_StartDate,RecCount,0,0,0

    FROM [tempdb..##Performance_Detail_Data_table]

    GROUP BY BU, PLT, KST, BLN, BLA, DAT,

    MGD, UMACHINE, ANR,SEL1, SEL3,

    Adj_Act_Time_MIN,

    Year_Number, WK_Number, WK_StartDate, RecCount

    commit

    ----------------------------------------------------------------------------------------------------

    ----------------------------------------------------------------------------------------------------

    ---

    -- Update Performance_SUM_Detail_Data_table to add Produced Data for each Week

    -- 4/14/14

    --

    update [tempdb..##Performance_SUM_Detail_Data_table] set Produced = (select sum(Producd)

    from X_Produced_By_Period where

    B2BNR = bu AND B2PLT = PLT AND B2BBE = BLN AND [Year] = Year_Number

    and WeekNumber = WK_Number and UMACHINE = b2chr5

    group by bu , PLT, BLN, Year_Number, WK_Number, UMACHINE)

    ------------------------------------------------------------------------------

    -- Update Performance_SUM_Detail_Data_table set Produced nulls to zero

    update [tempdb..##Performance_SUM_Detail_Data_table] set produced = 0 where Produced is null

    -------------------------------------------------------------------------------------

    --- 7/23/14

    -- Update Performance_SUM_Detail_Data_table set RecCount field

    update [tempdb..##Performance_SUM_Detail_Data_table] set RecCount = a.Rec_Count

    from X_SUM_Detail_Data_Count_Total as a

    where [tempdb..##Performance_SUM_Detail_Data_table].BU = a.BU

    AND [tempdb..##Performance_SUM_Detail_Data_table].PLT = a.PLT

    AND [tempdb..##Performance_SUM_Detail_Data_table].KST = a.KST

    AND [tempdb..##Performance_SUM_Detail_Data_table].BLN = a.BLN

    -------------------------------------------------------------------------------------

    --- 7/23/14

    -- Update Performance_SUM_Detail_Data_table set RecCount field to 1 where the value is 0

    update [tempdb..##Performance_SUM_Detail_Data_table] set RecCount = 1

    where RecCount = 0

    -------------------------------------------------------------------------------------

    -- NOTE - May have to take this out if hours are incorrect !!!!!!!

    --- 7/23/14

    -- Update Performance_SUM_Detail_Data_table correct Est_Time_Hrs field (deduct fixed time)

    -- update [tempdb..##Performance_SUM_Detail_Data_table] set Est_Time_Hrs =

    -- Case when Est_Time_Hrs > ( SDUR / RecCount) then Est_Time_Hrs - ( SDUR / RecCount)

    -- else Est_Time_Hrs

    -- end

    -------------------------------------------------------------------------------------

    -----------------------------------------------------------------------------------------------------------------------------------

    --- 8/11/14

    -- Update Performance_SUM_Detail_Data_table add the Est_Produced Data

    -- on the first week of multiple week jobs by the fixed time for the entire job

    --

    update [tempdb..##Performance_SUM_Detail_Data_table] set Est_Produced =

    Est_Time_Hrs * PR1

    -------------------------------------------------------------------------------------

    -----------------------------------------------------------------------------------------------------------------------------------

    -------------------------------------------------------------------------------------

    -------------------------------------------------------------------------------------

    --- 8/11/14

    -- Update Performance_SUM_Detail_Data_table reduce Est_Produced

    -- on the first week of multiple week jobs by the fixed time for the entire job

    --

    update [tempdb..##Performance_SUM_Detail_Data_table] set Est_Produced =

    ((a.Est_Time_Hrs - a.SDUR) * a.PR1) from [tempdb..##Performance_SUM_Detail_Data_table] as a

    inner join [tempdb..##Performance_SUM_Detail_Data_table] on

    [tempdb..##Performance_SUM_Detail_Data_table].BU = a.BU

    AND [tempdb..##Performance_SUM_Detail_Data_table].PLT = a.PLT

    AND [tempdb..##Performance_SUM_Detail_Data_table].KST = a.KST

    AND [tempdb..##Performance_SUM_Detail_Data_table].BLN = a.BLN

    where [tempdb..##Performance_SUM_Detail_Data_table].WK_Number =( select min(b.WK_Number)

    from [tempdb..##Performance_SUM_Detail_Data_table] as b

    where

    b.BU = a.BU

    AND b.PLT = a.PLT

    AND b.KST = a.KST

    AND b.BLN = a.BLN

    AND b.Est_Produced > 0)

    -------------------------------------------------------------------------------------

    -------------------------------------------------------------------------------------

    -------------------------------------------------------------------------------------

    --- 9/02/14

    -- Update Performance_SUM_Detail_Data_table reduce Est_Produced

    -- on the first week of multiple week jobs by the fixed time for the entire job

    --

    update [tempdb..##Performance_SUM_Detail_Data_table] set Est_Produced =

    (a.DUR * a.PR1) from [tempdb..##Performance_SUM_Detail_Data_table] as a

    inner join [tempdb..##Performance_SUM_Detail_Data_table] on

    [tempdb..##Performance_SUM_Detail_Data_table].BU = a.BU

    AND [tempdb..##Performance_SUM_Detail_Data_table].PLT = a.PLT

    AND [tempdb..##Performance_SUM_Detail_Data_table].KST = a.KST

    AND [tempdb..##Performance_SUM_Detail_Data_table].BLN = a.BLN

    INNER JOIN X_SUM_Split_Job_Record_Count AS c ON

    a.BU = c.BU and a.PLT = c.PLT and a.KST = c.KST and a.BLN = c.BLN

    where [tempdb..##Performance_SUM_Detail_Data_table].WK_Number =( select min(b.WK_Number)

    from [tempdb..##Performance_SUM_Detail_Data_table] as b

    where

    b.BU = a.BU

    AND b.PLT = a.PLT

    AND b.KST = a.KST

    AND b.BLN = a.BLN

    AND b.Est_Produced > 0

    and c.Rec_Count > 1)

    -------------------------------------------------------------------------------------

    -------------------------------------------------------------------------------------

    -- TEMP for TESTING

    -- Update Performance_SUM_Detail_Data_table set BaseLine value for testing

    -- update [tempdb..##Performance_SUM_Detail_Data_table]set BaseLine =

    -- ( select min(MGL + MGS) from [tempdb..##Performance_SUM_Detail_Data_table] as a

    -- where [tempdb..##Performance_SUM_Detail_Data_table].BU = a.BU

    -- AND [tempdb..##Performance_SUM_Detail_Data_table].PLT = a.PLT

    -- AND [tempdb..##Performance_SUM_Detail_Data_table].KST = a.KST

    -- AND [tempdb..##Performance_SUM_Detail_Data_table].BLN = a.BLN)

    -------------------------------------------------------------------------------------

    select * from [tempdb..##Performance_SUM_Detail_Data_table]

    SET ANSI_NULLS ON

  • Basically, you need to change the columns in the group by in this update

    update [tempdb..##Performance_SUM_Detail_Data_table] set Produced = (select sum(Producd)

    from X_Produced_By_Period where

    B2BNR = bu AND B2PLT = PLT AND B2BBE = BLN AND [Year] = Year_Number

    and WeekNumber = WK_Number and UMACHINE = b2chr5

    group by bu , PLT, BLN, Year_Number, WK_Number, UMACHINE)

    To use columns from the subquery like this:

    update [tempdb..##Performance_SUM_Detail_Data_table] set Produced = (select sum(Producd)

    from X_Produced_By_Period where

    B2BNR = bu AND B2PLT = PLT AND B2BBE = BLN AND [Year] = Year_Number

    and WeekNumber = WK_Number and UMACHINE = b2chr5

    group by B2BNR, B2PLT, B2BBE, [Year], WeekNumber, b2chr5)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks Luis C.

    Your code helped me solve the issue!

    JW

  • You're welcome.

    Next time, avoid posting lengthy procedures and post only the ones relative to the errors. If you double click on the error, it will locate it for you. You might also want to try the IFCodes, you can find them on the left section of the Reply page.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply