Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] BIlldate =MAX.WEEKENDDATE - 21DAYS

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] BIlldate =MAX.WEEKENDDATE - 21DAYS
 Login/Join
 
Guru
posted
I am faceing this problem. I am stuck at the first step actually:

I have two fields:
Weekend & lastbilldate
Both of field format is datetime. I am trying to convert datetime to date. I was able to convert in my metadata. Now i want to use
  
WHERE lastbilldate = MAX.weekenddate - 21 days
I looked at 1001 ways to work with dates but nothing seems to be working. 

This message has been edited. Last edited by: Kerry,


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Virtuoso
posted Hide Post
A multi-verb request might work.

TABLE FILE <filename>
 SUM COMPUTE compare_date/HYYMD = HADD(MAX.weekenddate,'DAY',-21,8,'HYYMD'); NOPRINT
 PRINT <column names>
 WHERE TOTAL lastbilldate EQ compare_date ;
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Guru
posted Hide Post
Thanks for the help. It seemed like it was working. However, when i did QA with SQL query, I got different numbers. Somehow comparison is not working. Its looking for value in the quotes and since its the field name and not variable I cant use quotes around the field name. Any idea how can i use two fields in comparison? For example,
WHERE LASTBILLDATE LE compare_date;


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Virtuoso
posted Hide Post
There should not be any quotes around the field name. WHERE TOTAL lastbilldate EQ compare_date is a valid clause. Don't forget the TOTAL keyword in the WHERE clause. Remove the NOPRINT on the COMPUTE statement so you can see the value calculated for compare_date. Turn on SQL traces so you can see the SQL query generated by the WebFOCUS code.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Guru
posted Hide Post
Dan,
Thanks again for helping out. I agree with you that it is a valid clause. However, I am getting two different results when I run SQL code for example:
  
select schoolid, COUNT(*) from stage_ChildDetail_POS 
WHERE weekendingdate >= '2010/09/24' AND (LastBillDate < '2010/09/03') 
Group by schoolid 
HERE is FOCUS CODE:
JOIN
 INNER STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.SCHOOLID IN
STAGE_CHILDDETAIL_POS TO UNIQUE DIM_SCHOOL.DIM_SCHOOL.SCHOOLID IN DIM_SCHOOL
 TAG J0 AS J0
 END
TABLE FILE STAGE_CHILDDETAIL_POS
SUM
     COMPUTE compare_date/HYYMD = HADD(MAX.WEEKENDINGDATE, 'DAY', -21, 8, 'HYYMD'); NOPRINT

     'CNT.STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.CHILDLASTNAME'
 AS 'COUNT,STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.CHILDLASTNAME'
 -* Highest 1 weekend is 9/24/2010 and compare value 9/24 - 21 = 09/03
BY HIGHEST 1 'STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.WEEKENDINGDATE'
BY 'STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.SCHOOLID' AS 'School ID'
WHERE SCHOOL_STATUS IN ('I','O');
WHERE TOTAL LASTBILLDATE LE compare_date



WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Guru
posted Hide Post
Or is there a way to get last fridays date and compare against becasue my MAX.weekend will alwayse be last friday.


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Guru
posted Hide Post
Okay I am able to figure it out using the LASTFRIDAY as MAX weekending date. So I used last fridays date
 -SET &adj   = DECODE &dowk( 'SUN' 2 'MON' 3 'TUE' 4 'WED' 5 'THU' 6 'FRI' 0 'SAT' 1);
-SET &adj       = (-1) * &adj ;
-SET &LASTDAY   = AYMD( &YYMD, &adj , 'I8YYMD');
-SET &NEEDED = AYMD(&LASTDAY, -21, 'I8YYMD'); 

AND I defined my LASTBILLDATE as
NEWDATE = HDATE(lASTBILLDATE, 'YYMD')
 and
SUM
CNT.FIELD
BY School
BY HIGHEST 1 WEEKENDINGFIELD
-* Since I couldnt do date LE, or GE, or EQ on DATE time to YYMD formate. Is this the best way to do it? It works though.
WHERE NEWDATE LE &NEEDED
 

I thank you DAN for helping me. Looking at your example gave me idea of how to minus dates. One of your PAST posts gave me idea about how to get DOW. Thanks!


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] BIlldate =MAX.WEEKENDDATE - 21DAYS

Copyright © 1996-2020 Information Builders