Question

;with cte as
( 
  select FingerId, [Date],
         LogTime,
         row_number() over(partition by FingerId order by LogTime) as rn
  from InOut 
)
select C3.EmployeeName,C1.FingerId,
       C1.LogTime as InTime,
       C2.LogTime as OutTime,
       C2.[Date]
from cte as C1
  left outer join cte as C2
    on C1.FingerId = C2.FingerId and
       C1.rn + 1 = C2.rn INNER JOIN
           EmployeeMaster as C3 ON C3.Fingerid = C2.Fingerid
where C1.rn % 2 = 1 and  C3.EmployeeName =  xyz  and C1.[Date] between  2011-07-21  and  2011-07-29  order by C2.[Date]

select * From Inout order by LogTime asc

我的简历它有5个记录,3个记录为2011-07-2011年。

InOuT Table:

AutoId     FingerId      LogTime            Date
1            22          11:18:48 AM       2011-07-29
2            22          11:19:54 AM       2011-07-29
3            22          11:20:50 AM       2011-07-21
4            22          11:21:54 AM       2011-07-21
5            22          11:21:59 AM       2011-07-21

我正在通过上述询问获得这一产出。

EmployeeName  FingerId   InTime         OutTime        Date

xyz             22        11:20:50 AM    11:21:54 AM   2011-07-21 
xyz             22        11:18:48 AM    11:19:54 AM   2011-07-29

<>0> 采用这种方式:-

EmployeeName  FingerId   InTime         OutTime        Date

xyz             22        11:20:50 AM    11:21:54 AM   2011-07-21
xyz             22        11:21:59 AM    ----          2011-07-21  
xyz             22        11:18:48 AM    11:19:54 AM   2011-07-29

在这里,第二行有时日,一想不时,就应当显示“--”干.。但是,我不想再问这个问题。事实是正确的,但需要加以修改。

Answer 1

我对你提出的问题做了一些修改,我现在以黑体表示的改动:

;WITH cte AS
( 
  SELECT FingerId, [Date],
         LogTime,
         ROW_NUMBER() OVER(PARTITION BY FingerId ORDER BY LogTime) AS rn
  FROM InOut 
)
SELECT C3.EmployeeName,C1.FingerId,
       C1.LogTime AS InTime,
       C2.LogTime AS OutTime,
       COALESCE(C2.[Date], C1.[Date]) AS Date
FROM cte AS C1
  LEFT OUTER JOIN cte AS C2 ON C1.FingerId = C2.FingerId AND C1.rn + 1 = C2.rn
  INNER JOIN EmployeeMaster AS C3 ON C3.Fingerid = C1.Fingerid
WHERE C1.rn % 2 = 1
  AND C3.EmployeeName =  xyz 
  AND C1.[Date] BETWEEN  2011-07-21  AND  2011-07-29 
ORDER BY C1.[Date]

最初,我想到的是将“C1.[Date]列入“SlectT”条款至C2.[Date]的改动,但我确信,如果两个日期不同,就足够替代。你们可以看到哪一种选择更好,如果有的话。

Answer 2

你必须使用C1.[Date],而不是C2.[Date],因为你是外人加入C2,而你失踪的牢房对C2具有全国人民力量的价值。

友情链接