Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Report Design
Message Icon Topic: calculating age in crystal command table? Post Reply Post New Topic
Author Message
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Location: United States
Online Status: Offline
Posts: 63
Quote cbaldwin Replybullet Topic: calculating age in crystal command table?
    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.

Edited by cbaldwin - 14 Apr 2017 at 8:46am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4267
Quote lockwelle Replybullet 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
IP IP Logged
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Location: United States
Online Status: Offline
Posts: 63
Quote cbaldwin Replybullet Posted: 20 Apr 2017 at 2:44am
Thanks. I will try that.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.