Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     date manipulation with -SET from a newbie
Go
New
Search
Notify
Tools
Reply
  
date manipulation with -SET from a newbie
 Login/Join
 
<frankie>
posted
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,
Frankie
 
Reply With QuoteReport This Post
Guru
posted Hide Post
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' ;
-TYPE &WHERE_YR
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Reply With QuoteReport This Post
<frankie>
posted
how do I SET &DT to "today's" date every day in YYMD format?

Thanks
 
Reply With QuoteReport This Post
Guru
posted Hide Post
Frankie:
Try this.
-SET &Y1 = &DATEYY;
-* Gets you the current year
-SET &Y2 = &Y1 - 1;
-* Gets you the prior year
-TYPE &|Y1 IS &Y1 &|Y2 IS &Y2
-EXIT
 
Posts: 252 | Location: USA | Registered: April 15, 2003Reply With QuoteReport This Post
<frankie>
posted
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 &ECHO='ALL';
-SET &TODAY_YYMD=&YYMD;
-SET &THIS_YR=EDIT(&TODAY_YYMD, '9999');
-SET &THIS_MNTH_DAY=EDIT(&TODAY_YYMD, '$$$$9999');
-SET &LAST_YR=&THIS_YR - 1;
-SET &FROM_DT_LY=&LAST_YR||0101;
-SET &FROM_DT_LY_YYMD=DATECVT(&FROM_DT_LY,'I8','YYMD');
-SET &TO_DT_LY=&LAST_YR||&THIS_MNTH_DAY;
-SET &TO_DT_LY_YYMD=DATECVT(&TO_DT_LY,'I8','YYMD');
-SET &FROM_DT_TY=&THIS_YR||0101;
-SET &FROM_DT_TY_YYMD=DATECVT(&FROM_DT_TY,'I8','YYMD');
-RUN
JOIN
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
END
TABLE FILE DIM_PERIOD
SUM
ITM_EXT_PRC_L_AMT
ACROSS
YR_NBR
WHERE TRANS_LVL_CD EQ 1
AND (((INV_SHRT_DT GE &FROM_DT_LY_YYMD) AND (INV_SHRT_DT LE &TO_DT_LY))
OR
((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 *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
ENDSTYLE
END
 
Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Frankie,
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
-SET &LST_YTD=DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),
- '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.
 
Posts: 118 | Location: DC | Registered: May 13, 2005Reply With QuoteReport This Post
<frankie>
posted
nice. thanks codermonkey.
 
Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     date manipulation with -SET from a newbie

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.