Crosstab Help 1

[newbie0423]

I have a report that I'm using a crosstab that shows the hour that a patient leaves the recovery room. I would like to get a count of patient's stay in the recovery room by the hour. For instance, if a patient comes into the recovery room at 7:10pm I would like a count that patient in the 7:00 hour, and if he/she is still in there at 8:25 pm I still would like to count them in the 8:00 hour. I have two formulas @PACUSTART and @PACUEND. In the crosstab I'm doing a count on PACUEND.
Is this something that I should be doing in a crosstab, or should I try to come up with a formula?? I am using Crystal XI

 

[newbie0423]

Yes, it is a number not text.

 

[pmax9999]

OK, so now we are getting somewhere.

Amend the formula I provided previously to the following:

If      Not (    
                Time(Picture(ToText({Table.In_Time},'0000'),"xx:xx"))  > Time(21,0,0) or
                Time(Picture(ToText({Table.Out_Time},'0000'),"xx:xx")) < Time(20,0,0)
            )    
Then    1

Thanks to elsenorjose a day or so ago for providing the quickest and easiest way to convert a numeric time to proper time I have seen.



Pete.

 

[newbie0423]

It works! Thanks so much for all of your time and effort to help me with this. Now I just have to make 24 formulas!!! Thanks Pete.

 

[Madawc]

If you need much the same formula, 24 times, you should be able to duplicate the formula field. If your version of Crystal does not allow duplicates, you can duplicate by doing a paste to a dummy report, changing the name and then pasting back.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[newbie0423]

Hello,

First I'd like to take this opportunity to say thank you for providing me with an excellent solution to my report to capture recovery room times. I have written 24 formulas for each hour that the patient can be n the recovery room. I am having an issue with the formula for 12 midnight. The formula below is how I have it written, but it doesn't seem to play nicely with the other formulas. Let's say I have a patient in at 2050 and out at 0035. When I look at the report none of the hours between 8 and 12 are displaying which I have formulas for. because this crosses to another day,should this formula be written differently? Any help will be greatly apppreciated!
If Not (
Time(Picture(ToText({v_CRA_20_Billing.time_pacu_start},'0000'),"xx:xx")) > Time(00,0,0) or
Time(Picture(ToText({v_CRA_20_Billing.time_pacu_end},'0000'),"xx:xx"))< Time(23,0,0)
)
Then 1

 

[pmax9999]

The issue arises when the time the patient spends in the recovery room spans midnight, meaning that the end time is earlier than the start time. It is something I should have anticipated, so apologies for giving you poor advice.

I have had a bit of a look at it but have had other things that took priority. It is going to be pretty messy but I will try and get something for you as soon as I can (unless you or someone else comes up with a solution before I get back to it).

Cheers
Pete.

 

[newbie0423]

Hi Pete,

Thanks for responding. I will continue to try different things until I hear back from you. Thanks Val

 

[pmax9999]

OK, this has just about done my head in - I think I am getting too old for this. I have tested it fairly thoroughly and it seems to deal with all possibiities I could come up with.

For your formula to count cases between 20:00 and 21:00 use the following code:

WhilePrintingRecords;
Local NumberVar IT := {Table.In_Time};
Local NumberVar OT := {Table.Out_Time};
Local NumberVar Rf := 2000;
Local NumberVar Rt := 2100;

If      {Table.Out_Time} < {Table.In_Time}
Then    OT := OT + 2400
Else    OT := OT;

If      {Table.Out_Time} > {Table.In_Time} and
        Not (    
                OT < Rf or
                IT > Rt
            )
Then    1
Else    
If      {Table.Out_Time} < {Table.In_Time} and
        (
            (IT < Rf and OT > Rf) or
            (IT < Rf and OT > 2400) or
            (IT > Rf and OT < 2400) or
            (IT in [Rf to Rt] and OT > 2400) or
            ({Table.Out_Time} in [Rf to Rt]) or
            (OT > 2400 and {Table.Out_Time} > Rt)
        )
Then    1
Else    0

Repeat the code for the other 23 formulas, amending the starting values of the 2 variables Rt (Range From) and Rt (Range to) to reflect the correct time periods.

Hope this helps.

Pete

 

[newbie0423]

Hi Pete,

Thank YOU!! The formula saved without any errors being found. However, when the report runs I get a message saying that " A summary has been specified on a non-recurring field." This is what I have:

WhilePrintingRecords;
Local NumberVar IT := {v_CRA_20_Billing.time_pacu_start};
Local NumberVar OT := {v_CRA_20_Billing.time_pacu_end};
Local NumberVar Rf := 2000;
Local NumberVar Rt := 2100;

If {v_CRA_20_Billing.time_pacu_end} < {v_CRA_20_Billing.time_pacu_start}
Then OT := OT + 2400
Else OT := OT;

If {v_CRA_20_Billing.time_pacu_end} > {v_CRA_20_Billing.time_pacu_start} and
Not (
OT < Rf or
IT > Rt
)
Then 1
Else
If {v_CRA_20_Billing.time_pacu_end} < {v_CRA_20_Billing.time_pacu_start} and
(
(IT < Rf and OT > Rf) or
(IT < Rf and OT > 2400) or
(IT > Rf and OT < 2400) or
(IT in [Rf to Rt] and OT > 2400) or
({v_CRA_20_Billing.time_pacu_end} in [Rf to Rt]) or
(OT > 2400 and {v_CRA_20_Billing.time_pacu_end} > Rt)
)
Then 1
Else 0

 

[pmax9999]

You will not be able to use a Summary to total the number of patients in the room each hour. You will need to use Variables. Before we get to that, can you remove the Summary fields to verify the formula is at least giving you the results you are looking for.

Pete.

 

[newbie0423]

I took off the Summary fields and It works!!!! I have a patient that came in at 21:35 and didn't leave until 01:05. they showed up in all hours preceding and after 12 midnight! Thanks Pete!!!! Will you show me how to use variables to total.........

 

[pmax9999]

Amend your {@20:00 - 21:00} formula to:

WhilePrintingRecords;
Local NumberVar IT := {Table.In_Time};
Local NumberVar OT := {Table.Out_Time};
Local NumberVar Rf := 2000;
Local NumberVar Rt := 2100;
Local NumberVar x;
Global NumberVar T20_21;

If      {Table.Out_Time} < {Table.In_Time}
Then    OT := OT + 2400
Else    OT := OT;

x :=
If      {Table.Out_Time} > {Table.In_Time} and
        Not (    
                OT < Rf or
                IT > Rt
            )
Then    1
Else    
If      {Table.Out_Time} < {Table.In_Time} and
        (
            (IT < Rf and OT > Rf) or
            (IT < Rf and OT > 2400) or
            (IT > Rf and OT < 2400) or
            (IT in [Rf to Rt] and OT > 2400) or
            ({Table.Out_Time} in [Rf to Rt]) or
            (OT > 2400 and {Table.Out_Time} > Rt)
        )
Then    1
Else    0;

T20_21 := T20_21 + x;

x

Note: the only variable name that will need to be changed from formula to formula is the one I have called "T20_21" (reflecting the time period).

Then create the following formula and place it in the Report Footer:

WhilePrintingRecords;
Global NumberVar T20_21;

The downside is that you will need 24 additional formulas to total each of the time periods. Messy, but it will work.

Cheers
Pete

 

[newbie0423]

Hi Pete,

The formulas work perfectly!!! Thank you so much for ALL of your help. )

Val