April 2, 2020 at 11:30 am
I would like to update TASK_NXT_RUN_DT column based on the script in column 2 dynamically. I have tried using while loop and dynamic sql but no luck. Any help on this much appreciated
create table #CFG_PROCESS_EVALUATE_TASK_MSTR (
taskid int,
TASK_SCHED_SQL_SCRIPT varchar(500),
TASK_LST_RUN_DT datetime,
TASK_NXT_RUN_DT datetime
)
insert into #CFG_PROCESS_EVALUATE_TASK_MSTR
select 1,'dateadd(dd,1,TASK_NXT_RUN_DT)','2020-04-02 03:17:40.043','2020-04-02 03:17:24.353' union all
select 2,'dateadd(dd,10,TASK_NXT_RUN_DT)','2020-04-02 03:17:40.043','2020-04-02 03:17:24.353'
April 2, 2020 at 1:58 pm
I would like to update TASK_NXT_RUN_DT column based on the script in column 2 dynamically. I have tried using while loop and dynamic sql but no luck. Any help on this much appreciated
create table #CFG_PROCESS_EVALUATE_TASK_MSTR (
taskid int,
TASK_SCHED_SQL_SCRIPT varchar(500),
TASK_LST_RUN_DT datetime,
TASK_NXT_RUN_DT datetime
)
insert into #CFG_PROCESS_EVALUATE_TASK_MSTR
select 1,'dateadd(dd,1,TASK_NXT_RUN_DT)','2020-04-02 03:17:40.043','2020-04-02 03:17:24.353' union all
select 2,'dateadd(dd,10,TASK_NXT_RUN_DT)','2020-04-02 03:17:40.043','2020-04-02 03:17:24.353'
Have you considered using a different model? For example, create an INT column called 'DaysToAdd' (set it to 1, 10 in your example above). The UPDATE query then becomes trivial.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply