Data selection criteria to pull last months information

30 pts.
Tags:
AS/400 date format
Date Picker
I run a series of queries at the beginning of each month to pull transactions from the previous month.  Currently I am setting the date range for the julian dates.  I would like to automate this series of queries and need a way to automate the data selection criteria so it will automatically pull the previous months information.

 

Is there any way to do this?  I have a date conversion file that give me access to a month name or a month number.  Watch out for January!



Software/Hardware used:
AS400 query

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you are using AS400 query, you can define a result field to use for you selection.
You can retrieve todays date using using teh keyword CURRENT.
Then you can create other result fields using that and values to determine the begining of the previous month.
I have done this in the past but cannot find a sample for you.
If you do F1 to get the HELP text on the field EXPRESSION in the Define Result Field, you will see what other options to use.

—–

If you need the last day of last month you could use the following in your query:
<pre>
LSTMTHEND CURRENT(DATE)-(DAY(CURRENT(DATE))) DAYS
</pre>
It takes today’s date and subtracts today’s day to get the last day of last month.

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • aceofdelts
    A non-query approach is to copy your transactions into work file(s) and run the queries from there (selecting all records). Easy enough to automate the date used for work file creation (e.g. RPG gets date (CCYYJJJ or whatever format fits your data) and passes it to CL who does CPYF (or equivalent). Downside is a large transaction file gives you performance and disk space issues. Upside is you could expand this into a way to rerun prior months, view current month, etc.
    1,960 pointsBadges:
    report
  • Teandy
    Would it not be cleaner and more efficient to consolidate your "series of queries" down to maybe one or two programs? Say one CL and one RPGLE? That would afford you far more flexibility for date manipulation, data extraction and delivery. You could set up the job on the job scheduler to run at the first of each month and not have to “remember” to run the option. Also, can convert the report to excel, PDF, or even plain text and have the program email the report to a predefined distribution list.
    5,860 pointsBadges:
    report
  • TomLiotta
    I run a series of queries... How? By typing an option on a menu? or Submitting a job that calls a program? or Calling programs from a command line? Executing a series of STRQMQRY (or RUNQRY) commands...? Are multiple queries run because of multiple files or just for multiple reports from a single file? Currently I am setting the date range for the julian dates. How? By changing each query before running? (QM query? Query/400? STRSQL? Web query? OPNQRYF? Other?) Do you type Julian dates or are the files defined with Julian dates and your input dates are converted to Julian? Other...? I would like to automate this series of queries and need a way to automate the data selection criteria so it will automatically pull the previous months information. One general interpretation is maybe -- You have a file(s) that you run monthly reports from, generally using the prior month's data. The reports have different formats and possibly different sort sequencings. You'd like this series of reports to happen automatically at (or very soon after) the start of every month. The series should know what month's data to use because the system already knows the current date and, therefore, knows the current month, and should figure out the prior month's records. Close? Tom
    125,585 pointsBadges:
    report
  • Clhalfa
    I access Query utilities and Work with Queries to submit each query individually. I go into each query that require the date to be reset to the previous month and change the julian date range before running the queries. The first query that I run is a history pull to extract the previous months data. From there the series of queries have different formats, data selections and sort sequences. I would like a program to automatically change the dates or be able to pull the previous month's history to a file and from there have a robot that runs the series of queries in sequence to build the various files and import them into excel. The problem I need to overcome is how to set the data selection so that it knows to select the previous months history without having to go into the query and reset the data selection criteria for the julian date each month.
    30 pointsBadges:
    report
  • Teandy
    Here is a little bit of psudo code to get you started. Run your program on the first day of the month.
    
    d  monthend       s               d   datfmt(*mdy)    
    d  monthstart     s               d   datfmt(*mdy)    
    d  lastdayofmon   s              2  0 inz(0)          
    
    /free
    
       monthend = %date() - %days(1);       // get the end of the previous month.             
       lastdayofmon = %subdt(monthend : *days);    // extract month number of days          
       monthstart = monthend - %days(lastdayofmon - 1);   // get the beginning of the month
    
    /end-free
    
    
    5,860 pointsBadges:
    report
  • Teandy
    I just realized that you said you were using julian dates. If you change the date format in the D specs to be *JUL instead of *MDY, the code will work the same. Also it should be pseudo code not psudo code.
    5,860 pointsBadges:
    report
  • TomLiotta
    I apologize for late response. I must have gotten pulled into too many work issues around that time and neglected this. I access Query utilities and Work with Queries to submit each query individually. I go into each query that require the date to be reset to the previous month and change the julian date range before running the queries. It's unfortunate this is a Query/400 issue. It would be far easier if these had been converted to Query Manager queries a decade or so ago when Query/400 became obsolete. But changes can still be made, even if the QM commands need to execute the modified Query/400 queries. Before going into any details, you should review the Selecting records for an OfficeVision dependent column list in Query/400 topic in Chapter 7, Selecting records in Query/400, of the Query/400 Use manual. Although the topic goes back to the (obsolete) OfficeVision product, the technique still works and can be of good use in a case such as yours. It provides a way to embed a reference in the query to a parameter value that you supply when you run the query. The parameter will be the date that you are changing every month. The Query/400 product can't actually handle running this way, but Query Manager can. Also, QM can be used to both handle the date parameter and run the Query/400 query. The STRQMQRY command will be used to do the work. Look through the topic to see the kinds of steps that will be done. Be aware that references to any "query or file" that your query will become dependent upon won't ever actually exist. Don't get sidetracked into thinking the topic is inappropriate. Your query won't actually receive the date value from any file. You simply need to make changes as if a file will supply the date value. It will be handled without a file ever actually existing. A similar QM query would do this directly. A Query/400 query needs some help. (You might consider converting them now.) I'll add this item to my WatchList to try to get back much quicker next time. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following