sql - 总结2个表的日期并比较它们并根据比较返回新列

问题描述

解决了：

得到了解决方案，尚未完善，但可以在前端进一步处理以显示！我基本上有 2 个选择来从一个表创建两个表，然后通过内部连接将它们连接起来，我在同一行中在同一天所有同一用户的戳入和戳出，是的。

SELECT * FROM 
(SELECT SUM(CASE WHEN DATEPART(YEAR, t.inStampDate) > 2018  THEN 1 ELSE 0 END) AS 'Stamps',
ISNULL(t.inStampDate, '') as 'inStampDate',
 t.employeeID
FROM dbo.timeRecording t
WHERE DATEPART(YEAR, t.inStampDate) > 2018
GROUP BY t.employeeID,
     t.inStampDate ) inStamp

INNER JOIN

(SELECT SUM(CASE WHEN DATEPART(YEAR, t.outStampDate) > 2018  THEN 1 ELSE 0 END) AS 'Stamps',
ISNULL(t.outStampDate, '') as 'outStampDate',
t.employeeID
FROM dbo.timeRecording t
WHERE DATEPART(YEAR, t.outStampDate) > 2018
GROUP BY t.employeeID,
     t.outStampDate ) outStamp

ON inStamp.inStampDate = outStamp.outStampDate AND inStamp.employeeID = outStamp.employeeID

以前的情况：

情况：我有可以登录或戳出的用户。正在记录这些值。现在我想显示用户在一天内总共签入或签出的频率。

我有一个表，其中有两个日期值StampInDate和StampOutDate. 还有一个employeeID. 我试图计算一天内进出的总金额。我已经可以向我显示每张邮票的总邮票数量，但我无法比较这些值并创建一个新列“totalStampsThatDay”，该列将填充当天的邮票数量。我得到的错误信息是：

消息 8120，级别 16，状态 1，第 17 行列 'dbo.timeRecording.employeeID' 在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。

有效的代码：

SELECT t.employeeID,
DATEPART(YEAR, t.inStampDate) AS 'InYear',                
DATEPART(MONTH, t.inStampDate) AS 'InMonth',              
DATEPART(DAY, t.inStampDate) AS 'InDay',
DATEPART(YEAR, t.outStampDate) AS 'OutYear',                  
DATEPART(MONTH, t.outStampDate) AS 'OutMonth',            
DATEPART(DAY, t.outStampDate) AS 'OutDay',
COUNT(t.inStampDate) AS 'StampIns',
COUNT( t.outStampDate) AS 'StampOuts'
FROM dbo.timeRecording t

GROUP BY  DATEPART(DAY, t.inStampDate),
          DATEPART(MONTH, t.inStampDate),
          DATEPART(YEAR, t.inStampDate),
          DATEPART(DAY, t.outStampDate),
          DATEPART(MONTH, t.outStampDate),
          DATEPART(YEAR, t.outStampDate),
          t.employeeID
ORDER BY t.employeeID

现在我尝试编辑它以实现我想要的但失败了：

SELECT tr.StampIns,
tr.StampOuts,
tr.eID,
t.employeeID,
DATEPART(YEAR, t.inStampDate) AS 'InYear',                
DATEPART(MONTH, t.inStampDate) AS 'InMonth',              
DATEPART(DAY, t.inStampDate) AS 'InDay',
DATEPART(YEAR, t.outStampDate) AS 'OutYear',                  
DATEPART(MONTH, t.outStampDate) AS 'OutMonth',            
DATEPART(DAY, t.outStampDate) AS 'OutDay'

FROM dbo.timeRecording t
JOIN
    (SELECT 
        COUNT(tr.inStampDate) AS 'StampIns',
        COUNT(tr.outStampDate) AS 'StampOuts',
        tr.employeeID as 'eID'
        FROM dbo.timeRecording tr
    ) tr ON tr.eID = t.employeeID

GROUP BY  DATEPART(DAY, t.inStampDate),
          DATEPART(MONTH, t.inStampDate),
          DATEPART(YEAR, t.inStampDate),
          DATEPART(DAY, t.outStampDate),
          DATEPART(MONTH, t.outStampDate),
          DATEPART(YEAR, t.outStampDate),
          t.employeeID,
          tr.eID,
          tr.StampIns,
          tr.StampOuts

ORDER BY t.employeeID

就像是：

employeeID | date    | totalStampsThatDay
1          | 21.11.19| 2
2          | 21.11.19| 0
3          | 22.11.19| 1
1          | 22.11.19| 1

... 等等

---- 更新---- 几乎得到了我想要的。我只需要为日期添加一列，例如 2019-11-19，但我现在无法这样做。

SELECT t.employeeID,
COUNT(t.inStampDate)  + COUNT( t.outStampDate) as 'TotalStamps',
SUM(CASE WHEN DATEPART(YEAR, t.inStampDate) > 2018  THEN 1 ELSE 0 END) AS 'StampIns',
SUM(CASE WHEN DATEPART(YEAR, t.outStampDate) > 2018  THEN 1 ELSE 0 END) AS 'StampOuts'
FROM dbo.timeRecording t
WHERE DATEPART(YEAR, t.inStampDate) > 2018 OR DATEPART(YEAR, t.outStampDate) > 2018 
GROUP BY t.employeeID

到目前为止我的输出：

 employeeID | Total | In | Out
 7          | 9     | 6  | 3
 8          | 105   | 48 | 57
 11         | 6     | 5  | 1

并且想要：

employeeID | Date       | Total | In | Out
7          | 2019-11-19 | 2     | 1  | 1
7          | 2019-11-20 | 3     | 2  | 1
11         | 2019-11-19 | 1     | 1  | 0
11         | 2019-11-16 | 2     | 1  | 1

标签： sqlsql-server

SELECT t.employeeID, DATEPART(YEAR, t.inStampDate) AS 'InYear', DATEPART(MONTH, t.inStampDate) AS 'InMonth', DATEPART(DAY, t.inStampDate) AS 'InDay', DATEPART(YEAR, t.outStampDate) AS 'OutYear', DATEPART(MONTH, t.outStampDate) AS 'OutMonth', DATEPART(DAY, t.outStampDate) AS 'OutDay', COUNT(t.inStampDate) + COUNT( t.outStampDate) as 'TotalStamps', FROM dbo.timeRecording t GROUP BY DATEPART(DAY, t.inStampDate), DATEPART(MONTH, t.inStampDate), DATEPART(YEAR, t.inStampDate), DATEPART(DAY, t.outStampDate), DATEPART(MONTH, t.outStampDate), DATEPART(YEAR, t.outStampDate), t.employeeID ORDER BY t.employeeID

sql - 总结2个表的日期并比较它们并根据比较返回新列

问题描述

解决方案

推荐阅读