calculating age in crystal command table?
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22293
Printed Date: 28 Apr 2024 at 9:11am
Topic: calculating age in crystal command table?
Posted By: cbaldwin
Subject: calculating age in crystal command table?
Date Posted: 14 Apr 2017 at 8:39am
Im my Crystal Command table i select the two following fields. d.birthdate and dd.draw_start_datetime. These are both Datetime fields.
I am trying to calculate within the select statement of my crystal command table the age at time of draw.
dd.draw_start_datetime - d.birthdate/365.25 as age. This works but i get this crazy long field with data down to micro seconds or some such thing. I just want the age in years. So if the person was 16 and 9 months at the time of visit i want the age to equal 16.
I have looked and read and read and looked and tried and i am not getting anywhere with this. Suggestions???
My current Command table select statement is as follows:
SELECT
dd.draw_id,
dd.visit_id,
dd.donor_id,
dd.actual_collection_type_cd,
dd.draw_start_datetime,
dd.draw_status_cd,
d.birthdate,
((sysdate-d.birthdate)/365) as age,
dvqt.donor_qual_test_id,
dvqt.donor_qual_test_result
FROM Donor_Draw dd
LEFT JOIN Donor d on dd.donor_id=d.donor_id
LEFT JOIN Donor_Visit_Qual_Test dvqt on dd.visit_id=dvqt.visit_id
WHERE dd.draw_start_datetime>=sysdate-364 and
dd.actual_collection_type_cd IN ('SUR ALYX','ALYX','WB 480','WB ALLO','WB AUTO480','WB AUTOADJ','WB AUTOLOW','WB HH','WB THER') and
dd.draw_status_cd='C' and
dvqt.donor_qual_test_id='HEMGLB'
chuck
Note that i am using SYSDATE and birthdate in my select statement above to see if i can figure out the syntax.
|
Replies:
Posted By: lockwelle
Date Posted: 19 Apr 2017 at 11:17am
have you tried DateDiff?
I would try: DateDiff("year",birthday, rundate)
if you get a negative number, reverse rundate and birthday.
another option might be to just divide by 365...the deicmal might imnply that you want a float (at least it does in SQL)
HTH
|
Posted By: cbaldwin
Date Posted: 20 Apr 2017 at 2:44am
|