July 16, 2015 at 12:43 pm
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.
July 16, 2015 at 12:57 pm
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
July 16, 2015 at 1:03 pm
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
July 16, 2015 at 1:24 pm
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)
July 17, 2015 at 7:40 am
Thanks Luis C.
Your code helped me solve the issue!
JW
July 17, 2015 at 9:31 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply