Re: Month-end queries

[Jim Essinger <dilbernator@xxxxxxxxx>]

John,

If that source is something you are willing to share with the group, please
post the code at code.midrange.com, then post the link to the source back to
this discussion.

Otherwise, I would like a copy too!

;^)

Jim
On Thu, Apr 30, 2009 at 9:02 AM, John Bussert <JBussert@xxxxxxxxxxxxxx>wrote:

> We use a little trick here with an sql view with lots of dates in it
> based on today (we create last month, period, etc)  then we just join
> the query to it and the dates adjust automatically.  The nice part is
> with 5.4 and better there are some new built in sql functions - like
> lastday that gives you the EOM dates for the month.
>
> If you are interested let me know and I can send you the source for it.
>
> Current_Date
> Last_Year_Today
> Current_CCYYMMDD
> Current_MMDDYY
> Last_Year_MMDDYY
> Current_CCYY
> Current_MM
> Current_DD
> Current_YY
> Current_YYMM
> Prev_Mth_MM
> Prev_Mth_YY
> Prev_Mth_YYMM
> Cur_Qtr_Beg_YYMM
> Cur_Qtr_End_YYMM
> Prev_Qtr_Beg_YYMM
> Prev_Qtr_End_YYMM
> Cur_Yr_Beg_YYMM
> Prev_Yr_Beg_YYMM
> Prev_Yr_YY
>
>
> John
> Swift Technologies, Inc
> Elgin, IL
>
>
> -----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.
>
> 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.