Checking date against time

Question

0.00/5 (No votes)

See more:

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 but i want if e.g. person doesn't come on particular day (e.g. sunday) then there would be obviously no InTime and EndTime so in that case it should his empid, empName etc and should put 00:00 in time columns like overtime, intime, workingtime etc but problem is that date is only entered when Intime is available but how Intime can be entered when it's holiday :(

e.g.

SQL

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

Query:

SQL

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

Posted 5-Feb-14 1:57am

Hunain Hafeez

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Answer 1 · 2014-02-05T09:13:00

As you say, you don't have the information about employee no being in at a particular day. Hence you need to manufacture those records. What you need is a calendar table i.e. a table with all days you are interested in. This is not exact query but should you give the general idea:

SQL

SELECT 
    e.id,
    e.name,
    COALESCE(a.InTime, cast('00:00' as TIME)) as InTime
FROM empoyees e
CROSS JOIN calendar c
LEFT JOIN attendance a 
    ON a.date = c.date 
    AND a.empolyee_id = e.employee_id

The CROSS JOIN will give you all possible combinations of employee and date. The LEFT JOIN will then append attendance records for given day/employee if found, COALESCE will provide the default value if not not found.

See this example on SqlFiddle

chaau · Answer 2 · 2014-02-05T17:31:00

In addition to Tomas's solution, you can use recursive queries, like this:

SQL

;with minmax as(
SELECT Convert(DATE, MIN(RecDate)) mindt,
Convert(DATE, MAX(RecDate)) maxdt FROM AtdRecord),
alldates AS( SELECT mindt as dt FROM minmax
UNION ALL
SELECT DateAdd(day, 1, a.dt) dt FROM alldates a cross join minmax m WHERE a.dt < m.maxdt
),
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
,a.dt AS [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 alldates a LEFT JOIN times ON a.dt = times.[DateVisited] order by EmplID, ShiftID, a.dt
OPTION (MAXRECURSION 0)

Checking date against time

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0