计算连续天数 SQL Server
我希望计算每个人的连续日法术.
I am looking to count consecutive day spells for each individual person.
我的桌子:
CREATE TABLE Absence(
Date Date,
Code varchar(10),
Name varchar(10),
Type varchar(10)
);
INSERT INTO Absence (Date, Code, Name, Type)
VALUES ('01-10-18', 'S', 'Sam', 'Sick'),
('01-11-18','S', 'Sam', 'Sick'),
('01-12-18','S', 'Sam', 'Sick'),
('01-21-18','S', 'Sam', 'Sick'),
('01-26-18','S', 'Sam', 'Sick'),
('01-27-18','S', 'Sam', 'Sick'),
('02-12-18','S', 'Sam', 'Holiday'),
('02-13-18','S', 'Sam', 'Holiday'),
('02-18-18','S', 'Sam', 'Holiday'),
('02-25-18','S', 'Sam', 'Holiday'),
('02-10-18','S', 'Sam', 'Holiday'),
('02-13-18','F', 'Fred', 'Sick'),
('02-14-18','F', 'Fred', 'Sick'),
('02-17-18','F', 'Fred', 'Sick'),
('02-25-18','F', 'Fred', 'Sick'),
('02-28-18','F', 'Fred', 'Sick');
这是我目前拥有的代码:
This is the code i currently have:
WITH CTE AS
(
SELECT
Date,
Name,
Type
,GroupingSet = DATEADD(DAY, ROW_NUMBER() OVER
(PARTITION BY [Name], [Type] ORDER BY [Date]), [Date])
FROM Absence
)
SELECT
Name,
StartDate = MIN(Date),
EndDate = MAX(Date),
Result = COUNT(Name),
min(Type) AS [Type]
FROM CTE
GROUP BY Name, GroupingSet
-- HAVING COUNT(NULLIF(Code, 0)) > 1
ORDER BY Name, StartDate
产生的结果:
| Name | StartDate | EndDate | Result | Type |
|------|------------|------------|--------|---------|
| Fred | 2018-02-13 | 2018-02-13 | 1 | Sick |
| Fred | 2018-02-14 | 2018-02-14 | 1 | Sick |
| Fred | 2018-02-17 | 2018-02-17 | 1 | Sick |
| Fred | 2018-02-25 | 2018-02-25 | 1 | Sick |
| Fred | 2018-02-26 | 2018-02-28 | 1 | Sick |
| Sam | 2018-01-10 | 2018-01-10 | 1 | Sick |
| Sam | 2018-01-11 | 2018-01-11 | 1 | Sick |
| Sam | 2018-01-12 | 2018-01-12 | 1 | Sick |
| Sam | 2018-01-21 | 2018-01-21 | 1 | Sick |
| Sam | 2018-01-26 | 2018-01-26 | 1 | Sick |
| Sam | 2018-01-27 | 2018-01-27 | 1 | Sick |
| Sam | 2018-02-10 | 2018-02-10 | 1 | Holiday |
| Sam | 2018-02-12 | 2018-02-12 | 1 | Holiday |
| Sam | 2018-02-13 | 2018-02-13 | 1 | Holiday |
| Sam | 2018-02-18 | 2018-02-18 | 1 | Holiday |
| Sam | 2018-02-25 | 2018-02-25 | 1 | Holiday |
我正在寻找这样的结果集:
Where as i am looking for a result set like this:
| Name | Date | Result | Type |
|------|------------|---------|---------|
| Fred | 2018-02-13 | 2 | Sick |
| Sam | 2018-01-27 | 2 | Sick |
| Sam | 2018-02-10 | 1 | Holiday |
我需要计算连续超过 1 天的连续天数.然后把这个作为一个人有多少连续法术的总数.例如在那段时间里，弗雷德连续两次生病.如果有人周五和周一休息，我也需要这个来覆盖，这应该算作连续的咒语.
I need to count the consecutive days where there is more then 1 day in a row. And then have this as a total of how many consecutive spells someone had. e.g. fred had 2 consecutive sick spells during that time period. I also need this to cover if someone had a friday and a monday off, this should count as a consecutive spell.
我对如何到达那里有点迷茫.任何帮助将不胜感激.
Im a bit lost as to how to get there. Any help would be appreciate.
请参阅:http://sqlfiddle.com/#!18/88612/16
推荐答案
您可以使用以下方法获取缺勤时间:
You can get the periods of absences using:
select name, min(date), max(date), count(*) as numdays, type
from (select a.*,
row_number() over (partition by name, type order by date) as seqnum_ct
from absence a
) a
group by name, type, dateadd(day, -seqnum_ct, date);
这里是一个 SQL Fiddle.
Here is a SQL Fiddle for this.
你可以添加有count(*)>1 获得一天或更长时间的经期.这似乎很有用.我不明白最终的输出是什么.描述对我来说没有意义.
You can add having count(*) > 1 to get periods with one day or more. This seems useful. I don't understand what the ultimate output is. The description just doesn't make sense to me.
如果您想要 2 天或更多天的缺勤次数，请将其用作子查询/CTE:
If you want the number of absences that are 2 or more days, then use this as a subquery/CTE:
select name, count(*), type
from (select name, min(date) as mindate, max(date) as maxdate, count(*) as numdays, type
from (select a.*,
row_number() over (partition by name, type order by date) as seqnum_ct
from absence a
) a
group by name, type, dateadd(day, -seqnum_ct, date)
) b
where numdays > 1
group by name, type;
相关文章