Hi All,

I have written a sql script and it runs in informix. I have to modify the scripts so that each week when the scripts run they append the "new" week's data, so we don't have to re-run the entire timeframe each time we run them.

and

then modify scripts so that user does not have to change Yearmonth, week nbr, and txn dt perimeters. Each time the when the script runs these dates should automatically change depending on the system date.

Thank you. Your help is greatly appreciated.

Hi,

You can play with current in sql script of yours. Find below the script to find dates based on current (see the comments to find what the expressions does actually) and modify the same as per your requirement:
select current year to day as cytd
, day(current) as day

-- current month start day
, current year to day - day(current) units day + 1 units day as mth_start1
, current year to day - (day(current)-1) units day as mth_start2
, extend (current year to month, year to day) as mth_start3 -- most elegant ?

-- current month end day
, current year to day + 1 units month -
(day(current)) units day as month_end

-- previous month end day
, current year to day - (day(current)) units day as prev_month_end

-- previous month start day
, current year to day -
(day(current)-1) units day - 1 units month as prev_month_start
, extend (current year to month, year to day) - 1 units month as prev1_month_start


-- n months previous end day (eg below is 4 months) -- ( biggest frig comparred to the others)
, current year to day -3 units month - (day(current)) units day as prevN_month_end

-- previous n month start day
, current year to day -
(day(current)-1) units day - 3 units month as prevN_month_start
, extend (current year to month, year to day) - 3 units month as prevN1_month_start

-- year start date
, extend (current year to year, year to day)
, extend (current year to month, year to day) - (month(current)-1) units month as year_start

from systables
where tabname = 'systables'

Bye

Nitin