The requirement is to produce a YTD comparative report to last year YTD.
I need to end up with a where clause that resolves to this in sql:
where (b.inv_shrt_dt between RTRIM(CHAR(year(current date - 1 YEAR))) ||'-01-01' and current date - 1 year
or (b.inv_shrt_dt between RTRIM(CHAR(year(current date))) ||'-01-01' and current date))
I actually got it working with the define tool, to find out that this executes on every row, not the DB, so I was told to "do it with &var using -SET" and using the amper var in the where clause so that the where is executed on the server.
Any help would be greatly appreciated,
Hope this helps:
-SET &DT = '20050510';
-SET &CURRENT_YR = 'NO';
-*-SET &CURRENT_YR = 'YES';
-SET &YR = EDIT(&DT,'9999');
-SET &YR = IF &CURRENT_YR EQ 'YES' THEN &YR ELSE IF &CURRENT_YR EQ 'NO' THEN &YR -1 ELSE &YR;
-TYPE The year used in WHERE statement is: &YR
-SET &WHERE_YR = 'where b.inv_shrt_dt between ' | &YR || '-01-01' ;
how do I SET &DT to "today's" date every day in YYMD format?
-SET &Y1 = &DATEYY;
-* Gets you the current year
-SET &Y2 = &Y1 - 1;
-* Gets you the prior year
-TYPE &|Y1 IS &Y1 &|Y2 IS &Y2
Thanks for the replies, they helped get me there! below is what I ended up with, I'm posting it so if anyone wants to give me "best practices" suggestions on the implementation, please do so! I had difficulty using DATEADD in a SET command, but it seemed to work fine in a DEFINE file ...??? That's why I parsed out THIS_MNTH_DAY and concatenated it to LAST_YR to get TO_DT_LY. I am also not certain if it was necessary for me to use DATECVT, but it's where i've ended up and I need to move on.
Here it is:
-SET &THIS_YR=EDIT(&TODAY_YYMD, '9999');
-SET &THIS_MNTH_DAY=EDIT(&TODAY_YYMD, '$$$$9999');
-SET &LAST_YR=&THIS_YR - 1;
DIM_PERIOD.DIM_PERIOD.PERIOD_KEY IN DIM_PERIOD TO ALL
FCT_DLY_ITM_SLS.FCT_DLY_ITM_SLS.PERIOD_KEY IN FCT_DLY_ITM_SLS AS J0
TABLE FILE DIM_PERIOD
WHERE TRANS_LVL_CD EQ 1
AND (((INV_SHRT_DT GE &FROM_DT_LY_YYMD) AND (INV_SHRT_DT LE &TO_DT_LY))
((INV_SHRT_DT GE &FROM_DT_TY_YYMD) AND (INV_SHRT_DT LE &TODAY_YYMD)));
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
FONT='TIMES NEW ROMAN',
Here's some stream-lined code that might work better. This may wrap strangely in the posting. The first -SET should be 2 lines. The other -SET statements are one line each.
-* Subtract 1 yr
- 'Y',-1)),'YYMD', 'I8YYMD');
-* Change month/day to Jan 1
-SET &CUR_BEG = EDIT(&DATEYY,'9999') | '0101';
-SET &LST_BEG = EDIT(&LST_YTD,'9999') | '0101';
You can't use the DATEADD function directly with amper-variables because the DATEADD function works on smart dates (which is why you can use it in a DEFINE) and amper-variables are not stored in date format. So to use DATEADD in the -SET for &LST_YTD you have to:
1) convert &YYMD to a date format using the DATECVT function
2) perform the DATEADD function to subtract 1 year
3) convert the smart date result back to a format that can be stored in an amper-variable -- I8YYMD here.
You can see this when you look at the code above by working through the parentheses from the inside out.
Your WHERE statement would look like this:
AND (((INV_SHRT_DT GE &LST_BEG) AND (INV_SHRT_DT LE &LST_YTD)) OR
((INV_SHRT_DT GE &CUR_BEG) AND (INV_SHRT_DT LE &YYMD)));
Hope this helps.
nice. thanks codermonkey.
|Powered by Social Strata|