• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 532
  • Last Modified:

Advance Crystal report seperating date time field

Tough question...Need to break down "Correct time" into 5 columns.   M - F 7:30am - 3:30pm, 3:30 - 6pm, 6 -8 pm, 8pm to 7:30 am, and a column for all other hours (sat and sun).  

Need to rename attached file from .txt to .rpt before it will work in Crystal Reports XI.
OR-Block-Utilization-Scheduled-v.txt
0
Becky Edwards
Asked:
Becky Edwards
  • 5
  • 4
1 Solution
 
James0628Commented:
I'm not sure what you're trying to do.  You say that you want to break "Correct time" down into 5 columns, based on the day of the week and the time, but the "Correct time" appears to be a total amount of time (X number of hours and minutes), so I'm guessing that the 5 columns will be based on something else?  Maybe SNAPSHOT_DATE?

 If so, then one option would be to have a formula for each time period that includes the time in a record if the date and time fall in that period.  For example, assuming that SNAPSHOT_DATE is a datetime, if it's on a Monday - Friday, between 3:30 and 6 PM, then the time in that record is included by the formula for that time period.  Put a total for each of those 5 formulas in each column.

 And if I've completely misunderstood what you're trying to do, can you try to explain it a bit further?

 James
0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Yes, The correct time is a total amount of hours and minutes. What I need is to know, of that total (say 6 hours, 10 minutes) how much of it fell between 7 am and 3:30 pm, 3:30 pm to 6 Pm, etc.
Someone else wrote this report and I am trying to modify it to include more information that our Operating Room Supervisor needs.
I included the formula for Actual correct time, which is the field I am trying to break up into increments.  It is greek to me.  Perhaps it is to everyone.

//calculates correct minutes.
//Turnover time is either included or not depending on the "Include Turnover" parameter.


If 
(
    {OR_UTIL_BLOCK.SNAPSHOT_NUMBER} in [{?Snapshot - Actual Utilization}]
    and {?Include Turnover}="Yes"
    and ({OR_UTIL_BLOCK.SLOT_TYPE}="CORRECT")
)
    then {OR_UTIL_BLOCK.SLOT_LENGTH}
else
If 
(
    {OR_UTIL_BLOCK.SNAPSHOT_NUMBER} in [{?Snapshot - Actual Utilization}]
    and ({?Include Turnover}="No")
    and ({OR_UTIL_BLOCK.SLOT_TYPE}="CORRECT")
    and {OR_UTIL_BLOCK.PROC_TIME}="Y"
)
    then {OR_UTIL_BLOCK.SLOT_LENGTH}
else 0

Open in new window

0
 
James0628Commented:
What that formula does is output {OR_UTIL_BLOCK.SLOT_LENGTH} if one of two sets of conditions are met, otherwise it outputs 0.  IOW, if these conditions are true or those conditions are true, {OR_UTIL_BLOCK.SLOT_LENGTH} is included in the total time for the group.

 What you need is something that gives you the start and/or end date and time for SLOT_LENGTH.  For example, if SLOT_LENGTH started at 3 PM on a Tuesday and it's 90 minutes, 30 minutes of it falls in the 7 AM - 3:30 PM slot, and 60 minutes falls in the 3:30 PM - 6 PM slot.

 OR_UTIL_BLOCK includes START_TIME and END_TIME fields.  Those may give you the dates and times that you need.  Can confirm that?

 If so, I have some questions.

 First of all, you have overlapping brackets.  For example, 3:30 - 6 PM and 6 - 8 PM both include 6 PM.  You presumably don't want to include 6 PM in both of those.  Should it be something like from 3:30 to before 6 PM, and from 6 to before 8 PM?

 What are the units for SLOT_LENGTH?  I'm guessing minutes?

 What is the maximum SLOT_LENGTH?  Could it be more than 12 hours?  More than 24?  That may affect how you check the times.

 Even if SLOT_LENGTH is relatively short (say, limited to a few hours), could a single entry run from one day to the next (eg. from 11 PM on Wednesday to 2 AM on Thursday)?

 Assuming that a single entry can run from one day to the next, if it goes from Friday night to Saturday morning, or Sunday night to Monday morning, do you want the part of the SLOT_LENGTH that falls after midnight Friday or before Midnight Sunday to be in the "other hours" column, even though the entry started or ended on a weekday (Friday or Monday)?  Just checking.

 James
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Whew!  OK - I will check on all that and get back to you. I need to discuss with OR.  Thank you!
0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Here is what they said---------------
 OR_UTIL_BLOCK includes START_TIME and END_TIME fields.  Those may give you the dates and times that you need.  Can confirm that?  
Yes, that sounds correct.

Should it be something like from 3:30 to before 6 PM, and from 6 to before 8 PM?
 
M-Th 7:31a  3:30p, 3:31p-6:00p,  6:01p-8:00p, 8:01p-7:30am.  Friday 7:31a  3:30p, 3:31p-6:00p, 6:01p-8:00p, 8:01p-11:59p.  Saturday 12:01am thru Monday 7:30am.  
 
 What are the units for SLOT_LENGTH?  I'm guessing minutes
 
Yes, total minutes.

 What is the maximum SLOT_LENGTH?  Could it be more than 12 hours?  More than 24?  That may affect how you check the times.  
 
No max or min specified.

 Even if SLOT_LENGTH is relatively short (say, limited to a few hours), could a single entry run from one day to the next (eg. from 11 PM on Wednesday to 2 AM on Thursday)?
 
Yes, it is possible for the slot length to run into the next day. .

 Assuming that a single entry can run from one day to the next, if it goes from Friday night to Saturday morning, or Sunday night to Monday morning, do you want the part of the SLOT_LENGTH that falls after midnight Friday or before Midnight Sunday to be in the "other hours" column, even though the entry started or ended on a weekday (Friday or Monday)?  Just checking.  
 
Yes, the hours that fall into the weekend would be in the other column, even if the SLOT_LENGTH started on a week day.  (start Friday and end on Saturday)
 
0
 
James0628Commented:
I've given quite a bit of thought to this.  Is there any practical limit to SLOT_LENGTH?

 For the approaches that I've been considering, the possibility that a single entry might include time in the same bracket on multiple days really seems to complicate things.  For example, if an entry started at 5 PM on Monday and ended at 4 PM on Tuesday, it has some time in the 3:31 - 6 bracket on Monday (from 5 to 6), and some time in the 3:31 - 6 bracket on Tuesday (from 3:31 - 4), so the report has to pick up the time in that bracket on both days.

 If you can say that an entry will never be long enough to hit the same time bracket on more than one day, I think that will simplify things.  Just wanted to check, before I pursue the more complicated route.

 James
0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Yes:  I believe we can delete this question.  I have found the OR may be re-working their processes and work flows due to  some other issues.  So until that is finalized, I do not feel it is reasonable to spend more time on this report.
Thank you for your assistance.
0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
The OR is reworking their processes so the answer provided is enough for now.
0
 
James0628Commented:
OK.  Thanks for getting back to us.

 > I believe we can delete this question.

 FWIW, technically, since you've accepted a solution, I don't think the question will be deleted.  It will be closed, but not deleted.

 James
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now