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     [CLOSED] Determine Date Between Two Dates

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Determine Date Between Two Dates
 Login/Join
 
bsmith
Member
posted
I have two files. FileA contains EMP_ID, BEGIN_DATE, END_DATE, and PAY_RATE. FileB contains EMP_ID, SICK_DATE, SICK_HOURS. The EMP_ID in both files are the same. The BEGIN_DATE and END_DATE represent personnel actions such as promotions, demotions, etc. The SICK_DATE field is the days the employee used sick leave. I need to determine what an employee PAY_RATE was on a specific date.

FileA
EMP_ID BEGIN_DATE END_DATE PAY_RATE
12476 20070104 20080202 34,987
12476 20060805 20070103 33,857
12476 20060623 20060804 33,857
12476 20050618 20060622 31,675

FileB
EMP_ID SICK_DATE SICK_HOURS
12476 20050620 7
12476 20050621 7
12476 20060721 7


What code can I use that will look at both of these files and determine on 20050620 and 20050621 the employee PAY_RATE was 31,675 and on 20060721 the PAY_RATE was 33,857. The SICK_DATE falls between the BEGIN_DATE AND END_DATE, but I need to see this all on one report.

This message has been edited. Last edited by: Kerry,
 
Posts: 8 | Registered: March 30, 2007Report This Post
GinnyJakes
Expert
posted Hide Post
This should do it.

TABLE FILE FILEA
PRINT PAY_RATE
BY EMP_ID
BY BEGIN_DATE
BY END_DATE
ON TABLE HOLD AS PAYHOLD FORMAT FOCUS INDEX EMP_ID
END
JOIN EMP_ID IN FILEB TO ALL EMP_ID IN PAYHOLD AS J1
TABLE FILE FILEB
PRINT *
WHERE SICK_DATE GE BEGIN_DATE AND SICK_DATE LE END_DATE
END


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
bsmith
Member
posted Hide Post
I was hoping to see the actual dates between the begin_date and end_date. So the report would produce the following results:
12476 20070104 34,987
12476 20070105 34,987
12476 20070106 34,987
etc.
12476 20080202 34,987

Next would be 20060805 to 20070103.
12476 20060805 33,857
12476 20060806 33,857
12476 20060807 33,857
etc.
12476 20070103 33,857

I have a time and attendance file by date that I want to join the information with. My join statement would be EMP_ID | DATE.
 
Posts: 8 | Registered: March 30, 2007Report This Post
ypatel
Silver Member
posted Hide Post
quote:
Originally posted by bsmith:
I was hoping to see the actual dates between the begin_date and end_date. So the report would produce the following results:
12476 20070104 34,987
12476 20070105 34,987
12476 20070106 34,987
etc.
12476 20080202 34,987

Next would be 20060805 to 20070103.
12476 20060805 33,857
12476 20060806 33,857
12476 20060807 33,857
etc.
12476 20070103 33,857

I have a time and attendance file by date that I want to join the information with. My join statement would be EMP_ID | DATE.


Assuming your attendance file has record for each date/day you want in your final result,
You can take GinnyJakes'S solution one step forward to accomplish that.

Generate Hold file as mentioned in previous post

"TABLE FILE FILEA
PRINT PAY_RATE
BY EMP_ID
BY BEGIN_DATE
BY END_DATE
ON TABLE HOLD AS PAYHOLD FORMAT FOCUS INDEX EMP_ID
END
JOIN EMP_ID IN FILEB TO ALL EMP_ID IN PAYHOLD AS J1
TABLE FILE FILEB
PRINT *
WHERE SICK_DATE GE BEGIN_DATE AND SICK_DATE LE END_DATE
ON TABLE HOLD FORMAT FOCUS INDEX EMP_ID
END"

JOIN CLEAR *
JOIN EMP_ID IN ATTENDANCEFILE TO ALL EMP_ID IN HOLD AS J2

TABLE FILE ATTENDANCEFILE
PRINT *
WHERE ATTENDANCE_DATE GE BEGIN_DATE AND ATTENDANCE_DATE LE END_DATE
END.

THIS IS ONE WAY TO INCREASE ROWS TO SHOW EACH DAY IN YOUR ATTENDANCE FILE ALONG WITH PAYRATE, SICK DATE ETC.


-Yogesh Patel
------------------------------------------------------------------------
PROD: WF 764 on Linux Apache tomcat v5.5
DEV: WF 768 on Linux
 
Posts: 42 | Location: Edison, New Jersey | Registered: January 30, 2007Report This Post
Danny-SRL
Virtuoso
posted Hide Post
B_Smith,

Please update your signature.

Your first post says that you only want to see the PAY_RATE for each SICK_DATE. Ginny gave you an example.

Your second post says that you want to create a file with all days for each EMP_ID. This is something the the McGuyver technique does best. I suggest you search the forum for that. If you don't find/understand, come back here and we can give you an example.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report 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     [CLOSED] Determine Date Between Two Dates

Copyright © 1996-2020 Information Builders