DateDiff problem

[pwilson]

This problem comes from an access database and old asp code. The program is basically a punch clock. People punch in and out. This is the basic guts of a punch record
The date and time are separate because it was originally put together in access and looked prettier then. It is now beind stored in SQL2000, but changing all the code would have been a task.

employeeID(int)
Date(datetime)
Type(int) : 0 is a punch in, 1 is a punch out
Time(datetime) : All records will be on 12/30/1899 only time is being inserted. Date irrelevant here

employeeID  Date                      Type   Time                   
----------  ------------------------  ------ -----------------------

22          2004-09-08 00:00:00.000   0      1899-12-30 06:00:00.000
22          2004-09-08 00:00:00.000   1      1899-12-30 7:00:00.000 
22          2004-09-08 00:00:00.000   0      1899-12-30 11:00:00.000
22          2004-09-08 00:00:00.000   1      1899-12-30 12:00:00.000
22          2004-09-08 00:00:00.000   0      1899-12-30 14:00:00.000
22          2004-09-08 00:00:00.000   1      1899-12-30 15:00:00.000

So this persons basic day is
6-7, 11-12, and 2-3pm

I need to calculate the hours per "work shift".

Fiddling around I have this sp that mostly works. Its running as the exec() because I wanted to label the result sets when testing.

CREATE PROCEDURE GetEmpTime
	@empid varchar(9),
	@date  datetime,
	@Message varchar(20)
AS
	EXEC("SELECT DATEDIFF(mi, PP.Time, PQ.Time) AS " + @Message + ", PP.Time, PQ.Time
	FROM Punch PP, Punch PQ
	WHERE
			PP.Type=0 AND PP.employeeID=" + @empid + " AND PP.Date='" + @date + "'  AND
			PQ.Type=1 AND PQ.employeeID=" + @empid + " AND PQ.Date='" + @date + "'  AND
			PQ.Time IN
			(SELECT S2.Time
			FROM
				(SELECT EmployeeID, Date, Type, Time
				FROM Punch P1
				WHERE Type=0 AND employeeID=" + @empid + " AND Date='" + @date + "') AS S1,

				(SELECT Top 1 EmployeeID, Date, Type, Time
				FROM Punch P1
				WHERE Type=1 AND employeeID=" + @empid + " AND Date='" + @date + "'  AND PP.Time< P1.Time) AS S2)");
GO

This is what it outputs when all their punches are correct.

                       Time                      Time                     
----------  ------------------------  ------------------------

60          1899-12-30 06:00:00.000   1899-12-30 07:00:00.000 
60          1899-12-30 11:00:00.000   1899-12-30 12:00:00.000 
60          1899-12-30 14:00:00.000   1899-12-30 15:00:00.000

I'll be tossing a sum() on it once I know it is working.

The problem is that managers can come in and edit punches, delete them and what not.
Example:
If the manager deletes the 7am punch. Only the workshifts of 11-12 and 2-3 should return.

If they delete the 3pm punch only the first two (6-7,11-12)

Unfortunatley this is what is returned in the 7am case.

dddd  Time                      Time                    
----- ------------------------  ------------------------

360   1899-12-30 06:00:00.000   1899-12-30 12:00:00.000 
60    1899-12-30 11:00:00.000   1899-12-30 12:00:00.000 
60    1899-12-30 14:00:00.000   1899-12-30 15:00:00.000

The 360 line should not display at all. As you can see it is turning the shift into
6-12, 11-12, and 2-3

If the last punch of the day is missing, everything works well.

I was trying to get it to work using Join's, but I can't seem to get the Top 1 tool to work with it.

Any help would be VERY much appreciated

 

[donutman]

There's all sorts of problems with attempting to write time and attendance software. I would not recommend trying to perform calculations on the server, there are just too many gotchas. If your business has only one shift, it might be reasonable, otherwise it's hopeless. I speak from experience, but of course it's only my opinion.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

It seems to me that your problem is that by eliminating the 7:00 am punch out, the employee did stay on the clock. You need to look at the process that happens when the manager adjusts the times. Every punch in must have a punch out or the system can never work correctly.

Questions about posting. See faq183-874

 

[rac2]

donutman has experience so his advice should be heeded. However.

I dont know why he would recommend taking calculations off of the server, seems like that could be a good place for them, sometimes.


If I were going to write T-SQL code for these calculations, I think I would take this approach.

Given that these will be T-SQL calculations, I would construct valid DATETIME values from the bogus Punch.Date and Punch.Time values. Overall this is going to simplify things. I do not agree that "Date irrelevant here".

So, a clockin_time would be

DECLARE @clockin_time DATETIME

SELECT @clockin_time =
      CAST (
        CONVERT(CHAR(11), [Date], 120) +
        CONVERT(CHAR(8), [Time], 108)
      AS DATETIME )
FROM Punch
WHERE Type = 0

Similarly, a @clockout_time.

Then assuming one can find the corresponding in and out times, the @working_duration measured in minutes is

DECLARE @working_duration INT

SELECT @working_duration = 
       DATEDIFF(minute, @clockin_time, @clockout_time)

Finding the corresponding in and out times is a whole other kettle of fish. Without closer consideration, I will say that you have that under control, except for the whimsical managerial deletions. It seems you need to add a level to handle cases where there are no matching in and out times. And that means you must decide what to do, possibly, return nothing by ignoring orphaned in and out rows.

Interesting problem. Hope this helps.

 

[donutman]

I don't know why he would recommend taking calculations off of the server, seems like that could be a good place for them, sometimes.

Finding the corresponding in and out times is a whole other kettle of fish.

That's only the tip of the kettle of fish.
Then you have to worry about overtime, lunch breaks that are too short, smoke breaks, shifts crossing midnight, shift premiums, punches prior to or after scheduled shift and the list goes on.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

You know, you can totally simplify your date and time calculations by just using

Date + Time + 2

in every case where you want the regular old datetime. Try it.

SELECT Date, Time, Date + Time + 2 FROM Punch

Converting to string to chop off the date and time portions is totally unnecessary (and probably one-tenth the speed).


And adding to donutman's list, work periods which last more than 12 hours or even more than 24 hours.

 

[donutman]

Oh yeah, those are good ones, but the guys who work more than 24-hours usually end up in a dough mixer, so we don't have to cut them a paycheck...the insurance company does.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Veep]

Is there any way you can redesign this table? I built an ASP/SQLServer/Barcode Scanner timeclock app a couple of years ago that's still ticking (100+ employees) and I haven't been asked to "enhance" it (yet). You need to get the IN time and the OUT time on the same row and only create a new row for the employee if the IN and OUT are not null (if they didn't punch out then they can't punch back in - call a supervisor :-(). I think that I used a bit flag for that for some reason but I'm not sure. If you are able to tweak it let me know and I'll show you what I did (if I can find the app).

 

[ESquared]

Hey, if donutman fell into a dough mixer, might he become nonutman?

 

[donutman]

Esquared, if I do I'll try to make sure that they're making date nut bread at the time.
Veep, the problem with that solution is that it disables the timeclock for that employee until a "qualified" supervisor can fix his error. Supervisors aren't always at hand and I don't have very many qualified ones that are computer literate on top of it.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

So allow the clockin, make the employee provide a provisional clockout time for the previous record, and review all discrepancies once a week. It's in the employer's best interest to allow people to fix clockout errors by themselves as much as possible.

Adjusting clockIN time, now that would be supervisor only.

 

[donutman]

It's interesting that the current T&A program that I'm using (commercial) doesn't ask if you are clocking in or out, which I think was a poor decision. I was a beta tester for their system (biometric), but they were too far along with the design for me to convince them. My program, on the other hand, alerted the employee that his punch was out of order and gave him a chance to toggle it, but didn't allow them to enter a missing punch. The admin would fix the time-cards at the end of the week and it wasn't too bad. The new system can sometimes get flaky in its interpretation of the punch sequences and is much harder to figure out what is going on. Over all, however, the system has paid for itself by eliminating buddy punches (uses fingerprints), so I love it. We had employees that didn't even come to work, that where punching in via buddies. At night, when there's not many manager types floating around, it's pretty hard to catch them.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]