Link to home
Create AccountLog in
Avatar of Sue Taylor
Sue TaylorFlag for United States of America

asked on

Display records on a given date when it passes midnight

I have a Crystal Report that I want to set up to run automatically every day for "yesterday's" employee labor records.  This is a three shift operation, and third shift will start at 10:00 p.m. and work until 6:00 a.m.  The employees clock in and out of jobs throughout their shift.  There may be some times when an employee clocks in at 10:00 p.m and is still on that same job until 6:00 a.m. and then again, he may be on multiple jobs throughout the shift, with multiple punches that could be on two different dates.
The fields that I have are {starttime} and {endtime}, both which are in DateTime format.  I also have a {department_id} field, which is sort of setup by shifts.  I am trying to figure out how to run this report and have it display records for people who are on the third shift.  In this report, department_id  "212"  is equal to third shift.  I attempted to wrtie a formula that said...

If {Command.department_id} = 212 and
 Hour ({Command.cardstarttime_ts}) <= 7 then ({Command.cardstarttime_ts}) -1  
 else ({Command.cardstarttime_ts})

However my results are not showing clockins after midnight.

Am I going about this all wrong?
Test-with-mising-punch-names.rpt
Avatar of Mike McCracken
Mike McCracken

You have the tests backwards.  The endtime will be 7 or less if they are on shift 3.  Starttime will be after 10pm or 22 hours

mlmcc
Avatar of Sue Taylor

ASKER

You are correct, mlmcc, however I changed this and still do not get the results I was hoping for.  In fact, no matter what I put in there, I'm not getting different results which leads me to believe that something is wrong with my formula.  So I put the original start and end times from the db and got the same results as I would if I used the start and end times that I created a formula for.

Could this be due to the fact that I have a SQL Command that says GETDATE() -1?  Would it be overiding what I'm trying to display?  This is the first time I've used a SQL Command so I am at a lost with it.
Can you upload the report with some saved data that shows the issue?

mlmcc
I had to copy two screen shots of what "Bobby Watson's" labor should have been.
Test-with-mising-punch-names.rpt
Yesterday.pdf
Bobby-Labor-part-1.jpg
Bobby-Labor-part-2.jpg
Can you copy the command here

mlmcc
Yes, any test in the Command is going to be limiting the data that's produced by that Command (ie. the data that the report gets).  Any tests that you put in the report's record selection formula are going to be filtering that data.

 If possible, you want to alter the test in the Command so that it includes the data that you're looking for.  IOW, take the tests that you were trying to do in the record selection formula and put them in the Command instead.

 If that's a problem, the next best thing would be to widen the tests in the Command so it will include the data that you need, without too much "extra" data.  For example, if you're looking for "yesterday", but that could include times early today (before first shift starts), then if the Command is looking for something like cardstarttime_ts = GETDATE() - 1, change = to >=, to include yesterday and today, and then check the time in the report, so that it only includes the times from today that were before first shift.

 James
mlmcc, here is the command.

James, my commond includes CONVERT and I'm at a lost on how to modify this since I know noting about SQL

If either of you could help, that would be great!

SELECT "aptp_view_time_cards"."TaskName", "aptp_view_time_cards"."cardstarttime_ts",  "aptp_view_time_cards"."cardendtime_ts", "empMain"."firstname", "aptp_view_time_cards"."quantity", "aptp_view_time_cards"."total_hr", "aptp_view_time_cards"."JobNumber", "empMain"."lastname", "empMain"."department_id", "empMain"."active_yn"
FROM   empMain "empMain" LEFT OUTER JOIN aptp_view_time_cards "aptp_view_time_cards" ON "empMain"."employee_id"="aptp_view_time_cards"."employee_id" AND "aptp_view_time_cards"."cardstarttime_ts" >= CONVERT (varchar(20), GETDATE () - 1, 101) AND
"aptp_view_time_cards"."cardstarttime_ts" < CONVERT (varchar(20), GETDATE (), 101)
NOt sure how to do it in SQL but you really need

StartTime >= (GetDate()-1,Time(6,0,0))
AND
StartTime < (GetDate(),Time(6,0,0))

mlmcc
SOLUTION
Avatar of James0628
James0628

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Sorry for the delay guys.  

Ok....so I have the command line changed to show both yesterday's and today's punches.  Now I am struggling with creating the formula in record selection to filter the data to show only the times for the shifts that I want.

My current Record Selection shows the following:
{Command.department_id} <> 206 and
{Command.active_yn} = 1

I need  to filter the times for {Command.department_id} = '212' from 21:00 until 6:30
Then on {Command.department_id} = '211' (2nd shift) I want it to display from 14:00 to 03:00 to allow for time if they work overtime.


Would you please help me with this formula?
I forgot to mention, that if it would be easier for me to have (3) separate reports, one for each shift, I'm not opposed to that.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
We are so close!  

I'm concerned that the command does check the dates....or my real problem is that I could have punches being reported twice.

For example:
a Second shift employee (211) comes into work on Tues, 11/13 at 2:30 p.m.  and works until 11/14 at 1:00 a.m.    He had punches on a job on 11/14 12:15 a.m. - 11/14 12:45 a.m.

The next day he works the same shift.... 11/14 2:30 p.m. - 11/15 1:00 a.m.

My report will show the one job that he ran from 11/14 12:15 a.m. - 11/14 12:45 a.m. plus all the work he ran from 11/14 2:30 p.m. - 11/15 1:00 a.m.

--------------------------------------------------------------------------------------------------------

My command looks like this:


SELECT "aptp_view_time_cards"."TaskName", "aptp_view_time_cards"."cardstarttime_ts",  "aptp_view_time_cards"."cardendtime_ts", "empMain"."firstname", "aptp_view_time_cards"."quantity", "aptp_view_time_cards"."total_hr", "aptp_view_time_cards"."JobNumber", "empMain"."lastname", "empMain"."department_id", "empMain"."active_yn"
FROM   empMain "empMain" LEFT OUTER JOIN aptp_view_time_cards "aptp_view_time_cards" ON "empMain"."employee_id"="aptp_view_time_cards"."employee_id" AND "aptp_view_time_cards"."cardstarttime_ts" >=  CONVERT (varchar(20),   GETDATE () - 1, 101) AND
"aptp_view_time_cards"."cardstarttime_ts" <  CONVERT (varchar(20),   GETDATE () +1, 101)



--------------------------------------------------------------------------------------------------------------------------

My Record Selection looks like this:

(
 (
  {Command.department_id} = 212 and
  (Time ({Command.cardstarttime_ts}) >= Time (21, 0, 0) or
   Time ({Command.cardstarttime_ts}) < Time (6, 30, 0))
 )
 or
 (
  {Command.department_id} = 211 and
  (Time ({Command.cardstarttime_ts}) >= Time (14, 0, 0) or
   Time ({Command.cardstarttime_ts}) < Time (3, 0, 0))
 )
or
 
 (
  {Command.department_id} = 210  and
  (Time ({Command.cardstarttime_ts}) >= Time (5, 0, 0) or
   Time ({Command.cardstarttime_ts}) < Time (17, 30, 0))
 )
or
(
  {Command.department_id} = 209  and
  (Time ({Command.cardstarttime_ts}) >= Time (5, 0, 0) or
   Time ({Command.cardstarttime_ts}) < Time (17, 30, 0))
 )
) and
{Command.active_yn} = 1

--------------------------------------------------------------------------------------------------------

212 = 3rd shift
211 = 2nd shift
209 and 210 = 1st shift
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I think the date is handled by the command, mlmcc.  But this is the very first time I have ever used a command.  The command was used in order to be able to show names of employees who didn't have any punches at all.   This was a prior question that I had help with.
When do you run the report?

I think the issue is that you are getting data for yesterday AM (3rd shift) and Today AM (3rd shift).  That is where the data issue comes into it.
You probably also pick up some data for 1st shift from today but if you run the report early it may not show since there is no end time .

WHat you really need is
Yesterday and >= 2100 or Today and <= 0630

Similarly for 2nd shift
Yesterday and >= 1400 or Today and <= 0300

mlmcc
I know!   But how do I do that, mlmcc?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks guys!   I had to leave work early today because I broke a tooth.  But I'll be on this first thing in the morning.
Good point James.

Much easier to read and may be able to be passed to the server

mlmcc
I originally avoided checking the dates, because it seemed like (theoretically) unnecessary work, but including the dates actually makes things a bit simpler, and seems clearer, and gives you more control.

 As for it being passed to the server, will that happen with a Command?  Will CR add a record selection formula to a Command?  I guess it could, theoretically, if it actually parses and understands the Command.  But if it's just replacing any parameters in the Command and then sending it to the db, without trying to interpret it, then it presumably can't add the record selection formula.

 If not, the Command could probably be changed so that it includes those tests, but if the Command is already filtering the data down to just the records for the last 2 days, maybe that's enough and the report can do the rest.  It depends on how many records they could have for 2 days.

 James
This was a tough one.  I learned a lot and appreciate mlmcc and James for all of their hard work.
It won't be passed as part of the command since the command is treated as a stored procedure but it may be passed to the server for filtering before the data is passed to the report.

mlmcc