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

 

[PeterDimitrov]

Cross-tab is used for summary data. If you want to show how many patients you had in the recovery room at 7 ,8 ... then it is OK .

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[newbie0423]

OK so if I have four patients in the recovery room.(see below) Patient D would be counted for the 2:00, 3:00, 4:00 and 5:00 hour
Pt C would be counted for the 4:00 and the 5:00 hour. WE are trying to get a count of how many patients we have in the recovery room at any given hour. I'm not sure that this can be done in Crystal.

In Out
Patient A 8:28 9:23
Patient B 6:44 8:30
Patient C 4:27 5:17
Patient D 2:25 5:28

 

[PeterDimitrov]

The simplest way to do this is to define a table (for example named Hours) with 24 records - 1 record for each hour
StartTime EndTime
...
8:00 9:00
9:00 10:00
10:00 11:00
...

Then join your data with this table:

SELECT p.PatientName, convert(varchar,h.StartTime) + ' - ' + convert(varchar,h.EndTime) as TimePeriod
FROM Patients p INNER JOIN Hours h ON
(h.StartTime <= p.StartTime AND p.StartTime<h.EndTime)
OR
(p.StartTime<= h.StartTime AND h.StartTime<p.EndTime)

This will return records like this:
PatientName TimePeriod
Patient A 8:00 - 9:00
Patient A 9:00 - 10:00
Patient B 6:00 - 7:00
Patient B 7:00 - 8:00
Patient B 8:00 - 9:00

In Crystal you can create a crosstab using TimePeriod as Row Values and Count of patients as a summary

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[newbie0423]

Thamk you very much for responding Peter. Please forgive me but I don't understand when you say "define a table." Are you refering to performing this in SQL?

 

[PeterDimitrov]

Yes you need to create or define a table.

if you don't have permissions to create tables you can use something like this:

select convert(datetime,'00:00') as StartTime, convert(datetime,'01:00') as EndTime
UNION
select convert(datetime,'01:00'), convert(datetime,'02:00')
UNION
select convert(datetime,'02:00'), convert(datetime,'03:00')
...

and replace the Hours table


all these SQL commands are for SQLSevver if you are using another database you need to change the syntax

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[newbie0423]

This won't work for me. I'm an end user. I only have access to Crystal XI, I don't have access to SQL. I was hoping that this was something that could be done in Crystal without having to write tables, views, or sp.
Thanks for your help.

 

[newbie0423]

Could this possibly done using a running total?

 

[pmax9999]

The other approach would be to create 24 formulas (ie one for each hour of the day) - less is the Recovery Room is avalable for less than 24 hours.

The formula for 8:00 pm to 9:00 pm would be:

If      Not (    
                Time({Table.In_Time})  > Time(21,0,0) or
                Time({Table.Out_Time}) < Time(20,0,0)
            )    
Then    1

This will return a 1 if that patient was in the room at anytime during the hour. The formula can then be summed to give a total of all patients who used the room during that hour.

Hope this helps.

Cheers
Pete

 

[newbie0423]

Hi Pete,
Thanks for responding I REALLY appreciate it. I'm struggling with this one. I created the one formula that you posted, and I do have patients that were in the recovery room between 8:00 and 9:00 but the formula didn't return one, it returned zeros.

 

[pmax9999]

I tested this and it worked for me. Please copy an paste the exact formula you used, and sample data for the records returning 0 instead of 1.

The only thing that springs to mind is that nulls could cause a problem. Is this possible, for example while someone is in the room, what does the "out" time show?

Cheers
Pete

 

[newbie0423]

This is the exact formula that I put into my report.
If Not (
Time({v_CRA_20_Billing.time_pacu_start}) > Time(21,0,0) or
Time({v_CRA_20_Billing.time_pacu_end}) < Time(20,0,0)
)
Then 1


Some of the hours that the patients were in the recovery room are:

IN Out
1559 1653 0
1844 2030 0 (this shoould have returned a 1, right)
1338 1604 0
1458 1529 0
2038 2133 0 (this should have returned a 1)

Thanks

 

[pmax9999]

It worked in my testing. The times in your sample above don't look like times - I would expect 20:38 rather than 2038.

Please confirm these are actual time fields.



Cheers
pete

 

[newbie0423]

Hi Pete,

You are correct they were numnber fields. I changed them to time fields. Now I'm getting "a date-time field is required" here when I try to use the formula that you posted. Here is the formula that I am using to convert my number to time.

if Length (cstr({v_CRA_20_Billing.time_pacu_start}))>5 then ctime(replace(left(CStr ({v_CRA_20_Billing.time_pacu_start}),Length (cstr({v_CRA_20_Billing.time_pacu_start}))-5),",","") & ":" &
Left(Right(CStr ({v_CRA_20_Billing.time_pacu_start}),5),2)) else If Length (cstr({v_CRA_20_Billing.time_pacu_start}))=5 then ctime("00:" &
Left(Right(CStr ({v_CRA_20_Billing.time_pacu_start}),5),2)) else If Length (cstr({v_CRA_20_Billing.time_pacu_start}))=4 then ctime("00:0" &
Left(Right(CStr ({v_CRA_20_Billing.time_pacu_start}),4),1)) else ctime("00:00")

 

[pmax9999]

So, is your post of 26 Jul 13 8:58 the exact formula you are using (ie using database fields)? Is that where you are getting the error mentioned in your most recent formula?

Also, please post some examples of the number format representing the In/Out times. Your conversion formula looks unnecessarily complex.

 

[newbie0423]

Yes Pete, that is the exact formula that I am using. and that is the formula that you posted for me to use. it is returning zero. I tried to replace your formula with the formula below, but the "a date-time field is required" message appeared. @0RStarttime is a time field.
If Not (
Time({@ORStartTime})> Time(21,0,0) or
Time({@ORStartTime})< Time(20,0,0)
)
Then 1

here are some of the times:
3:27 4:23
3:10 4:24
2:41 3:56

Thanks

 

[pmax9999]

What I need is a sample of the data as it appears in the database, not how it appears after you have manipulated it. Based on your earlier formula the field is text and can consist of more than 5 characters.

I believe the reason why my formula is failing is because of the way it is being converted to time. I had assumed the field was date/time, rather than text, so it is important to understand the exact format so it can be converted correctly.

Cheers
Pete

 

[newbie0423]

I'm sorry Pete, I have limited access to the system. I am not able to provide the way the field is structured in the database. I can only tell you that the ({v_CRA_20_Billing.time_pacu_start}) field in crystal says that it is a number. Thank you for all of your replys.

Val

 

[newbie0423]

Is there anyother way for me to get the results that I need?

 

[pmax9999]

So, are you saying that the time 20:38 is in the database as 2038, ie a number (as opposed to text)?