Monthly Archives: December 2016

sp change

ALTERPROCEDURE [dbo].[client_ashrae_paoe_auto_task_exec]

@metrics_key VARCHAR(40)

AS

BEGIN

DECLARE @p39_key VARCHAR(40);

DECLARE @p38_key VARCHAR(40);

DECLARE @p32_key VARCHAR(40);

DECLARE @p37_eval_type VARCHAR(90);

DECLARE @p37_eval VARCHAR(255);

DECLARE @p37_eval_formula VARCHAR(MAX);

DECLARE @p32_maximum_entry DECIMAL(20, 3);

DECLARE @p32_maximum_points DECIMAL(20, 3);

DECLARE @sSQL VARCHAR(MAX);

DECLARE @multiplier INT

DECLARE @err_number INT

DECLARE @feedback VARCHAR(MAX)

SET @p39_key =NEWID()

BEGINTRAN

SELECT @p32_key = p32_key ,

@p32_maximum_entry =ISNULL(p32_maximum_entry, 0.001),

@p32_maximum_points =ISNULL(p32_maximum_points, 0),

@p37_eval_formula = p37_eval_formula ,

@p37_eval_type = p37_eval_type ,

@p38_key = p38_key ,

@p37_eval = p37_eval

FROM dbo.client_ashrae_paoe_type(NOLOCK)

JOIN dbo.client_ashrae_paoe_type_x_eval(NOLOCK)ON p32_key = p38_p32_key

JOIN dbo.client_ashrae_paoe_eval(NOLOCK)ON p37_key = p38_p37_key

WHERE p37_key = @metrics_key

–set multiplier

–SET @multiplier = CASE WHEN @p32_maximum_points=0 THEN tiplier

–start the eval exec

INSERTINTO client_ashrae_paoe_eval_record

( p39_key ,

p39_p38_key ,

p39_start_time

)

VALUES ( @p39_key ,

@p38_key ,

GETDATE()

)

–create table Ashrae_work_tables.dbo.STG_PAOE_POINTS (cnt int, chp_cst_key varchar(40));

TRUNCATETABLE Ashrae_work_tables.dbo.STG_PAOE_POINTS ;

SET @sSQL =‘ INSERT INTO Ashrae_work_tables.dbo.STG_PAOE_POINTS(cnt,chp_cst_key) ‘

+ @p37_eval_formula

EXECUTE (@sSQL)

SELECT @err_number =@@ERROR

IF @err_number <> 0

BEGIN

SET @feedback =‘ execute metric error!’

PRINT @feedback

GOTO ROLLBACKBLOCK

END

PRINT @p37_eval +‘ recal start at ‘+CAST(GETDATE()ASVARCHAR(20))

+CHAR(10)

— update existing records

UPDATE client_ashrae_paoe_entry

SET p33_change_date =getdate()

, p33_change_user =‘Automation’

, p33_points =(CASEWHEN @p37_eval_type =‘EVAL_FLAG’

OR @p32_maximum_points = 0 THEN stg.cnt

ELSE (CASEWHEN stg.cnt > @p32_maximum_points/ @p32_maximum_entry

THEN @p32_maximum_points / @p32_maximum_entry

ELSE stg.cnt

END)

END)*( @p32_maximum_entry )

FROM Ashrae_work_tables.dbo.STG_PAOE_POINTS stg

JOIN client_ashrae_paoe_entry ent

ON ent.p33_p32_key = @p32_key

AND ent.p33_chp_cst_key =stg.chp_cst_key

PRINT @p37_eval +‘ point entry start at ‘

+CAST(GETDATE()ASVARCHAR(20))+CHAR(10)

— insert if not exist, left join with chp_key null will tell you non-exist

INSERTINTO client_ashrae_paoe_entry

( p33_p32_key ,

p33_chp_cst_key ,

p33_points ,

p33_add_user ,

p33_add_date

)

SELECT @p32_key ,

stg.chp_cst_key ,

(CASEWHEN @p37_eval_type =‘EVAL_FLAG’

OR @p32_maximum_points = 0 THEN stg.cnt

ELSE (CASEWHEN stg.cnt > @p32_maximum_points

/ @p32_maximum_entry

THEN @p32_maximum_points

/ @p32_maximum_entry

ELSE stg.cnt

END)

END)*( @p32_maximum_entry ) a32_paoe_point ,

‘Automation’,

GETDATE()

FROM Ashrae_work_tables.dbo.STG_PAOE_POINTS stg

LEFTJOIN client_ashrae_paoe_entry ent

ON ent.p33_p32_key = @p32_key

AND ent.p33_chp_cst_key =stg.chp_cst_key

WHERE ent.p33_chp_cst_key ISNULL

SELECT @err_number =@@ERROR

IF @err_number <> 0

BEGIN

SET @feedback =‘ execute metric error!’

PRINT @feedback

GOTO ROLLBACKBLOCK

END

— update status

UPDATE client_ashrae_paoe_eval_record

SET p39_end_time =GETDATE(),

p39_status = 100

WHERE p39_key = @p39_key

COMMITTRAN

PRINT‘update finish at ‘+CAST(GETDATE()ASVARCHAR(20))+CHAR(10)

ROLLBACKBLOCK:

IF @err_number <> 0

BEGIN

ROLLBACKTRAN

PRINT‘Roll Back Point Change for ‘+ @p37_eval

EXEC msdb.dbo.sp_send_dbmail@profile_name =‘amsdev’,

@recipients =’email address’,

@subject =‘PAOE task run error (testing)’,

@body =‘One of the PAOE automation task has ended in error!’

END

END;