检查日期与时间 [英] Checking date against time

问题描述

我已经写了这个查询，以显示特定日期（如果他来了）员工的总工作时间和加班时间，否则它不显示某个人的记录，但我想要，例如，人不会来特定的日子（例如星期日）那么显然没有InTime和EndTime所以在那种情况下它应该是他的empid，empName等，并且应该把00:00放在时间列中，如加班，银泰，工作时间等但问题该日期仅在银泰可用时输入，但是在假期时可以输入银泰:(



例如

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

查询：

  次 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 ），强制转换（[TimeOutSub]  -  InTimeSub  as  时间） ， 108 ）TotalWorkingTime 
，[DateVisited] 
， CASE   WHEN  [InTime]  IS   NOT   NULL   AND  [TimeOut]  IS   NOT   NULL  那么 
  CONVERT （ char （ 5 ）， CASE   WHEN  CAST（[TimeOutSub]  AS   DATETIME ）> = ShEndTime 和 ShiftID = '  S002' 然后  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 和 ShiftID = '  S001' 然后  LEFT （ CONVERT （ varchar （ 12 ），DATEADD（ms，DATEDIFF（ms，CAST（ShEndTim） e  AS   DATETIME ），CAST（[TimeOutSub]  AS   DATETIME ））， 0 ）， 108 ）， 5 ）
  ELSE   '  00:00'  END ， 108 ）
  ELSE  ' 缺货'  END   AS  OverTime 
  FROM 次订单 按 EmplID，ShiftID，DateVisited 

解决方案

正如您所说，您没有关于员工的信息天。因此，您需要制作这些记录。你需要的是一个日历表，即你感兴趣的所有日子的表。这不是确切的查询，但你应该给出一般的想法：

  SELECT  
 e.id，
 e.name，
  COALESCE （a.InTime，cast（'  00:00'  as   TIME ）） as  InTime 
  FROM  empoyees e 
  CROSS   JOIN 日历c 
  LEFT   JOIN 出勤
  ON  a。 date  = c。 date  
  AND  a.empolyee_id = e.employee_id 

CROSS JOIN 将为您提供员工和日期的所有可能组合。然后 LEFT JOIN 将附加给定日期/员工的出勤记录（如果找到）， COALESCE 将提供默认值找不到。



在 SqlFiddle上查看此示例

除了Tomas的解决方案，您还可以使用递归查询，如下所示：

;   minmax  as （
  SELECT  转换（ DATE ，MIN（RecDate））mindt，
 转换（ DATE ，MAX（RecDate））maxdt  FROM  AtdRecord），
 alldates  AS （ SELECT  mindt  as  dt  FROM  minmax 
  UNION   ALL  
  SELECT  DateAdd（day，  1 ，a.dt）dt  FROM  alld a  cross   join  minmax m  WHERE  a.dt< m.maxdt 
），
次 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 
 < span class =code-keyword> 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 ），强制转换（[TimeOutSub]  -  InTimeSub  as  时间） ， 108 ）TotalWorkingTime 
，a.dt  AS  [DateVisited] 
，< span class =code-keyword> CASE   WHEN  [InTime]  IS   NOT   NULL   AND  [TimeOut]  IS   NOT   NULL  那么 
  CONVERT （ char （ 5 ）， CASE   WHEN  CAST（[TimeOutSub]  AS   DATETIME ）> = ShEndTime 和 ShiftID = '  S002' 然后  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 和 ShiftID = '  S001 ' 然后  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 表示 LEFT   JOIN  times  ON  a.dt = times。[DateVisited]  order   by  EmplID，ShiftID，a.dt 
  OPTION （MAXRECURSION  0 < /跨度>）

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.

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

解决方案

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:

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

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

;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)

这篇关于检查日期与时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！