gpt4 book ai didi

sql - 用 datepart(day) 求和一个不同的计数

转载 作者:行者123 更新时间:2023-12-01 04:54:28 28 4
gpt4 key购买 nike

这是我的问题。这是一家美黑沙龙,我们最近增加了水疗服务。客户过去一天只晒黑一次,因此按员工计算不同的客户非常容易。现在,他们一天进行不止一次晒黑/水疗,并且在任何一天都会看到不同的员工。我正在尝试将每天的独特客户数量汇总为一个数字。这是查询。我希望这是有道理的。第一次发帖请见谅?

此查询将生成每天他们进来的不同客户的计数以及哪个员工晒黑他们。 (这是正确的)我只需要按员工总结一下。如果我不包括日期部分,它会为整个日期范围选择不同的。

SELECT   COUNT(DISTINCT ClientUID) AS [# clients], DATEPART(day, DateOfTan) AS [day of tan], EmployeeUID
FROM History_TanHistory
WHERE DateOfTan > CONVERT(DATETIME, '2016-06-01 00:00:00', 102)
AND DateOfTan < CONVERT(DATETIME, '2016-07-01 00:00:00', 102)
AND Deleted = 0 AND Borrow = 0 AND AddedBack = 0 AND CanceledTan = 0
GROUP BY EmployeeUID, DATEPART(day, DateOfTan)

上面的查询产生这种类型的输出(没有底部的总和)
tan # 客户的 EmployeeUID 日
383-E11132012143712J1U 1 52
383-E11132012143712J1U 2 80
383-E11132012143712J1U 3 68
383-E11132012143712J1U 5 58
383-E11132012143712J1U 6 78
383-E11132012143712J1U 7 65
383-E11132012143712J1U 9 85
383-E11132012143712J1U 10 64
383-E11132012143712J1U 11 65
383-E11132012143712J1U 13 55
383-E11132012143712J1U 14 55
383-E11132012143712J1U 16 76
383-E11132012143712J1U 17 65
383-E11132012143712J1U 18 50
383-E11132012143712J1U 20 55
383-E11132012143712J1U 21 56
383-E11132012143712J1U 23 47
383-E11132012143712J1U 24 79
383-E11132012143712J1U 25 59
383-E11132012143712J1U 27 55
383-E11132012143712J1U 28 54
383-E11132012143712J1U 30 62

总计 1383

如果删除了日期部分,它会将整个 30 天的时间段视为一个分组,并且只返回该员工的不同计数 656。

我需要它来返回 1383 的总和。

上述员工的样本数据。客户 ID 可能在 davy 中使用服务超过 1 次,但我只想计算一次。

最佳答案

如果我理解正确,只需将您的查询包装为子查询。

这将返回一行和一个总数。

SELECT
SUM(T.[# clients]) AS TotalClients
FROM
(
SELECT COUNT(DISTINCT ClientUID) AS [# clients], DATEPART(day, DateOfTan) AS [day of tan], EmployeeUID
FROM History_TanHistory
WHERE DateOfTan > CONVERT(DATETIME, '2016-06-01 00:00:00', 102)
AND DateOfTan < CONVERT(DATETIME, '2016-07-01 00:00:00', 102)
AND Deleted = 0 AND Borrow = 0 AND AddedBack = 0 AND CanceledTan = 0
GROUP BY EmployeeUID, DATEPART(day, DateOfTan)
) AS T

如果你想要一个 SUM每个员工,只需添加另一个 GROUP BY .这将每 EmployeeUID 返回一行:
SELECT
T.EmployeeUID
,SUM(T.[# clients]) AS ClientsPerEmployee
FROM
(
SELECT COUNT(DISTINCT ClientUID) AS [# clients], DATEPART(day, DateOfTan) AS [day of tan], EmployeeUID
FROM History_TanHistory
WHERE DateOfTan > CONVERT(DATETIME, '2016-06-01 00:00:00', 102)
AND DateOfTan < CONVERT(DATETIME, '2016-07-01 00:00:00', 102)
AND Deleted = 0 AND Borrow = 0 AND AddedBack = 0 AND CanceledTan = 0
GROUP BY EmployeeUID, DATEPART(day, DateOfTan)
) AS T
GROUP BY T.EmployeeUID

关于sql - 用 datepart(day) 求和一个不同的计数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38448261/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com