Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

The desire is to extract data from 12 months of data from a ...


robert fuschetto

Recommended Posts

The desire is to extract data from 12 months of data from a table.

The user can only be prompted for a thru (end) Period. This report is being integrated onto an existing dashboard that already prompts for THRUPERIODGREAT! We do not we want to add a STARTPERIOD prompt thoso I must derive the start period in some fashion.

To aid in this, we have a calendar table. It shows each month(PERIOD) and how many months old it is(MO). I could derive the MOSTART:

Ex:

For PERIOD: 202110, MO=0, MOSTART = -12

202109, MO = -1, MOSTART = -13

202108, MO = -2, MOSTART = -14

etc.

I have a query that does this. The user is prompted and enter the thru period of 202105. The HOLD file results in an entry of:

PERIOD = 202105

MO = -5

MOSTART = -17

All I now need to do is get the HOLD file value for MOSTART into a parameter called &MOSTARTPARAM and I can set my subsequent query to filter on:

PERIOD <=&PERIOD AND

MO>=MOSTARTPARAM

How do I get the contents of HOLD.MOSTART into &MOSTARTPARAM

Link to comment
Share on other sites

Something such as this

SET ASNAMES = ON

TABLE FILE MyHoldFile

BY PERIOD AS 'PERIOD'

BY MO AS 'MO'

BY MOSTART AS 'MOSTARTPARAM'

ON TABLE HOLD AS EXTPARM

END

-RUN

-READFILE EXTPARM

-DEFAULTH &PARIOD = ''

-DEFAULTH &MO = ''

-DEFAULTH &MOSTARTPARAM = ''

 

-TYPE PERIOD: &PERIOD

-TYPE MO: &MO

-TYPE MOSTARTPARAM: &MOSTARTPARAM

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...