Time Range

[Gizmo0917]

We operate on a 24X7 hour time range. I need to do a report that will allow me to capture the time range for my 2nd shift people. The time will be 7Pm to 7:30AM This allows me to do a productivity report. I have a report set up and it is getting my information for the dayshift just fine but the night shift is not giving me accurate information. We are using a SQL Based Server and I use Crystal Reports V8.5 in order to pull reports. Is there a way that I can get the correct times in Crystal? I think the SQL pgm has a 24X7 clock but it goes from 12 to 12. Can anyone help?

 

[synapsevampire]

If you group by the following it might help:

if time({table.field}) >= time(7,30,0)
and
time({table.field}) < time(19,0,0) then
"Shift 1"
else
"Shift 2"

When you say SQL Program, that doesn't help, you need to learn the type of the database, and in most cases it will be a 24 hour clock, even though some programs presentation of the time might demonstrate 12-12 AM/PM

-k

 

[Gizmo0917]

I will try this. We use a medical Software pkg that sits on a Sql Server I believe it is SQL 7 if that helps.

 

[Gizmo0917]

I put this formula in and I am getting an error. That says I am missing ) . I have double checked the formula and have it exactly as it is in the post. Any suggestions?

Thank you!

 

[dgillz]

I see nothing wrong with SV's formula. Is this the entire formula or do you have lines of code the precede or follow this?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Gizmo0917]

This is the entire formula I found one mistake where I left out something and tried it again and it said the result must be a boolean. The fields that I am getting this from our datetime fields. So I believe I have to account for the dates somehow but I am not sure how to do that.

Thank you.

 

[Reebo99]

Can you post the exact formula you are using (i.e copy from formula editor and paste here), including the correct field names...



Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[dgillz]

Most likely you are entering this as a record selection formula rather than creating a formula field. Record selection formuls must always be boolean.

Can you tell us exactly what steps, mouse click by mouse click, you used to create this formula?

If you edit the formula and at the top it says "Record selection formula editor" rather than "Formula Editer: @YourFormulaName" then this is the problem.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Gizmo0917]

if time({table.field}) >= time(7,30,0)
and
time({table.field}) < time(19,0,0) then
"Shift 1"
else
"Shift 2"

I created a New Formula and called it Shift using this formula and got no errors. But when I placed it in the report or tried to group on it it put everyone in Shift2. I have a parameter set up to choose beginning date and ending date for the report. It is set as a Date String. Do I need to put in a parameter for time as well?

 

[Gizmo0917]

{PRACTWORK.DATE}in DTStoDateTime({?StartDate}+ ""+{?StartTime}
+":00.00")to
DTStoDateTime({?EndDate} + "" +{?EndTime}+
":00.00")

I tried creating 4 parameters and using this formula that I got from the Crystal Reports Knowledge base. The formula gives me no errors but it still doesn't give me the correct information. I doesn't allow for am and pm and it doesn't pull up all the days that each person worked.

 

[dtempleton]

Sounds like {PRACTWORK.DATE} is a Date-Time field, and the time changing to midnight on the bad machines. In your reporting tab for options (on both the good and bad machines, do they both have "Convert Date-Time Field to Date-Time" ?

 

[Gizmo0917]

I am not sure what you mean by good and bad machines. But On the report options tab I do have it set for ConvertDate-Time Field to Date-Time.

 

[dtempleton]

Sorry about the good and bad machine confusion - I was reading your thread and another thread at the same time. Doh!

Dave

 

[Gizmo0917]

if time({PRACTWORK.DATE}>= time(7,30,0)
and
time({PRACTWORK.DATE}) < time(19,0,0)then
"Shift 1"
else
"Shift 2"

This is how I have it and I did this by creating a formula called shift in the new formula field not the edit selection formula. It gives me an error says I am missing a ). I named the formula shift.

 

[Reebo99]

Replace it with:

if time({PRACTWORK.DATE})>= time(7,30,0)
and
time({PRACTWORK.DATE}) < time(19,0,0)then
"Shift 1"
else
"Shift 2"


Reebo
Software Development Manager
Dunkett and Leggit

 

[Gizmo0917]

I used that and got no errors however it still will not give me the correct information. I give up I have tried about 5 different ways to do this and Crystal will not give me that results I need. I must assume that there is not a way to get the correct data from 2 different days when a shift runs into the next day. Thanks anyway for all your help.

 

[kskid]

Hmmmm... I have CR8.5 and the shift formula in the detail section worked for me so I am not sure as to why it did not work for you. Unless it's because you are on SQL Server and I am on Oracle.

Need to see what is on the detail record to determine why the formula does not work.

Is there just one record per employee per date?

Are the start and end dates in separate fields or are we looking at multiple records using the same field for start and end times?

How do you handle overtime when the hours overlap into another shift?

Is there a shift field on the record or somewhere on a different table that can be linked to the employee id.

Otherwise, we are just guessing at the solutions

 

[Gizmo0917]

We use 1 date record per employee. They don't get overtime the times will always be from 6:45am to 7:30PM and 6:45Pm til 7:30 am. The productivity comes from their charting. so if they are late charting I give them until 7:30 to catch it up.

{PRACTTEAMS.TEAMNAME} = {?TeamName} and
{PRACTWORK.DATE} in Datetime ({?BeginningDate})to Datetime ({?EndingDate})

The time parmeter is set up in the Report Header. Above is the Record Selection Formula. The only other fields are the
TherapistName
Dates Worked (which also is the PractWork.Date field)
@DailyProdAVG
@MonthlyPercentage (Percentage of what the daily productivity is for the month)

We use a Mediserve Client with a SQL backend.

This is how the report is right now without any adjustments to it. I put it back the way it was cause I couldn't get any of the suggested formulas to pull up anything different than what I already was getting. Of course I could be doing it wrong. But this is all the information I have to give. The date params in this are just date and won't allow me to specify a time I tried changing the field to a DateTime where I could specifiy the time and I get this error.


{PRACTTEAMS.TEAMNAME} = {?TeamName} and
{PRACTWORK.DATE} in Datetime ({?BeginningDate})to Datetime ({?EndingDate})

"A date is required here" and the cursor will flash in the paren right before BeginningDate.

Thanks!

 

[RWahlert]

I would think that field {PRACTWORK.DATE} is a date field not a date/time field and since you are using the Datetime function there is a difference between the tow field types.

I'm a little late joining this party so I hope I'm not too off base.

 

[kskid]

1 date record per employee or 1 datetime record per employee? If one date record per employee, then how can the start and end date be in the same field? In either case, how do you distinguish whether it is a start time for one employee or the quit time for another?

As far as your error, either {PRACTWORK.DATE} a date field only or value type specified for parameter {?BeginningDate} is not set to date. I'm not sure but Crystal may handle date only fields with a fixed time of 00:00:00 or 12:00:00 time, depending on your setup.

I also noticed that you have parameter, {?TeamName}, as part of your selection criteria. This in itself is a little puzzling as to why you want a shift breakdown. With a parameter, wouldn't you just enter names for the shift you want?