Focal Point
Birthdate Conversion for Age in years months or days
December 20, 2007, 03:29 PM
LOgleBirthdate Conversion for Age in years months or days
I need to create a field called AGE based on the Birthdate Field. Here is what I have so far.The way I have this now the define is calling the field AGE Dt and Printing a Date in the field that is 4 digit yr 2 digit mo and 2 digit day. I need the field to show the Age in Years based off of the Birthdate field. Suggestions? For example if my birthdate is 19640723 I want the Age field to show the age in years 43 or if less than 1 year I need it to show how many months or days old. How can I do this?
JOIN
OI_CHARGES.OI_CHARGES.MSR_NBR IN OI_CHARGES TO MULTIPLE MSR.MSR.MSR_NBR IN MSR
AS J0
END
JOIN
MSR.MSR.MSR_PAT_MPI_SET AND MSR.MSR.MSR_PAT_MPI_NBR IN OI_CHARGES TO MULTIPLE
MPI_PERSON.MPI_PERSON.MPI_SET AND MPI_PERSON.MPI_PERSON.MPI_NBR IN MPI_PERSON
AS J1
END
DEFINE FILE OI_CHARGES
CURRENTDT = TODAY(CURRENTDT);
AGE = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'Y');
END
TABLE FILE OI_CHARGES
PRINT
'OI_CHARGES.OI_CHARGES.RESP_GUAR_NBR'
'OI_CHARGES.OI_CHARGES.DT_OF_SERVICE'
'MPI_PERSON.MPI_PERSON.BIRTHDAY'
'MPI_PERSON.MPI_PERSON.ETHNIC_ORIGIN_CD'
'OI_CHARGES.OI_CHARGES.PAT_MPI_NBR'
'OI_CHARGES.OI_CHARGES.PAT_NBR'
'OI_CHARGES.OI_CHARGES.MSR_NBR'
'MSR.MSR.MSR_NBR'
'MPI_PERSON.MPI_PERSON.MPI_NBR'
'MPI_PERSON.MPI_PERSON.SSN'
AGE
HEADING
""
FOOTING
""
WHERE OI_CHARGES.OI_CHARGES.DT_OF_SERVICE GE 20070101;
WHERE OI_CHARGES.OI_CHARGES.APPL_SOURCE NOT LIKE 'EC';
ON TABLE NOTOTAL
END
V. 762
Windows XP
December 20, 2007, 03:50 PM
GlendaFirst convert the birthdate to YYYYMM format and then divide by 12.
Here it is stepped out (BDATEI is formatted as I8.):
BDATE/I4YM = BDATEI/100;
NOWDATEI/I8 = &YYMD;
NOWDATE/I4YM = NOWDATEI/100;
AGEM/I4 = YM(BDATE, NOWDATE, 'I4');
AGE/I4 = AGEM/12;
Here is the short cut:
AGE/I4 = YM(BDATEI/100, &YYMD/100, 'I4')/12;
Glenda
In FOCUS Since 1990
Production 8.2 Windows
December 20, 2007, 03:51 PM
Francis MarianiDEFINE FILE CAR
BDATE WITH COUNTRY/YYMD = '1970/03/15';
TDATE/YYMD = '&DATEYYMD';
END
TABLE FILE CAR
PRINT
BDATE
COMPUTE AGE_Y/I8 = DATEDIF(BDATE, TDATE, 'Y');
COMPUTE AGE_M/I8 = DATEDIF(BDATE, TDATE, 'M');
COMPUTE AGE_D/I8 = DATEDIF(BDATE, TDATE, 'D');
COUNTRY NOPRINT
HEADING
"WEBFOCUS REPORT"
WHERE READLIMIT EQ 100
WHERE RECORDLIMIT EQ 100
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLESHEET *
BORDER=1, BORDER-COLOR=SILVER,
FONT='ARIAL', SIZE=8, $
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 20, 2007, 04:15 PM
wf1998Try using following code in DEFINE Request..
CURRENTDT = TODAY(CURRENTDT);
AGE_Y = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'Y');
AGE_M = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'M');
AGE_D = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'D');
AGE = IF AGE_Y GE 1 THEN AGE_Y |' Years'
- ELSE IF AGE_M GE 1 THEN AGE_M|' Months'
- ELSE AGE_D|' Days';
Env Prod:WebFOCUS 7702 ,Windows xp on 64, SQL Server 2008, IRF Tool
Env 1 Local: DevStudio 7702 - MS Windows XP SP2 - Apache Tomcat 5.0.28
Output: HTML, Excel and PDF
December 21, 2007, 09:41 AM
LOgleI tried as follows and it is still not working. Can you show me using this code where it should be? I know I have to declare it like in sql and I am assuming define and declare are the same thing but I don't get how to do it in WebFocus. Can I use a SQL Statement in the middle of the WebFocus Statement and have it work or if I start in one language do I need to continue on in that language?
JOIN
OI_CHARGES.OI_CHARGES.MSR_NBR IN OI_CHARGES TO MULTIPLE MSR.MSR.MSR_NBR IN MSR
AS J0
END
JOIN
MSR.MSR.MSR_PAT_MPI_SET AND MSR.MSR.MSR_PAT_MPI_NBR IN OI_CHARGES TO MULTIPLE
MPI_PERSON.MPI_PERSON.MPI_SET AND MPI_PERSON.MPI_PERSON.MPI_NBR IN MPI_PERSON
AS J1
END
DEFINE MPI_PERSON.MPI_PERSON.BIRTHDAY
CURRENTDT = TODAY(CURRENTDT);
AGE_Y = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'Y');
AGE_M = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'M');
AGE_D = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'D');
AGE = IF AGE_Y GE 1 THEN AGE_Y |' Years'
- ELSE IF AGE_M GE 1 THEN AGE_M|' Months'
- ELSE AGE_D|' Days';
END
TABLE FILE OI_CHARGES
PRINT
'OI_CHARGES.OI_CHARGES.RESP_GUAR_NBR'
'OI_CHARGES.OI_CHARGES.DT_OF_SERVICE'
'MPI_PERSON.MPI_PERSON.BIRTHDAY'
'MPI_PERSON.MPI_PERSON.ETHNIC_ORIGIN_CD'
'OI_CHARGES.OI_CHARGES.PAT_MPI_NBR'
'OI_CHARGES.OI_CHARGES.PAT_NBR'
'OI_CHARGES.OI_CHARGES.MSR_NBR'
'MSR.MSR.MSR_NBR'
'MPI_PERSON.MPI_PERSON.MPI_NBR'
'MPI_PERSON.MPI_PERSON.SSN'
AGE
HEADING
""
FOOTING
""
WHERE OI_CHARGES.OI_CHARGES.DT_OF_SERVICE GE 20070101;
WHERE OI_CHARGES.OI_CHARGES.APPL_SOURCE NOT LIKE 'EC';
ON TABLE NOTOTAL
END
V. 762
Windows XP
December 21, 2007, 09:48 AM
GinnyJakesThe DEFINE is done against the file, not the field:
DEFINE OI_CHARGES
CURRENTDT = TODAY(CURRENTDT);
AGE_Y = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'Y');
AGE_M = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'M');
AGE_D = DATEDIF(MPI_PERSON.MPI_PERSON.BIRTHDAY,CURRENTDT,'D');
AGE = IF AGE_Y GE 1 THEN AGE_Y |' Years'
- ELSE IF AGE_M GE 1 THEN AGE_M|' Months'
- ELSE AGE_D|' Days';
END
TABLE FILE OI_CHARGES
........
I did not verify the code in the DEFINE but I know you copied it from an expert suggestion above.
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
December 21, 2007, 09:54 AM
GlendaWhat is the format of the date field you are manipulating?
Glenda
In FOCUS Since 1990
Production 8.2 Windows
December 21, 2007, 10:59 AM
wf1998Hi tried on my system againsit CAR file
Here is the code for it..
DEFINE FILE CAR
-*BDATE1 WITH COUNTRY/YYMD = '1970/03/15';
BDATE1 WITH COUNTRY/YYMD = '2007/03/15';
-*BDATE1 WITH COUNTRY/YYMD = '2007/12/02';
CDATE/YYMD = DATECVT(&YYMD,'I8YYMD','YYMD');
AGE_Y/D3 = DATEDIF(BDATE1,CDATE,'Y');
AGE_M/D3 = DATEDIF(BDATE1,CDATE,'M');
AGE_D/D3 = DATEDIF(BDATE1,CDATE,'D');
AGE/A30 = IF AGE_Y GE 1 THEN FTOA(AGE_Y, '(D3)', 'A3') |' Years'
ELSE IF AGE_M GE 1 THEN FTOA(AGE_M, '(D3)', 'A3') |' Months'
ELSE FTOA(AGE_D, '(D3)', 'A3') |' Days';
END
-RUN
TABLE FILE CAR
PRINT
CDATE
BDATE1
AGE
COUNTRY NOPRINT
HEADING
"WEBFOCUS REPORT"
WHERE READLIMIT EQ 10
WHERE RECORDLIMIT EQ 10
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLESHEET *
BORDER=1, BORDER-COLOR=SILVER,
FONT='ARIAL', SIZE=8, $
END
both the Birth day and Current should be in same Date Format.YYMD, if your Date is in different format use DATECVT function convert in to YYMD, and then use it DATEDIFF function,i guess it will work for you.
Env Prod:WebFOCUS 7702 ,Windows xp on 64, SQL Server 2008, IRF Tool
Env 1 Local: DevStudio 7702 - MS Windows XP SP2 - Apache Tomcat 5.0.28
Output: HTML, Excel and PDF
December 21, 2007, 12:14 PM
Leahquote:
TODAY/YYMD = &YYMD;
BDATE/YYMD = AA010;
AGE/I3 = ((TODAY - BDATE)/365.25);
Our birthdate is stored in a DB2 date field we access as AA010. The above is what we use to calculate the age in a define, or you could put in as computes as well.
Note the BDATE could probably have been eliminated, this is really old code.
Leah
December 21, 2007, 12:41 PM
wf1998my understanding is that, he has to display only
Years or
Months or
Days only as per the age not entire age..
Pl. correct me if i missed..
Env Prod:WebFOCUS 7702 ,Windows xp on 64, SQL Server 2008, IRF Tool
Env 1 Local: DevStudio 7702 - MS Windows XP SP2 - Apache Tomcat 5.0.28
Output: HTML, Excel and PDF
December 21, 2007, 02:25 PM
FrankDutchThe code I would use is
DEFINE FILE XXX
TODAY/YYMD = '&DATEYYMD'
AGE_Y = DATEDIF(BIRTHDAY,TODAY,'Y');
AGE_M = DATEDIF(BIRTHDAY,TODAY,'M');
AGE_D = DATEDIF(BIRTHDAY,TODAY,'D');
AGE/A10 = IF AGE_Y GE 1 THEN EDIT(AGE_Y)|' Years'
- ELSE IF AGE_M GE 1 THEN EDIT(AGE_M)|' Months'
- ELSE EDIT(AGE_D)|' Days';
NEXTBIRTHDAY/YYMD=DATEADD(BIRTHDAY, 'Y', AGE_Y+1);
END
TABLE FILE XXX
PRINT
NAME
BIRTHDAY
AGE
NEXTBIRTHDAY
BY SSN
END
This is based upon the suggestions from others, but there seems sometimes a mix of SET commands and DEFINE commands.
If you want to concatenate numbers and strings you can not just do it as above in a DEFINE, although this works in the "-" SET part of the reports.
I hope this will work for you
Happy Holidays.
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
December 21, 2007, 02:29 PM
GlendaFor this example to work your date field must be formatted as "I8". If it is a different format, convert it first. (Change the date below and rerun for different dates.
-SET &BDATE = 19640723;
DEFINE FILE CAR
BDATE/I8 = &BDATE;
YEARS/I4 = YM(BDATE/100, &YYMD/100, 'I4')/12;
MONTHS/I9 = YM(BDATE/100, &YYMD/100, 'I9');
DAYS/I9 = YMD(BDATE, &YYMD);
AGEZEROS/A20 = IF YEARS GT 1 THEN EDIT(YEARS)||(' YEARS') ELSE
IF MONTHS GT 0 THEN EDIT(MONTHS)||(' MONTHS') ELSE EDIT(DAYS)||(' DAYS');
AGE/A20 = TRIM('L', AGEZEROS, 20, '0', 1, 'A20');
END-*
TABLE FILE CAR
PRINT BDATE
YEARS
MONTHS
DAYS
AGE
COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
END
Results:
PAGE 1
BDATE YEARS MONTHS DAYS AGE
19640723 43 521 15856 43 YEARS
Glenda
In FOCUS Since 1990
Production 8.2 Windows