我正在尝试编写一个查询来查看包含 ID、日期和值的数据点。这些数据点代表员工的休假时间。
以下是一些示例数据点（请假请求）的示例：
+------------+-------+-------+-------------+
| Date | Value | Total | Status |
+------------+-------+-------+-------------+
| 8/20/2015 | 0.25 | 0.25 | |
| 1/11/2016 | 1 | 1.25 | |
| 5/27/2016 | 0.5 | 1.75 | |
| 8/5/2016 | 0.25 | 2 | |
| 9/13/2016 | 0 | 1.75 | |
| 9/28/2016 | 0.25 | 2 | |
| 9/29/2016 | 0.25 | 2.25 | |
| 10/17/2016 | 0 | 2.25 | |
| 10/17/2016 | 1 | 3.25 | Verbal |
| 11/17/2016 | 0.25 | 3.5 | |
| 12/1/2016 | 1 | 4.5 | Written |
| 12/2/2016 | 1 | 5.5 | Final |
| 12/6/2016 | 0.25 | 5.75 | |
| 12/14/2016 | 0.25 | 6 | Termination |
| 12/19/2016 | 0.25 | 6.25 | |
| 1/6/2017 | 0.25 | 6.5 | |
| 2/15/2017 | 0.25 | 5.75 | Final |
| 3/15/2017 | 0.25 | 6 | Termination |
| 4/17/2017 | 0.25 | 6.25 | |
| 5/8/2017 | 0 | 6.25 | |
| 5/9/2017 | 1 | 7.25 | |
| 5/21/2017 | 0.25 | 7.5 | |
| | | | |
+------------+-------+-------+-------------+
我想要一个计算上述结果的查询。总计列由当前数据点日期的 1 年内的先前数据点的总和计算。因此，如果当前数据点的日期为 2016 年 9 月 29 日，我想将 2015 年 9 月 29 日 - 2016 年 9 月 29 日之间的所有先前数据点相加。
我使用此查询的最终目标是查看一个人在过去一年内是否根据他们的休假时间（数据点）收到了两次最终警告。根据我提供的示例，在 12/2/2016 和 2/15/2017 发出了最终警告，它们发生在去年 (8/21/2016 - 8/21/2017)。
这样做的最佳方法是什么？
这是我目前拥有的：
SELECT
a.dtmDATEAPP AS [Date],
MAX(CASE WHEN a.dtmDATEAPP = b.dtmDATEAPP THEN a.strOCCUR END) AS [Value],
SUM(b.strOCCUR) AS Total,
CASE
WHEN SUM(b.strOCCUR) >= 5 AND SUM(b.strOCCUR) < 6
THEN 'Final'
END AS [Status]
FROM
[Attendance].[dbo].[TIMEUSE] a
JOIN
[Attendance].[dbo].[TIMEUSE] b ON b.dtmDATEAPP BETWEEN DATEADD(year, -1, a.dtmDATEAPP) AND a.dtmDATEAPP
WHERE
a.lngEMPID = 1162002
AND a.Absence <> 'Scheduled'
AND b.lngEMPID = 1162002
AND b.Absence <> 'Scheduled'
GROUP BY
a.dtmDATEAPP
ORDER BY
a.dtmDATEAPP
我不确定查看员工在去年是否收到 2 次最终警告的最佳途径。
非常感谢所有帮助。
像这样的事情可能会帮助你：
declare @TimeUsage table
(
missingDate date,
absence varchar(20),
pctDay decimal(6,2),
status varchar(20),
employeeid int
)
INSERT INTO @TimeUsage VALUES
('8/20/2015', 'Unscheduled', 0.25, '', 1162002),
('1/11/2016', 'Unscheduled', 1, '', 1162002),
('5/27/2016', 'Unscheduled', 0.5, '', 1162002),
('8/5/2016', 'Unscheduled', 0.25, '', 1162002),
('9/13/2016', 'Unscheduled', 0, '', 1162002),
('9/28/2016', 'Unscheduled', 0.25, '', 1162002),
('9/29/2016', 'Unscheduled', 0.25, '', 1162002),
('10/17/2016', 'Unscheduled', 0, '', 1162002),
('10/17/2016', 'Unscheduled', 1, 'Verbal', 1162002),
('11/17/2016', 'Unscheduled', 0.25, '', 1162002),
('12/1/2016', 'Unscheduled', 1, 'Written', 1162002),
('12/2/2016', 'Unscheduled', 1, 'Final', 1162002),
('12/6/2016', 'Unscheduled', 0.25, '', 1162002),
('12/14/2016', 'Unscheduled', 0.25, 'Termination', 1162002),
('12/19/2016', 'Unscheduled', 0.25, '', 1162002),
('1/6/2017', 'Unscheduled', 0.25, '', 1162002),
('2/15/2017', 'Unscheduled', 0.25, 'Final', 1162002),
('3/15/2017', 'Unscheduled', 0.25, 'Termination', 1162002),
('4/17/2017', 'Unscheduled', 0.25, '', 1162002),
('5/8/2017', 'Unscheduled', 0, '', 1162002),
('5/9/2017', 'Unscheduled', 1, '', 1162002),
('5/21/2017', 'Unscheduled', 0.25, '', 1162002)
;WITH cte AS
(SELECT missingDate, pctDay,
(SELECT SUM(pctDay) FROM @TimeUsage
WHERE missingDate <= d.missingDate
AND missingDate >= DATEADD(yy,-1,d.missingDate)
AND employeeid = d.employeeid
AND absence <> 'Scheduled') as prevYrTot,
(SELECT COUNT(*) FROM @TimeUsage
WHERE missingDate <= d.missingDate
AND missingDate >= DATEADD(yy,-1,d.missingDate)
AND employeeid = d.employeeid
AND status = 'Final') AS FinalCount,
status,employeeid
from @TimeUsage d)
SELECT * FROM cte where employeeid IN
(SELECT DISTINCT(employeeid) FROM cte WHERE FinalCount > 1)
编辑
如果最终状态由 prevyrtot 确定，请尝试以下操作：
declare @TimeUsage table
(
missingDate date,
absence varchar(20),
pctDay decimal(6,2),
employeeid int
)
INSERT INTO @TimeUsage VALUES
('8/20/2015', 'Unscheduled', 0.25, 1162002),
('1/11/2016', 'Unscheduled', 1, 1162002),
('5/27/2016', 'Unscheduled', 0.5, 1162002),
('8/5/2016', 'Unscheduled', 0.25, 1162002),
('9/13/2016', 'Unscheduled', 0, 1162002),
('9/28/2016', 'Unscheduled', 0.25, 1162002),
('9/29/2016', 'Unscheduled', 0.25, 1162002),
('10/17/2016', 'Unscheduled', 0, 1162002),
('10/17/2016', 'Unscheduled', 1, 1162002),
('11/17/2016', 'Unscheduled', 0.25, 1162002),
('12/1/2016', 'Unscheduled', 1, 1162002),
('12/2/2016', 'Unscheduled', 1, 1162002),
('12/6/2016', 'Unscheduled', 0.25, 1162002),
('12/14/2016', 'Unscheduled', 0.25, 1162002),
('12/19/2016', 'Unscheduled', 0.25, 1162002),
('1/6/2017', 'Unscheduled', 0.25, 1162002),
('2/15/2017', 'Unscheduled', 0.25, 1162002),
('3/15/2017', 'Unscheduled', 0.25, 1162002),
('4/17/2017', 'Unscheduled', 0.25, 1162002),
('5/8/2017', 'Unscheduled', 0, 1162002),
('5/9/2017', 'Unscheduled', 1, 1162002),
('5/21/2017', 'Unscheduled', 0.25, 1162002)
;WITH cte as
(SELECT missingDate, pctDay, prevYrTot, employeeid,
CASE WHEN prevYrTot >= 5 AND prevYrTot < 6
THEN 'Final' ELSE '' END AS Status FROM
(SELECT missingDate, pctDay,
(SELECT SUM(pctDay) FROM @TimeUsage
WHERE missingDate <= d.missingDate
AND missingDate >= DATEADD(yy,-1,d.missingDate)
AND employeeid = d.employeeid
AND absence <> 'Scheduled') as prevYrTot,
employeeid
from @TimeUsage d) tu)
SELECT missingDate, pctDay, prevYrTot, employeeid, Status FROM cte
WHERE employeeid IN
(SELECT DISTINCT employeeid FROM (SELECT employeeid, COUNT(*) as FinalCount FROM cte
WHERE Status = 'Final'
GROUP BY employeeid
HAVING Count(*) > 1) de)
我认为这可以满足您的要求，但您需要进行测试！它适用于给定的数据。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句