@Age Formula (Ken Hamady) Suppress Formula help needed!

Tech2377 · Jan 2, 2008

I am using Ken Hamady's Age Formula (

http://www.kenhamady.com/form06.shtml):

Code:

WhileReadingRecords;
DateVar Birth:= date({SB1001_main.PatientBirthdate});   // Replace this with your field for Date Of Birth
DateVar Ann := date({SB1001_main.ApptStart});  // Replace this with CurrentDate to get their age as of the time of the report
                                                            //or the date field of an event to get their age as of the time of that event.
if (Month(Ann) * 100) + Day (Ann) >=(Month(Birth) *100) + Day (Birth)  
then Year (Ann) - Year(Birth)  
else Year (Ann) - Year(Birth) -1

I need some help on a report. My report currently has 5 detail bands (A - E) and each utilize the @Age formula. Its seems the weeks and months at certain levels trigger the wrong form. I just came across an 8 week old and a 7 month old that were incorrect and printing the 7-18 yr forms. I am a bit stumped as to where the defining problem is. Perhaps I should rework this entirely to pull the right form. Any questions, please ask. I appreciate any help!

Birth to 6 yrs Old. (Details A)

Code:

ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'M'
OR 

ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'F'
OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'F'
OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'M'

Female 7 - 18 yrs old (Details B)

Code:

ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'M'
OR 

ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'F'
OR

(ToNumber({@Age}) in (0 to 6))
OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'M'

Male 7 - 18 yrs old (Details C)

Code:

ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'M'
OR 

ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'F'
OR

(ToNumber({@Age}) in (0 to 6))
OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'F'

Adult Female (over 18 yrs old) (Details D)

Code:

ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'M'
OR 

(ToNumber({@Age}) in (0 to 6))
OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'M'

OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'F'

Adult Male (over 18 yrs old) (Details E)

Code:

ToNumber({@Age}) >= 19 and {SB1001_main.PatientSex} = 'F'
OR 

(ToNumber({@Age}) in (0 to 6))
OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'M'

OR

(ToNumber({@Age}) in (7 to 18)) and {SB1001_main.PatientSex} = 'F'

lbass · Jan 2, 2008

You don't need tonumber() as it is already a number, and you are overcomplicating this. Try the following suppression formulas:

det_a:
{@Age} > 6

det_b:
{SB1001_main.PatientSex} = 'M' or
{@Age} < 7 or
{@Age} > 18

det_c:
{SB1001_main.PatientSex} = 'F' or
{@Age} < 7 or
{@Age} > 18

det_d:
{SB1001_main.PatientSex} = 'M' or
{@Age} < 19

det_e:
{SB1001_main.PatientSex} = 'F' or
{@Age} < 19

The reason you were getting in trouble with your formulas was because you weren't setting off your "or" statements with parens to clarify what "and/or" clauses "go together."

-LB

Tech2377 · Jan 2, 2008

LBass:

I set up a male patient with a DOB = 11/01/2007. This made his age = 8 weeks. For some reason, it pulled the Male 7-18 yr old detail band (details C). It should have pulled Details A tab. Any thoughts?

lbass · Jan 2, 2008

Ken's formula doesn't look at weeks or months of age, so how did you arrive at {@age} = 8 weeks? This is not a problem with the suppression formula, but with your age formula.

-LB

Tech2377 · Jan 2, 2008

Wow ... I guess if his formula isnt pulling Weeks or months, then using his formula wont help me too much. I am calculating Age in the Stored Procedure (SQL). Please see the below section (specific to Age). Do you think I could use this and have the suppress statements altered?

Code:

Declare @ApptDate datetime
Select @ApptDate = a.ApptStart
FROM    Appointments a
WHERE   a.AppointmentsId = @AppointmentsId

SELECT  '290.PatientName'=IsNull(pp.First,'') + ' ' + isnull(pp.Middle,'') + ' ' + isnull(pp.Last,'')+ ' ' + isnull(pp.Suffix,''), 
        '291.PatLast'=IsNull(pp.Last,''),
        '292.PatFirst'=IsNull(pp.First,''),
        '293.PatMiddle'=IsNull(pp.Middle,''),
        '294.PatientAddr1'=IsNull(pp.Address1,''),
        '295.PatientAddr2'=IsNull(pp.Address2,''),
        '296.PatientCity'=IsNull(pp.City,''),
        '297.PatientState'=IsNull(pp.State,''),
        '298.PatientZip'=IsNull(pp.Zip,''),
		'299.PatientCountry' = ISNULL(pp.Country,''),
        '300.PatientBirthdate' = pp.Birthdate,
        '301.PatientSex'=IsNull(pp.Sex,''), 
        '302.PatientPhone1'=IsNull(pp.Phone1,''),
        '303.PatientSSN'=IsNull(pp.SSN,''),
        '304.PatOccupation'=IsNull(pp.EmpOccup,''),
        '305.PatSchool'=IsNull(pp.MedicalRecordNumber,''),
        '306.PatBudget'=IsNull(g.Budget,0),
        '307.PatientSameAsGuarantor'=IsNull(pp.PatientSameAsGuarantor,0),
		'308.PatSuffix'=IsNull(pp.Suffix,''),
		'400.PatientAge' = CASE	WHEN datediff(day, pp.birthdate, @ApptDate) <= 6 THEN cast(datediff(day, pp.birthdate, @ApptDate) as varchar(10)) + ' dys'
			WHEN datediff(day, pp.birthdate, @ApptDate) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 7) as varchar(10)) + ' wks'
			WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) as varchar(10)) + ' mos'
			WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) > day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) - 1 as varchar(10)) + ' mos'
			WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) as varchar(10)) + ' yrs'
			ELSE '' END
FROM    PatientProfile pp 
		LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
WHERE	pp.PatientProfileID = @PatientProfileId

lbass · Jan 2, 2008

Please show a sample of how the age displays if you place the age field in the detail section.

-LB

Tech2377 · Jan 2, 2008

If I displayed the age field in the detail section for my test patient (DOB 11/01/2007) it would display as: "8 wks

lbass · Jan 2, 2008

I mean for you to show a sample of multiple results spanning the range of ages.

-LB

lbass · Jan 2, 2008

Actually, I think you could convert the age like this:

//{@AgeinYrs}:
if right({age},3) <> 'yrs' then
0 else
truncate(val({age}))

Then use {@AgeinYrs} in my earlier suppression formulas instead of {@Age}.

-LB

Tech2377 · Jan 2, 2008

LBass:

I created a new formula @AgeinYrs

Code:

//{@AgeinYrs}:
if right({SB1001_main.PatientAge},3) <> 'yrs' then
0 else
truncate(val({SB1001_main.PatientAge}))

Then in each of the detail sections I replaced {@Age} with {AgeinYrs}. With my prior patient (my test patient), it still pulls the MALE 7-18 YR OLD detail band (Details C) and it should have pulled Birth to 6 yrs Old. (Details A).

lbass · Jan 2, 2008

Then please show how your test patient appears in {SB1001_main.PatientAge}.

-LB

Tech2377 · Jan 3, 2008

If I displayed the age field in the detail section for my test patient (DOB 11/01/2007) it would display as: "8 wks"

I wonder if I should create something different in SQL to make this easier ... ?

lbass · Jan 3, 2008

If it did display that way, then it would show 0 for {@Age inYrs} and the suppression formula would work correctly, so perhaps you are entering your test record into the database incorrectly?

-LB

Tech2377 · Jan 3, 2008

lbass:

I went in a re-formatted the SQL:

Code:

Declare @ApptDate datetime
Select @ApptDate = a.ApptStart
FROM    Appointments a
WHERE   a.AppointmentsId = 1095

SELECT  '290.PatientName'=IsNull(pp.First,'') + ' ' + isnull(pp.Middle,'') + ' ' + isnull(pp.Last,'')+ ' ' + isnull(pp.Suffix,''),
        '291.PatLast'=IsNull(pp.Last,''),
        '292.PatFirst'=IsNull(pp.First,''),
        '293.PatMiddle'=IsNull(pp.Middle,''),
        '294.PatientAddr1'=IsNull(pp.Address1,''),
        '295.PatientAddr2'=IsNull(pp.Address2,''),
        '296.PatientCity'=IsNull(pp.City,''),
        '297.PatientState'=IsNull(pp.State,''),
        '298.PatientZip'=IsNull(pp.Zip,''),
        '299.PatientCountry' = ISNULL(pp.Country,''),
        '300.PatientBirthdate' = pp.Birthdate,
        '301.PatientSex'=IsNull(pp.Sex,''),
        '302.PatientPhone1'=IsNull(pp.Phone1,''),
        '303.PatientSSN'=IsNull(pp.SSN,''),
        '304.PatOccupation'=IsNull(pp.EmpOccup,''),
        '305.PatSchool'=IsNull(pp.MedicalRecordNumber,''),
        '306.PatBudget'=IsNull(g.Budget,0),
        '307.PatientSameAsGuarantor'=IsNull(pp.PatientSameAsGuarantor,0),
        '308.PatSuffix'=IsNull(pp.Suffix,''),
        '400.PatientAge' = CASE    
			WHEN datediff(day, pp.birthdate, @ApptDate) <= 6 THEN cast(datediff(day, pp.birthdate, @ApptDate) as varchar(10)) + ' dys'
            WHEN datediff(day, pp.birthdate, @ApptDate) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 7) as varchar(10)) + ' wks'
            WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) as varchar(10)) + ' mos'
            WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) > day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) - 1 as varchar(10)) + ' mos'
            WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) as varchar(10)) + ' yrs'
            ELSE '' END,
        CASE When Year(@ApptDate - pp.birthdate - 1) % 100 < 7 Then 'A'
             When Year(@ApptDate - pp.birthdate - 1) % 100 < 18 And pp.Sex = 'F' Then 'B'
             When Year(@ApptDate - pp.birthdate - 1) % 100 < 18 And pp.Sex = 'M' Then 'C'
             When pp.Sex = 'F' Then 'D'
             When pp.Sex = 'M' Then 'E'
             Else 'Unknown' End As [401.PatientAgeGroup]
             
FROM    PatientProfile pp
        LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
WHERE    pp.PatientProfileID = 319

So now I am getting a "A", "B", "C", "D" or "E" based on the prior setup. Can you advise me on the suppress statements?

lbass · Jan 3, 2008

This seems self-explanatory.

det_a:
{PatientAgeGroup} <> "A"

det_b:
{PatientAgeGroup} <> "B"

etc.

-LB

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

@Age Formula (Ken Hamady) Suppress Formula help needed!

Tech2377

Programmer

lbass

Technical User

Tech2377

Programmer

lbass

Technical User

Tech2377

Programmer

lbass

Technical User

Tech2377

Programmer

lbass

Technical User

lbass

Technical User

Tech2377

Programmer

lbass

Technical User

Tech2377

Programmer

lbass

Technical User

Tech2377

Programmer

lbass

Technical User

Similar threads

Part and Inventory Search

Sponsor