Calculating differences between dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculating differences between dates

Author

Topic

RalphWiggum
Starting Member

13 Posts

Posted - 2011-07-29 : 09:06:03

I have 2 tables: E_Employees and E_TimeCards. I'm attempting to produce a "Time Card" report that pulls data from these 2 tables. Here is my query:

SELECT
E_Employees.D_LNAME+', '+E_Employees.D_FNAME+' '+E_Employees.D_EMPNO as LAST_FIRST_NAME_EMPNO,

CONVERT(NVARCHAR, E_TimeCards.TC_PDATE, 101) AS Date,

E_TimeCards.TC_TYPE AS PunchType,

CONVERT(NVARCHAR, E_TimeCards.TC_ATIME, 108) AS ActualTime,

CONVERT(NVARCHAR, E_TimeCards.TC_RTIME, 108) AS RoundedTime,

CONVERT(NVARCHAR, (SELECT E_TimeCards.TC_RTIME WHERE E_TimeCards.TC_TYPE = '1'), 108) AS InDay,

CONVERT(NVARCHAR, (SELECT E_TimeCards.TC_RTIME WHERE E_TimeCards.TC_TYPE = '2'), 108) AS OutLunch,

CONVERT(NVARCHAR, (SELECT E_TimeCards.TC_RTIME WHERE E_TimeCards.TC_TYPE = '12'), 108) AS InLunch,

CONVERT(NVARCHAR, (SELECT E_TimeCards.TC_RTIME WHERE E_TimeCards.TC_TYPE = '11'), 108) AS OutDay,

E_Employees.D_PCLASS AS PAYCLASS,

E_Employees.D_SHIFT AS SHIFT,

E_Employees.D_JOBCODE AS JOB,

E_TimeCardDetail.DE_PAY AS PayType

FROM E_Employees

INNER JOIN E_TIMECARDS ON E_EMPLOYEES.D_EMPNO = E_TIMECARDS.TC_EMPNO

My issue is this: I need to find a way to calculate hours worked per day. Specifically, I need to add up the hours between InDay and OutDay times. The problem is that I can't find a way to do this. I've tried straight up subtraction (OutDay - Inday), and DATEDIFF(hh, InDay, OutDay), but they are both producing "NULL" in the dataset. Here's an example result with the name and employee number left out (I realize the formatting of all of this sucks, but I would attach jpegs if I could?):

Header would be: Date, PunchType, Actual Time, Rounded Time, InDay, OutLunch, InLunch, OutDay, PayClass, Shift, Job

07/25/2011 1 07:49:00 07:45:00 07:45:00 NULL NULL NULL 01 01 RECRUITR 10
07/25/2011 2 12:52:00 12:45:00 NULL 12:45:00 NULL NULL 01 01 RECRUITR 10
07/25/2011 11 16:30:00 16:30:00 NULL NULL NULL 16:30:00 01 01 RECRUITR NULL
07/25/2011 12 12:23:00 12:30:00 NULL NULL 12:30:00 NULL 01 01 RECRUITR NULL
07/26/2011 12 12:27:00 12:30:00 NULL NULL 12:30:00 NULL 01 01 RECRUITR NULL
07/26/2011 11 16:29:00 16:30:00 NULL NULL NULL 16:30:00 01 01 RECRUITR NULL
07/26/2011 1 07:51:00 07:45:00 07:45:00 NULL NULL NULL 01 01 RECRUITR 10
07/26/2011 2 12:56:00 13:00:00 NULL 13:00:00 NULL NULL 01 01 RECRUITR 10
07/27/2011 1 07:54:00 08:00:00 08:00:00 NULL NULL NULL 01 01 RECRUITR 10
07/27/2011 2 13:02:00 13:00:00 NULL 13:00:00 NULL NULL 01 01 RECRUITR 10
07/27/2011 12 12:31:00 12:30:00 NULL NULL 12:30:00 NULL 01 01 RECRUITR NULL
07/27/2011 11 16:31:00 16:30:00 NULL NULL NULL 16:30:00 01 01 RECRUITR NULL
07/28/2011 1 07:48:00 07:45:00 07:45:00 NULL NULL NULL 01 01 RECRUITR 10
07/28/2011 2 12:37:00 12:30:00 NULL 12:30:00 NULL NULL 01 01 RECRUITR 10
07/28/2011 12 12:18:00 12:15:00 NULL NULL 12:15:00 NULL 01 01 RECRUITR NULL
07/28/2011 11 16:35:00 16:30:00 NULL NULL NULL 16:30:00 01 01 RECRUITR NULL

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-29 : 10:44:54

could you please post some DDL/DML to populate a temp table with your sample data? Cause it looks like the majority of your rows have Nulls for [OutDay] values. If either of the date operands in a DATEDIFF function call is null then the result will be NULL.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 11:38:49

what are datatypes of Inday,outday,inlunch,outlunch etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RalphWiggum
Starting Member

13 Posts

Posted - 2011-07-29 : 12:46:28

quote:
Originally posted by visakh16

what are datatypes of Inday,outday,inlunch,outlunch etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

They are all DATETIME

RalphWiggum
Starting Member

13 Posts

Posted - 2011-07-29 : 12:53:59

quote:
Originally posted by TG

could you please post some DDL/DML to populate a temp table with your sample data? Cause it looks like the majority of your rows have Nulls for [OutDay] values. If either of the date operands in a DATEDIFF function call is null then the result will be NULL.

Be One with the Optimizer
TG

Each record in the results is a "Time Clock Punch"...one for InDay, OutLunch, InLunch, OutDay. Each time an employees punches for one of those punch types, the time associated with that punch type populates the record. All other punch types will contain NULL.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-29 : 13:12:41

Since you didn't post any of your DDL/DML all I can do is describe what I think you need to do:
forget all those subqueries in you select clause. Transpose the rows to columns with PIVOT or simple grouping and from that calc your datediff. like this:


select datediff(hh, max(case when punchtype = 1 then InDay end), 
                    max(case when punchtype = 11 then outDay end))
from   timecards
group by employee
       ,day

if you still need help then post the DML/DDL with expected output and someone can provide a working solution.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 13:20:18

[code]
SELECT
E_Employees.D_LNAME+', '+E_Employees.D_FNAME+' '+E_Employees.D_EMPNO as LAST_FIRST_NAME_EMPNO,

DATEADD(dd,DATEDIFF(dd,0, E_TimeCards.TC_PDATE),0) AS Date,

DATEDIFF(ss,MAX(CASE WHEN E_TimeCards.TC_TYPE = '1' THEN E_TimeCards.TC_RTIME ELSE NULL END),MAX(CASE WHEN E_TimeCards.TC_TYPE = '11' THEN E_TimeCards.TC_RTIME ELSE NULL END))-COALESCE(DATEDIFF(ss,MAX(CASE WHEN E_TimeCards.TC_TYPE = '12' THEN E_TimeCards.TC_RTIME ELSE NULL END),MAX(CASE WHEN E_TimeCards.TC_TYPE = '2' THEN E_TimeCards.TC_RTIME ELSE NULL END)),0) AS WorkingTime
FROM E_Employees

INNER JOIN E_TIMECARDS ON E_EMPLOYEES.D_EMPNO = E_TIMECARDS.TC_EMPNO
GROUP BY E_Employees.D_LNAME+', '+E_Employees.D_FNAME+' '+E_Employees.D_EMPNO,
DATEADD(dd,DATEDIFF(dd,0, E_TimeCards.TC_PDATE),0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources