[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

Crystal Reports, 9, Formula Assistance

Posted on 2007-12-04
Medium Priority
2,441 Views
I need some help on a report. My report currently has 5 detail bands (A - E) and each utilize the @AgeNumber 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!

@AgeNumber Formula
StringVar yeardiff := LEFT(totext(YEAR(CurrentDate)-YEAR({SB1001_main.PatientBirthdate})),LENGTH(totext(YEAR(CurrentDate)-YEAR({SB1001_main.PatientBirthdate})))-3);
StringVar yeardiff_minus1 := LEFT(totext((YEAR(CurrentDate)-YEAR({SB1001_main.PatientBirthdate}))-1),LENGTH(totext((YEAR(CurrentDate)-YEAR({SB1001_main.PatientBirthdate}))-1))-3);
NumberVar birthmonth := MONTH({SB1001_main.PatientBirthdate});
NumberVar currentmonth := MONTH(CurrentDate);
StringVar monthdiff := LEFT(totext(MONTH(CurrentDate)-MONTH({SB1001_main.PatientBirthdate})),LENGTH(totext(MONTH(CurrentDate)-MONTH({SB1001_main.PatientBirthdate})))-3);
NumberVar birthday := DAY({SB1001_main.PatientBirthdate});
NumberVar currentday := DAY(CurrentDate);
StringVar daysdiff := LEFT(TOTEXT(CurrentDate - {SB1001_main.PatientBirthdate}),LENGTH(TOTEXT(CurrentDate - {SB1001_main.PatientBirthdate}))-3);
//StringVar noweeks := LEFT(TOTEXT(ROUND(ToNumber(daysdiff)/7,0)),LENGTH(TOTEXT(ToNumber(daysdiff)/7))-3);
//StringVar nomonths := LEFT(TOTEXT(ROUND(ToNumber(daysdiff)/30,0)),LENGTH(TOTEXT(ToNumber(daysdiff)/30))-3);

StringVar noweeks := LEFT(TOTEXT(Truncate(ToNumber(daysdiff)/7,0)),LENGTH(TOTEXT(Truncate(ToNumber(daysdiff)/7,0)))-3);
//StringVar nomonths := LEFT(TOTEXT(Truncate(ToNumber(daysdiff)/30,0)),LENGTH(TOTEXT(Truncate(ToNumber(daysdiff)/30,0)))-3);
NumberVar diffinmonths := DATEDIFF('m', date({SB1001_main.PatientBirthdate}), CurrentDate);
NumberVar diffindays := DATEDIFF('d', DATEADD('m', diffinmonths, date({SB1001_main.PatientBirthdate})), CurrentDate);

IF diffindays < 0 THEN
diffinmonths := diffinmonths - 1;
StringVar nomonths := LEFT(TOTEXT(Truncate(ToNumber(diffinmonths),0)),LENGTH(TOTEXT(Truncate(ToNumber(diffinmonths),0)))-3);

StringVar nomonths_plus12 := LEFT(TOTEXT(12 + MONTH({SB1001_main.PatientBirthdate})),LENGTH(TOTEXT(12 + MONTH({SB1001_main.PatientBirthdate})))-3);

NumberVar diffone = ROUND(ToNumber(daysdiff)/30, 0);
NumberVar difftwo = Truncate(ToNumber(daysdiff)/31, 0);

IF ToNumber(daysdiff) > 119 AND ToNumber(daysdiff) <= 730 THEN
IF diffone <> difftwo THEN
nomonths := LEFT(TOTEXT(Truncate(ToNumber(daysdiff)/31,0)),LENGTH(TOTEXT(Truncate(ToNumber(daysdiff)/31,0)))-3);

IF ToNumber(daysdiff) >= 0 AND ToNumber(daysdiff) <= 6 THEN
daysdiff
ELSE
IF ToNumber(daysdiff) > 6 AND ToNumber(daysdiff) <= 119 THEN
noweeks
ELSE
IF ToNumber(daysdiff) > 119 AND ToNumber(daysdiff) <= 730 THEN
nomonths
ELSE
IF ToNumber(daysdiff) > 365  AND ToNumber(daysdiff) <= 730 THEN
nomonths_plus12
ELSE
IF ToNumber(daysdiff) > 730 AND birthmonth = currentmonth  AND currentday > birthday THEN
yeardiff
ELSE
IF ToNumber(daysdiff) > 730 AND birthmonth = currentmonth  AND currentday < birthday THEN
yeardiff_minus1
ELSE
IF ToNumber(daysdiff) > 730 AND birthmonth = currentmonth  AND currentday = birthday THEN
yeardiff
ELSE
IF ToNumber(daysdiff) > 730 AND birthmonth > currentmonth  THEN
yeardiff_minus1
ELSE
IF ToNumber(daysdiff) > 730 AND birthmonth < currentmonth  THEN
yeardiff

``````Birth to 6 yrs Old. (Details A)
ToNumber({@AgeNumber}) >= 19 and {SB1001_main.PatientSex} = 'M'
OR

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

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

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

Female 7 - 18 yrs old (Details B)
ToNumber({@AgeNumber}) >= 19 and {SB1001_main.PatientSex} = 'M'
OR

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

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

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

Male 7 - 18 yrs old (Details C)
ToNumber({@AgeNumber}) >= 19 and {SB1001_main.PatientSex} = 'M'
OR

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

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

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

Adult Female (over 18 yrs old) (Details D)
ToNumber({@AgeNumber}) >= 19 and {SB1001_main.PatientSex} = 'M'
OR

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

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

OR

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

Adult Male (over 18 yrs old) (Details E)
ToNumber({@AgeNumber}) >= 19 and {SB1001_main.PatientSex} = 'F'
OR

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

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

OR

(ToNumber({@AgeNumber}) in (7 to 18)) and {SB1001_main.PatientSex} = 'F'
``````
0
Question by:Jeff S
• 3
• 2

LVL 101

Expert Comment

ID: 20407562
Rather than trying to work through your formula, what are you trying to do?

What numbers do you need from the formula?
Years, months, weeks, etc?

What does your data look like?
Is it a number, string or date field?

mlmcc
0

LVL 7

Author Comment

ID: 20408383
Cut and dry, this report was formatted by an ex-employee and i am trying to fix it . What I need is to have it pull certain detail bands when a criteria is met (age and sex). I have coded in SQL the patients age ... and this report is currently pulling in the sex. Maybe with your help I can part with this @AgeNumber formula all together and edit the suppress statements?!
0

LVL 101

Accepted Solution

mlmcc earned 2000 total points
ID: 20416979
Let me look at it in more detail.  Does the formula work in most cases?

Do you have some sample data that I could test with (even just the age or date field and the sex field)

What data types are the fields?

mlmcc
0

LVL 7

Author Comment

ID: 20450760
Ok this formula was giving me some nasty headaches. So I went into SQL and coded Age. I have attached a section of this Stored Procedure and attached it below.  I need 5 Suppress statements now that pull based off gender and age ranges (if possible).
``````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,''),
'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
``````
0

LVL 7

Author Closing Comment

ID: 31412716
Thanks for looking and helping ... your tips helped
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Suggested Courses
Course of the Month17 days, 14 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.