help on script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help on script

Author

Topic

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-06-12 : 02:52:44

Hi,
i have the following script and i have to execute this script every sundays. Its just that i need to edit the script at some of the places. is there anyway that i can automatically edit the script? The items in light blue shuld be edited.

For example,

WW23 should be changed to WW24 and so on
20080601 23 should be changed to 20080608 24 and so on
20080605 23 should be changed to 20080612 24 and so on
ST_SUM05 should be changed to ST_SUM06 and so on

Select 'WW23', EET.eqp_id, EET.st_id, EET.ST_DSC, EET.intrvl_val, EET.st_dur_hour, EET.op_id, EEC.op_comments, EEC.last_update
from
(
select
a1.eqp_id, a1.st_id, SUBSTR(a1.st_dsc,1,30) ST_DSC, a1.intrvl_val, a1.st_dur_hour, b1.op_id--, a1.op_comments
from
(
select distinct a.*, max(b.op_start_time) max_time from
( select
DISTINCT
s.eqp_id ,
s.st_id,
t.st_dsc,
S.INTRVL_VAL ,
round(st_dur / 3600, 4) as st_dur_hour
from
ST_SUM05 s, --real log in/out table
st t--, -- state table
where
s.st_id = t.st_id
and s.st_id IN ('3100','3200','3001','3000')
and to_char(s.intrvl_val, 'yyyymmdd hh24') >= '20080601 23' and to_char(s.intrvl_val, 'yyyymmdd hh24') < '20080605 23'
and s.eqp_id like ('MOD_PTST7%')
) a,
( select eqp_id, op_start_time, op_id from ST_SUM_OP_ID -- operator table
where eqp_id like ('MOD_PTST7%') AND
to_char(op_start_time+1, 'yyyymmdd hh24') >= '20080601 23' and
to_char(op_start_time, 'yyyymmdd hh24') < '20080605 23'
)b
WHERE a.eqp_id = b.EQP_ID AND
a.INTRVL_VAL > = b.OP_START_TIME
group by a.eqp_id , a.st_id, a.st_dsc, a.INTRVL_VAL , a.st_dur_hour--, a.op_comments
) a1,
( select eqp_id, op_start_time, op_id from ST_SUM_OP_ID
where eqp_id like ('MOD_PTST7%') AND
to_char(op_start_time+1, 'yyyymmdd hh24') >= '20080601 23' and
to_char(op_start_time, 'yyyymmdd hh24') < '20080605 23' )
b1
where a1.max_time = b1.op_start_time and a1.eqp_id = b1.eqp_id
) EET, --EE_TestTime

(
select
a1.eqp_id, a1.st_id, SUBSTR(a1.st_dsc,1,30) ST_DSC, a1.intrvl_val, a1.st_dur_hour,
lpad(b1.op_comments, length(b1.op_comments)-2) as op_comments, b1.last_update
from
(
select distinct a.*, max(b.last_update) max_time from
( select
DISTINCT
s.eqp_id ,
s.st_id,
t.st_dsc,
S.INTRVL_VAL ,
round(st_dur / 3600, 4) as st_dur_hour
from
ST_SUM05 s, --real log in/out table
st t--, -- state table
where
s.st_id = t.st_id
and s.st_id IN ('3100','3200','3001','3000')
and to_char(s.intrvl_val, 'yyyymmdd hh24') >= '20080601 23'
and to_char(s.intrvl_val, 'yyyymmdd hh24') < '20080605 23'
and s.eqp_id like ('MOD_PTST7%')
) a,
( select eqp_id, intrvl_val, op_comments, last_update from ST_SUM_OP_COMMENTS
where eqp_id like ('MOD_PTST7%') AND
to_char(last_update+1, 'yyyymmdd hh24') >= '20080601 23' and
to_char(last_update, 'yyyymmdd hh24') < '20080605 23' )b
WHERE a.eqp_id = b.EQP_ID AND
a.INTRVL_VAL > = b.intrvl_val AND
a.INTRVL_VAL > = b.last_update
group by a.eqp_id , a.st_id, a.st_dsc, a.INTRVL_VAL , a.st_dur_hour
) a1,
( select eqp_id, intrvl_val, op_comments, last_update from ST_SUM_OP_COMMENTS
where eqp_id like ('MOD_PTST7%') AND
to_char(last_update+1, 'yyyymmdd hh24') >= '20080601 23' and
to_char(last_update, 'yyyymmdd hh24') < '20080605 23'
)
b1
where a1.max_time = b1.last_update and a1.eqp_id = b1.eqp_id
)
EEC --EE Comments
Where
EET.eqp_id = EEC.eqp_id(+)
AND EET.st_id = EEC.st_id(+)
AND EET.ST_DSC = EEC.ST_DSC(+)
AND EET.intrvl_val = EEC.intrvl_val(+)
AND EET.st_dur_hour = EEC.st_dur_hour(+)
Order by EET.eqp_id, EET.intrvl_val

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 02:58:20

Why cant you replace the blue values in your script with parameters and pass the values you desire each time through them rather than opening and modifying code with hardcoded values?

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-06-12 : 03:16:14

Ya, Thats what i am thinking but i am not sure how to do that. When i use getdate() command it says unknown column and so on.. SO i dont know how to set the parameters..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 03:24:44

quote:
Originally posted by shan1430

Ya, Thats what i am thinking but i am not sure how to do that. When i use getdate() command it says unknown column and so on.. SO i dont know how to set the parameters..

Why? Are you using MS SQL Server?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources