問題描述

我希望計算每個人的連續日法術.

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;

這篇關于計算連續天數 SQL Server的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！