Solved

Crystal Reports, 9, Formula Assistance

Posted on 2007-12-04
5
2,428 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 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.

739 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