查询在SQL Server中为一个日期仅选择一行

I need a help to select only last one updated row for the punch in/out for one day.

我需要一个帮助来选择最后一个更新的行，用于打卡/换出一天。

I'm using this query:

我正在使用此查询：

SELECT     
    convert(nvarchar(20), EmpID) as EmpID, ID, 
    DATENAME(dw, date1) AS day, date1 AS date, 
    PunchDateTime AS timein,  
    COALESCE((SELECT MAX(PunchDateTime) AS Expr1
              FROM PunchData AS b 
              WHERE empid = '61039' 
                AND (EmpID = a.EmpID)  
                AND (PunchType = 4) 
                AND PunchDateTime between a.PunchDateTime and dateadd(hour, 17, a.PunchDateTime)), 
             (SELECT MIN(PunchDateTime) AS Expr1
              FROM PunchData AS b
              WHERE empid = '61039'
                AND (EmpID = a.EmpID) 
                AND (ID >= a.ID) 
                AND (PunchType = 1) 
                AND date1 = a.date1), 0)  AS timeout
FROM         
    dbo.PunchData AS a
WHERE    
    empid = '61039'
    AND (PunchType = 1)
GROUP BY 
    EmpID, ID, DATENAME(dw, date1), date1, PunchDateTime

UNION ALL

SELECT     
    convert(nvarchar(20), EmpID) as EmpID, ID, 
    DATENAME(dw, dateadd(hour, -12, PunchDateTime)) AS day,
    convert(date, COALESCE((SELECT Min(PunchDateTime) AS Expr1
                            FROM PunchData AS b 
                            WHERE EmpID = '61039'
                              AND (EmpID = a.EmpID)  
                              AND (PunchType = 1) 
                              AND PunchDateTime between dateadd(hour, -17,  a.PunchDateTime)  
                                                    and a.PunchDateTime),
                           dateadd(hour, -12, PunchDateTime))) AS date,  
    COALESCE((SELECT Min(PunchDateTime) AS Expr1
              FROM PunchData AS b 
              WHERE EmpID = '61039' 
                AND (EmpID = a.EmpID)  
                AND (PunchType = 1) 
                AND PunchDateTime between dateadd(hour, -17, a.PunchDateTime)  
                                      and a.PunchDateTime), 
             (SELECT Max(PunchDateTime) AS Expr1
              FROM PunchData AS b
              WHERE EmpID = '61039'
                AND (EmpID = a.EmpID) 
                AND (ID = a.ID) 
                AND (PunchType = 4) 
                AND date1 = a.date1), 0)  AS timein,
    PunchDateTime as timeout  
FROM         
    dbo.PunchData AS a
WHERE     
    (PunchType = 4) 
    AND empid = '61039'
GROUP BY 
    EmpID, ID, DATENAME(dw, date1), date1, PunchDateTime

SQL output image

SQL输出图像

1 个解决方案

#1

You could wrap the whole query in a CTE and then do a subsequent select from the CTE perhaps?

您可以将整个查询包装在CTE中，然后从CTE中进行后续选择吗？

something like this perhaps :

也许这样的事情：

;with mycte as (
select 1 as ID , '2017-08-01' as date1 
union all 
select 1 as ID , '2017-08-02' as date1 
)

Select distinct mycte.*
 from mycte
 inner join mycte mycte2
    on mycte.ID = mycte2.ID
        and mycte.date1 = (select max(mycte3.date1) from mycte mycte3 where mycte3.id = mycte.ID)

#1