CREATE TABLE [dbo].[ConformDaily] (
[ident] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[RecordType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[yyyymmdd] [int] NULL ,
[Mat_Class] [int] NULL ,
[pct_in] [float] NULL ,
[std_dev] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SkidChill] (
[ident] [int] IDENTITY (1, 1) NOT NULL ,
[yyyymmdd] [int] NULL ,
[pct_in] [float] NULL ,
[std_dev] [float] NULL ,
[Mat_Class] [int] NULL ,
[TRANS_AIM] [float] NULL ,
[TRANS_OFFSET] [float] NULL ,
[TRANS_CALC] [float] NULL ,
[TRANS_CROWN] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[HRP_UP] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HRP_DOWN] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HRP_AUTO] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HRP_USED] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CS_TEMP_INIT_HEAD] [varchar] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CS_TEMP_FINAL_HEAD] [varchar] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CS_TEMP_TAIL] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CROP_HEAD_LEN] [float] NULL,
[UR_LEAD_SPEED] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PR_LEAD_SPEED] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PR_LOAD] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COILER_AUTO] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MANDREL_LEAD_SPEED] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MANDREL_AMPS] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[COILER_GUIDE_SU] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[COILER_GUIDE_OFFSET] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PR_GAP_SETUP] [float] NULL ,
[PR_GAP_OFFSET] [float] NULL ,
[COILER_USED] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MANDREL_REF_AT_PU] [varchar] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MANDREL_SPD_AT_PU] [varchar] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PR_SPD_AT_PU] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MANDREL_LEAD_SPD_REF] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PR_LEAD_SPD_REF] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[UR_HOLD_TIME] [float] NULL ,
[PCNT_CARBON] [float] NULL ,
[PR_SPEED_ERR] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[COIL_TEMP_AIM] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OPER_COIL_TEMP_OFFSET] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[COIL_TEMP_AIM_FMSU] [varchar] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CT_PLUS_LIMIT] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CT_PLUS_LIMIT_2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CT_MINUS_LIMT] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CT_MINUS_LIMIT_2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[COIL_TEMP_HE_ERR] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CT_MIN_TOL_1] [float] NULL
) ON [PRIMARY]
GO
CREATE VIEW dbo.v_R6_Skid_Chill_Conformance_last_7_days
AS SELECT yyyymmdd, Mat_Class, pct_in, std_dev
from SkidChill
GO
Insert INTO ConformDaily(RecordType, yyyymmdd, Mat_Class, Pct_in,
Std_Dev)
VALUES ('SkidChill', 20040503, 1, 0, 0)
Insert INTO ConformDaily(RecordType, yyyymmdd, Mat_Class, Pct_in,
Std_Dev)
VALUES ('SkidChill', 20040503, 2, 0, 0)
Insert INTO ConformDaily(RecordType, yyyymmdd, Mat_Class, Pct_in,
Std_Dev)
VALUES ('SkidChill', 20040503, 5, 0, 0)
Insert INTO SkidChill(yyyymmdd, Mat_Class, Pct_in, Std_Dev)
VALUES (20040503, 1, 97.1, 26.98)
Insert INTO SkidChill(yyyymmdd, Mat_Class, Pct_in, Std_Dev)
VALUES (20040503, 2, 95.894, 29.23)
Insert INTO SkidChill(yyyymmdd, Mat_Class, Pct_in, Std_Dev)
VALUES (20040503, 5, 92.123, 28.12)
UPDATE dbo.ConformDaily
SET Pct_in= (select Pct_In from
v_R6_Skid_Chill_Conformance_last_7_days where Mat_Class=1 and
yyyymmdd='20040503') ,
Std_Dev= (select Std_Dev from v_R6_Skid_Chill_Conformance_last_7_days
where Mat_Class=1 and yyyymmdd='20040503' )
WHERE RecordType='SKIDCHILL' and
yyyymmdd='20040503' and Mat_Class=1
UPDATE dbo.ConformDaily
SET Pct_in= (select Pct_In from
v_R6_Skid_Chill_Conformance_last_7_days where Mat_Class=2 and
yyyymmdd='20040503') ,
Std_Dev= (select Std_Dev from v_R6_Skid_Chill_Conformance_last_7_days
where Mat_Class=2 and yyyymmdd='20040503' )
WHERE RecordType='SKIDCHILL' and
yyyymmdd='20040503' and Mat_Class=2
UPDATE dbo.ConformDaily
SET Pct_in= (select Pct_In from
v_R6_Skid_Chill_Conformance_last_7_days where Mat_Class=5 and
yyyymmdd='20040503') ,
Std_Dev= (select Std_Dev from v_R6_Skid_Chill_Conformance_last_7_days
where Mat_Class=5 and yyyymmdd='20040503' )
WHERE RecordType='SKIDCHILL' and
yyyymmdd='20040503' and Mat_Class=5
/*
Results Desired
ConformDaily Table
yyyymmdd Mat_Class pct_in std_dev
20040503 1 97.1 26.98
20040503 2 95.894 29.23
20040503 5 92.123 28.12
The ConformDaily will now have the updated records from the view
Basically the view is more real time, and the ConformDaily Table is
being used
as the source to generate reports that do not have to be in real time,
but need to load and generate quickly
So, I want to use an update command, not a delete and insert.
Is there any way to loop through all available Mat_Class and update
the records? I will not
know which material classes they produce each day.
Hope that helps, any more info needed, please ask!
*/