calculating time difference

--*********************************************************************************


--part 1

--*********************************************************************************

DROP TABLE #temp1

declare @date datetime

set @date = convert(varchar,getdate()-1,101)

SELECT distinct @date Tdate,emp.EmployeeNo,emp.[EmpName],

(select Min([CLKDT]) from [tmsdata].[tmsuser].[tmsclk] where EMPREF COLLATE DATABASE_DEFAULT=[EMP].[TMSNo] COLLATE DATABASE_DEFAULT and datediff(day,CLKDT,@date)=0) ClockIn,

(select Max([CLKDT])from [tmsdata].[tmsuser].[tmsclk] where EMPREF COLLATE DATABASE_DEFAULT=[EMP].[TMSNo] COLLATE DATABASE_DEFAULT and datediff(day,CLKDT,@date)=0 having

Max([CLKDT])<>(select Min([CLKDT]) from [tmsdata].[tmsuser].[tmsclk] where EMPREF COLLATE DATABASE_DEFAULT=[EMP].[TMSNo] COLLATE DATABASE_DEFAULT

and datediff(day,CLKDT,@date)=0)) Clockout,

EMPREF into #temp1

FROM tblEmployee emp

JOIN [tmsdata].[tmsuser].[tmsclk] [CLK] ON [EMP].[TMSNo] COLLATE DATABASE_DEFAULT = [CLK].[EMPREF] COLLATE DATABASE_DEFAULT

WHERE datediff(day,CLKDT,@date)=0 and emp.iscosting = '1' AND emp.active = 1

ORDER BY emp.[EmpName]

--*********************************************************************************

--part2

--*********************************************************************************

DROP TABLE #temp2

SELECT empref, empname, DATEDIFF(mi, ClockIn, Clockout) AS clockedhrs

INTO #temp2 FROM #temp1





--*********************************************************************************

--part3

--*********************************************************************************

DROP TABLE #TEMP3

--declare @date datetime

--set @date = convert(varchar,getdate()-2,101)

SELECT emp.[EmpName] AS empname,TMSNo,

SUM(DATEDIFF(mi, lt.timein, lt.timeout)) AS scannedhrs

INTO #TEMP3

FROM tblLotCardTracking lt JOIN tblEmployee emp ON lt.EmployeeNo = emp.EmployeeNo

INNER JOIN #temp1 t1 ON t1.EMPREF COLLATE DATABASE_DEFAULT = emp.TMSNo

WHERE lt.TimeIn > @date AND lt.TimeIn < @date + 1 AND emp.iscosting = '1' AND emp.active = 1

GROUP BY emp.[EmpName],TMSNo

--*********************************************************************************

--SELECT * FROM #temp1

--SELECT * FROM #temp3

--*********************************************************************************

--part 4

--*********************************************************************************

SELECT

t2.empname,

CONVERT(CHAR(8),DATEADD(MINUTE,SUM(scannedhrs)% 1440, '00:00'), 108) AS scannedhrs,

CONVERT(CHAR(8),DATEADD(MINUTE,clockedhrs% 1440, '00:00'), 108) AS clockedhrs,

scannedhrs,clockedhrs,

CONVERT(NUMERIC(18,2),(CONVERT(NUMERIC(18,2),scannedhrs)/CONVERT (NUMERIC(18,2),clockedhrs) )*100)

FROM #TEMP3 t3 INNER JOIN #temp2 t2 ON t2.EMPREF COLLATE DATABASE_DEFAULT = t3.TMSNo

group BY t2.empname,clockedhrs,scannedhrs

--*********************************************************************************

END