Solved

Crystal Reports, 9, Formula Assistance

Posted on 2007-12-04
5
2,411 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
  • 3
  • 2
5 Comments
 
LVL 100

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 100

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now