Solved

Crystal Reports, 9, Formula Assistance

Posted on 2007-12-04
5
2,430 Views
Last Modified: 2010-04-21
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'

Open in new window

0
Comment
Question by:Jeff S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 101

Expert Comment

by:mlmcc
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

by:Jeff S
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

by:
mlmcc earned 500 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

by:Jeff S
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,''),
        '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

Open in new window

0
 
LVL 7

Author Closing Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

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

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

Join & Ask a Question