RE: Month-end queries

Here is some embedded SQL I use for first/last of month.


d workdate s d inz(*sys) datfmt(*iso)

exec sql declare c1 cursor for
with
processdate as (
select date(:workdate) as pdate from sysibm/sysdummy1
),
daterange as (
select
pdate - (day(pdate)-1) days as bdate,
pdate - (day(pdate)-1) days + 1 month - 1 day as edate
from sysibm/sysdummy1, processdate
),
...
graphinput as (
select
capturedate, mtdsales, mtdcommittedinventory,
mtduncommittedinventory, futureinventory, mtddemandfirm,
mtddemandplan, salesforecast, mtdsalestarget, budget
from fsicapture, daterange
where capturedate between bdate and edate
),
...


The daterange clause will determine the first and last day of the month
of whatever is workdate. Note I am forcing a cross/Cartesian join by
specifying ",daterange" and ",processdate", but this is intended.

HTH,
Loyd


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Burns, Bryan
Sent: Thursday, April 30, 2009 9:23 AM
To: MIDRANGE-L@xxxxxxxxxxxx
Subject: Month-end queries

At the end of every month, I change the record selection date range in
15 or so queries to reflect the current month in order to get month-end
totals. Is there a way to automate this? Of course, the files to be
queried may be different so the date fields will not always be the same.