putting column 00:00

Hunain Hafeez

i have written this query to show total working time and overtime of an employee on particular date (if he has came) otherwise it doesn't show record for a person if his INTIME and TIMEOUT are empty but i don't want this now, i want if for a particular date person's INtime and OutTime are empty then put 00:00 in his intime, outtime, totalworkingtime, overtime. e.g.

EmplID EmplName ShiftID intime Outtime totalworking overtime dateVisited
0000001 John S001 00:00 00:00 00:00: 00:00 2013-12-01

Query:

with times as (
SELECT t1.EmplID
, t3.EmplName
, min(t1.RecTime) AS InTime
, max(t2.RecTime) AS [TimeOut]
, t4.ShiftId as ShiftID
, t4.StAtdTime as ShStartTime
, t4.EndAtdTime as ShEndTime
, cast(min(t1.RecTime) as datetime) AS InTimeSub
, cast(max(t2.RecTime) as datetime) AS TimeOutSub
, t1.RecDate AS [DateVisited]
FROM AtdRecord t1
INNER JOIN
AtdRecord t2
ON t1.EmplID = t2.EmplID
AND t1.RecDate = t2.RecDate
AND t1.RecTime < t2.RecTime
inner join
HrEmployee t3
ON t3.EmplID = t1.EmplID
inner join AtdShiftSect t4
ON t3.ShiftId = t4.ShiftId
group by
t1.EmplID
, t3.EmplName
, t1.RecDate
, t4.ShiftId
, t4.StAtdTime
, t4.EndAtdTime
)
SELECT
EmplID
,EmplName
,ShiftId As ShiftID
,InTime
,[TimeOut]
,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) TotalWorkingTime
,[DateVisited]
,CASE WHEN [InTime] IS NOT NULL AND [TimeOut] IS NOT NULL THEN
CONVERT(char(5),CASE WHEN CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S002' Then LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME),CAST([TimeOutSub] AS DATETIME)),0), 108),5)
WHEN CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S001' Then LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME), CAST([TimeOutSub] AS DATETIME)),0), 108),5)
ELSE '00:00' END, 108)
ELSE 'ABSENT' END AS OverTime
FROM times order by EmplID, ShiftID, DateVisited

Chris Quinn

Change your inner joins to outer joins

========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================

Mycroft Holmes

When building these type of query I break it down to getting the primary list (employees) and getting the data you are reporting (timesheet information) then I use a left outer join between the primary and data queries and use ISNULL to display the default values (00:00).

Never underestimate the power of human stupidity RAH