Conditional text display based upon 24 hr time range

[Jacque]

Crystal v9
DB2

I'm trying to conditionally display a text string based upon the date an order was discontinued. The report can span multiple days and each 'day' is considered to be any period of time spanning 24 hr. So a day could start at 6/2/05 1:30:00 and end on 6/5/05 1:29:59. Here is my formula but it's not working correctly and I'm not sure how to fix it:

if (date({Table.DATETIME}) = Date({Table.BEGINDATETIME}))
then "Discontinued " + totext({Table.DATETIME},"MM/dd/yy hh:mm")
else
if (date({Table.DATETIME}) > Date({Table.BEGINDATETIME})
and
DateDiff ("s",{Table.DATETIME},{Table.BEGINDATETIME}) < 86400)
then "Discontinued " + totext({Table.DATETIME},"MM/dd/yy hh:mm")
else ""

My date range could be for a 3 day report:
Start Date 6/2/05 01:30:00 End Date 6/5/05 01:29:59

I discontinued an order on 6/3/05 at 08:28:00

My display should look like this:

6/2/05 6/3/05 6/5/05
Discontinued
6/3/05 08:28

but I'm getting this instead:

6/2/05 6/3/05 6/5/05
Discontinued Discontinued
6/3/05 08:28 6/3/05 08:28


Any help would be appreciated, thanks!
Jacque

 

[rosemaryl]

How is the report laid out? Grouping .. etc.

That looks like a manual crosstab, so what would differentiate each row?

What sections is that you are showing in your example?

regards,

ro



Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[Jacque]

Hi Rosemary, thanks for responding.

The data is shown in my original post is being displayed in a detail section.

This report (nightmare actually ) has 6 groupings:

Grp1 ReportSeq
Grp2 Person
Grp3 Type
Grp4 SortKey (rarely used, never null)
Grp5 RelatedOrd (rarely used, never null)
Grp6 OrderNum

GH & GF - 1,4,5,6 suppressed.
GH2 - displays the BeginDate, BeginDate+1 and EndDate
GH3 - displays the Type

Detail a - displays a conditional type - suppressed if "None", section suppressed if empty
Detail b - contains 4 subreports. one for order info, one for day 1, one for day2 and one for day3. Each appearing as if a separate column.
Detail c - is the section where I want to display my conditional text regarding discontinued mm/dd/yy beneath the applicable subreport.

The detail section and the subreports in it are formatted to look similar to a crosstab but since this report 'morphs' into many different configurations based upon user selection, using a crosstab doesn't work for us.

Hope I haven't confused things more.

 

[Jacque]

I figured it out, just in case anyone else needs to do something similar this is what I came up with. There is a slight difference in formulas for each day and for the last day (in my particular case) the table.field name and the variable need to be reversed.
TGIF
Jacque

datetimevar vD1Reference := DateAdd ("d",1,date({Table.BEGINDATETIME}));
datetimevar v1Reference := DateAdd ("s",-1, vD1Reference);

if (datediff("s",{Table.DATETIME},v1Reference)< 86401
and datediff("s",{Table.DATETIME},v1Reference)>0)
then "Discontinued " + totext({Table.DATETIME},"MM/dd/yy hh:mm")

else ""

 

[rosemaryl]

Dear Jacque,

I am happy you figured it out ... and that you posted your solution.

I am sorry that I missed the email notification that you had responded ...

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762