asked on

Formatting Case Statement for use in formula

I am trying to create a formula that checks two date fields and calculate the dates. The field will be null if no date was entered and a blank string if it is set then removed.

SQL Code snippet Used in a query:

CASE
           WHEN(bp.ReleaseTime IS NULL
                OR bp.ReleaseTime = '')
           THEN DATEDIFF(d, bp.BookedDateTime, GETDATE())
           WHEN bp.ReleaseTime IS NOT NULL
           THEN DATEDIFF(d, bp.BookedDateTime, bp.ReleaseTime)
           ELSE ''
       END AS 'Days Incarcerated'

Open in new window

Crystal Reports Formula:

SELECT {BookedPerson.ReleaseTime}
CASE ISNULL (({BookedPerson.ReleaseTime}) OR {BookedPerson.ReleaseTime} = ''): DATEDIFF(d, {BookedPerson.BookedDateTime}, GETDATE())
CASE IS {BookedPerson.ReleaseTime} IS NOT NULL: DATEDIFF(d, {BookedPerson.BookedDateTime}, {BookedPerson.ReleaseTime})
DEFAULT: ''

Open in new window

MikeM670

ASKER

I changed tactics and tried this:

If IsNull({BookedPerson.ReleaseTime}) Or 
   InStr(ToText({BookedPerson.ReleaseTime},"MM/dd/yyyy"), " ") = 0 Then
   DATEDIFF("d", {BookedPerson.BookedDateTime}, CurrentDate())
Else
   DATEDIFF("d", {BookedPerson.BookedDateTime}, {BookedPerson.ReleaseTime})

Open in new window

but my number of days are way off.

Ryan Chong

To make the thing simpler, perhaps you can do all the calculation/ comparison at SQL and just display the values in Crystal Reports?

btw, what's the data type of field: bp.ReleaseTime ?

MikeM670

ASKER

Both the db.ReleasedTime and db.BookedDateTime are datetime fields set to a default value of null.

Ryan Chong

maybe you can try this in SQL:

DATEDIFF(d, bp.BookedDateTime, CASE WHEN bp.ReleaseTime IS NULL THEN GETDATE() ELSE bp.ReleaseTime END) AS 'Days Incarcerated'

Open in new window

MikeM670

ASKER

I need to do this in crystal. I already can do it in sql but not calling any sql command stuff here.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeM670

ASKER

That did work properly. However I'm concerned that the user may mark the releasetime field then remove that entry due to a mistake and that would not make the field null again but leave it as a empty string. I check a bunch of databases from various agencies and don't see any of those empty strings but just want to make sure I cover that possibility.

I tried

If IsNull({BookedPerson.ReleaseTime}) or {BookedPerson.ReleaseTime} = '' Then
DATEDIFF("d", {BookedPerson.BookedDateTime}, CurrentDate())
Else
DATEDIFF("d", {BookedPerson.BookedDateTime}, {BookedPerson.ReleaseTime})

but get an error stating a datetime is required here.... {BookedPerson.ReleaseTime} = ''

SOLUTION

Partha Mandayam

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeM670

ASKER

Partha,

Thank you that worked perfectly.