Re: Month-end queries

Bryan,

I have automated the date in qry/400 lots of times. Alot depends on the
timing of the query being run. If the query is being consistently executed
before the end of the month (before midnight on the last day of the month) -
you can use one date math routine. If the query is being consistently run
after the end of month (after midnight on the last day of the month) you use
a slightly different routine. If it run spanning midnight, then it would
not be easy.

The technique occurs in the "Define Result Fields" where you can retrieve
the current date. You then need to translate the date to the first of the
previous month like;

First = current(date) - (day(current(date)) - 1) days - 1 month

End of previous month like:

EOM = current(date) - day(current(date)) days

After that translate the dates to match your date fields, or translate your
date fields to a date data type.

Then in the "Select Records" you can do

Your_Date Range First EOM

HTH

Jim


On Thu, Apr 30, 2009 at 9:09 AM, Burns, Bryan <Bryan_Burns@xxxxxxxxxxxx>wrote:

We use Query for iSeries also known as Query/400, et al.
Bryan Burns
iSeries Specialist
ECHO, Incorporated
Lake Zurich, Illinois

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Glenn Hopwood
Sent: Thursday, April 30, 2009 9:30 AM
To: Midrange Systems Technical Discussion
Subject: Re: Month-end queries

Most of the query tools allow you to pull in the current month. Each of
them have different ways of doing it. Which tool are you using?

Glenn

Burns, Bryan wrote:

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.

Bryan Burns
iSeries Specialist
ECHO, Incorporated
Lake Zurich, Illinois

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.