主頁 > 資料庫 > MicrosoftSQLServer2016-T-SQL語言-'Gaps&Islands'問題-棘手的磁區

MicrosoftSQLServer2016-T-SQL語言-'Gaps&Islands'問題-棘手的磁區

2021-12-07 02:48:32 資料庫

我有 5 名員工的 ID 值為：111、222、333、444、555。

我有一個源資料集，其中包含 2021 年 3 月 28 日和 2021 年 4 月 5 日之間所有日歷日的所有五個資料集的資料。（這只是我實際資料的一個小樣本子集）。

您可以使用這些腳本來查看源資料的示例：

CREATE TABLE #SourceDataset
(
    EmployeeId int,
    PayGroupName varchar(100),
    PayTypeName varchar(100),
    SiteName varchar(100),
    Region varchar(100),
    PercentageScore int,
    Center int,
    [Function] varchar(100),
    Department varchar(100),
    EverySingleCalendarDate date
)

--- for Employee ID = 111 ---

INSERT INTO #SourceDataset
VALUES (111, 'Biweekly', 'Salaried', 'Shop Floor', 'NYC', 100, 17, 'WQ', 'FG', '3/28/2021'),
       (111, 'Biweekly', 'Salaried', 'Shop Floor', 'NYC', 100, 17, 'WQ', 'FG', '3/29/2021'),
       (111, 'Biweekly', 'Salaried', 'Shop Floor', 'NYC', 100, 17, 'WQ', 'FG', '3/30/2021'),
       (111, 'Biweekly', 'Salaried', 'Shop Floor', 'NYC', 100, 17, 'WQ', 'FG', '3/31/2021'),
       (111, 'Biweekly', 'Salaried', 'Shop Floor', 'NYC', 100, 17, 'WQ', 'FG', '4/1/2021'),
       (111, 'Biweekly', 'Salaried', 'Shop Floor', 'NYC', 50, 56, 'WQ', 'FG', '4/2/2021'),
       (111, 'Biweekly', 'Salaried', 'Shop Floor', 'NYC', 50, 80, 'WQ', 'FG', '4/2/2021'),
       (111, 'Biweekly', 'Salaried', 'Shop Floor', 'NYC', 50, 56, 'WQ', 'FG', '4/3/2021'),
(111,   'Biweekly', 'Salaried', 'Shop Floor',   'NYC',  50, 80, 'WQ',   'FG',   '4/3/2021'),
(111,   'Biweekly', 'Salaried', 'Shop Floor',   'NYC',  50, 56, 'WQ',   'FG',   '4/4/2021'),
(111,   'Biweekly', 'Salaried', 'Shop Floor',   'NYC',  50, 80, 'WQ',   'FG',   '4/4/2021'),
(111,   'Biweekly', 'Salaried', 'Shop Floor',   'NYC',  50, 56, 'WQ',   'FG',   '4/5/2021'),
(111,   'Biweekly', 'Salaried', 'Shop Floor',   'NYC',  50, 80, 'WQ',   'FG',   '4/5/2021')

--- for Employee ID = 111 ---

--- for Employee ID = 222 ---

INSERT INTO #SourceDataset

VALUES

(222,   'Monthly',  'PartTime', 'Office Room',  'POR',  100,    19, 'WQ',   'FG',   '3/28/2021'),
(222,   'Monthly',  'PartTime', 'Office Room',  'POR',  100,    19, 'WQ',   'FG',   '3/29/2021'),
(222,   'Monthly',  'PartTime', 'Office Room',  'POR',  100,    19, 'WQ',   'FG',   '3/30/2021'),
(222,   'Monthly',  'PartTime', 'Office Room',  'POR',  100,    20, 'WR',   'FY',   '3/31/2021'),
(222,   'Monthly',  'PartTime', 'Office Room',  'POR',  100,    20, 'WR',   'FY',   '4/1/2021'),
(222,   'Monthly',  'PartTime', 'Office Room',  'POR',  25, 19, 'RH',   'FG',   '4/2/2021'),
(222,   'Monthly',  'PartTime', 'Office Room',  'POR',  75, 19, 'DP',   'FG',   '4/2/2021'),
(222,   'Monthly',  'PartTime', 'Office Room',  'POR',  25, 19, 'RH',   'FG',   '4/3/2021'),
(222,   'Monthly',  'PartTime', 'Office Room',  'POR',  75, 19, 'DP',   'FG',   '4/3/2021'),
(222,   'Monthly',  'PartTime', 'Office Room',  'POR',  100,    19, 'WQ',   'FG',   '4/4/2021'),
(222,   'Monthly',  'PartTime', 'Office Room',  'POR',  100,    19, 'WQ',   'FG',   '4/5/2021')

--- for Employee ID = 222 ---

--- for Employee ID = 333 ---

INSERT INTO #SourceDataset

VALUES

(333, 'Weekly',    'Contract',  'Store',    'ATL',  100,    19, 'WQ',   'FG',   '3/28/2021'),
(333, 'Weekly PT', 'Contract',  'Store',    'ATL',  100,    19, 'WQ',   'FG',   '3/29/2021'),
(333, 'Weekly PT', 'Temporary', 'Store',    'ATL',  100,    19, 'WQ',   'FG',   '3/30/2021'),
(333, 'Weekly',    'Contract',  'Store',    'ATL',  100,    19, 'WQ',   'FG',   '3/31/2021'),
(333, 'Weekly',    'Contract',  'Store',    'ATL',  100,    19, 'WQ',   'FG',   '4/1/2021'),
(333, 'Weekly',    'Contract',  'Store',    'ATL',  40, 19, 'WQ',   'PQ',   '4/2/2021'),
(333, 'Weekly',    'Contract',  'Store',    'ATL',  60, 19, 'WQ',   'AD',   '4/2/2021'),
(333, 'Weekly',    'Contract',  'Store',    'ATL',  40, 19, 'WQ',   'PQ',   '4/3/2021'),
(333, 'Weekly',    'Contract',  'Store',    'ATL',  60, 19, 'WQ',   'AD',   '4/3/2021'),
(333, 'Weekly',    'Contract',  'Store',    'ATL',  40, 19, 'WQ',   'PQ',   '4/4/2021'),
(333, 'Weekly',    'Contract',  'Store',    'ATL',  60, 19, 'WQ',   'AD',   '4/4/2021'),
(333, 'Weekly',    'Contract',  'Store',    'ATL',  40, 19, 'WQ',   'PQ',   '4/5/2021'),
(333, 'Weekly',    'Contract',  'Store',    'ATL',  60, 19, 'WQ',   'AD',   '4/5/2021')

--- for Employee ID = 333 ---

--- for Employee ID = 444 ---

INSERT INTO #SourceDataset

VALUES

(444,   'Weekly',   'FT',           'StoreHouse',   'MIA',  100,    19, 'WQ',   'FG',   '3/28/2021'),
(444,   'Weekly',   'FT-Optional',  'StoreHouse',   'MIA',  100,    19, 'WQ',   'FG',   '3/29/2021'),
(444,   'Weekly',   'FT-Optional',  'StoreHouse',   'MIA',  100,    19, 'WQ',   'FG',   '3/30/2021'),
(444,   'Weekly',   'FT',           'StoreHouse',   'MIA',  100,    19, 'WQ',   'FG',   '3/31/2021'),
(444,   'Weekly',   'FT',           'StoreHouse',   'MIA',  100,    19, 'WQ',   'FG',   '4/1/2021'),
(444,   'Weekly',   'FT',           'StoreHouse',   'MIA',  50, 10, 'AB',   'FG',   '4/2/2021'),
(444,   'Weekly',   'FT',           'StoreHouse',   'MIA',  50, 11, 'HP',   'FG',   '4/2/2021'),
(444,   'Weekly',   'FT',           'StoreHouse',   'MIA',  50, 10, 'AB',   'FG',   '4/3/2021'),
(444,   'Weekly',   'FT',           'StoreHouse',   'MIA',  50, 11, 'HP',   'FG',   '4/3/2021'),
(444,   'Weekly',   'FT',           'StoreHouse',   'MIA',  65, 29, 'RR',   'FG',   '4/4/2021'),
(444,   'Weekly',   'FT',           'StoreHouse',   'MIA',  35, 37, 'QQ',   'FG',   '4/4/2021'),
(444,   'Weekly',   'FT',           'StoreHouse',   'MIA',  50, 10, 'AB',   'FG',   '4/5/2021'),
(444,   'Weekly',   'FT',           'StoreHouse',   'MIA',  50, 11, 'HP',   'FG',   '4/5/2021')

--- for Employee ID = 444 ---

--- for Employee ID = 555 ---

INSERT INTO #SourceDataset

VALUES

(555,   'Weekly',                   'FT',   'StoreHouse1',    'RIC',    100,    19, 'WQ',   'FG',   '3/28/2021'),
(555,   'WeeklyOptional',           'PT',   'StoreHouse2',    'RIC',    100,    19, 'WQ',   'FG',   '3/29/2021'),
(555,   'Weekly',                   'FT',   'StoreHouse1',    'RIC',    100,    19, 'WQ',   'FG',   '3/30/2021'),
(555,   'Weekly',                   'FT',   'StoreHouse1',    'RIC',    100,    19, 'WQ',   'FG',   '3/31/2021'),
(555,   'Weekly',                   'FT',   'StoreHouse1',    'RIC',    100,    19, 'WQ',   'FG',   '4/1/2021'),
(555,   'Weekly',                   'FT',   'StoreHouse1',    'RIC',    40, 23, 'BB',   'MA',   '4/2/2021'),
(555,   'Weekly',                   'FT',   'StoreHouse1',    'RIC',    25, 24, 'CV',   'RU',   '4/2/2021'),
(555,   'Weekly',                   'FT',   'StoreHouse1',    'RIC',    35, 25, 'FJ',   'BN',   '4/2/2021'),
(555,   'Weekly',                   'FT',   'StoreHouse1',    'RIC',    40, 23, 'BB',   'MA',   '4/3/2021'),
(555,   'Weekly',                   'FT',   'StoreHouse1',    'RIC',    25, 24, 'CV',   'RU',   '4/3/2021'),
(555,   'Weekly',                   'FT',   'StoreHouse1',    'RIC',    35, 25, 'FJ',   'BN',   '4/3/2021'),
(555,   'Weekly-NonOptional',           'FT',   'StoreHouse156',  'RIC',    33, 23, 'BB',   'MA',   '4/4/2021'),
(555,   'Weekly-NonOptional',           'FT',   'StoreHouse156',  'RIC',    33, 24, 'CV',   'RU',   '4/4/2021'),
(555,   'Weekly-NonOptional',           'FT',   'StoreHouse156',  'RIC',    34, 25, 'FJ',   'BN',   '4/4/2021'),
(555,   'Weekly',                   'FT',   'StoreHouse1',    'RIC',    40, 23, 'BB',   'MA',   '4/5/2021'),
(555,   'Weekly',                   'FT',   'StoreHouse1',    'RIC',    25, 24, 'CV',   'RU',   '4/5/2021'),
(555,   'Weekly',                   'FT',   'StoreHouse1',    'RIC',    35, 25, 'FJ',   'BN',   '4/5/2021')

--- for Employee ID = 555 ---

SELECT *
FROM #SourceDataset
ORDER BY EmployeeId, EverySingleCalendarDate

---------------------------------------------------

目標：我正在嘗試跟蹤員工如何在一段時間內獲得 100% 的百分比分數，并開發組來顯示此資訊，使用新的MinDate和MaxDate列，以顯示組存在的日期范圍。在此處提到的時間段內（2021 年 3 月 28 日至 2021 年 4 月 5 日），任何員工的日歷天數都沒有間隔。

關于源資料集的說明：

員工可以在給定日歷日的一行中獲得 100% 的百分比分數，每列都有一個唯一值 - 中心、職能、部門。或者，她可以在另一個日歷日獲得 100% 的百分比分數，這 3 列的不同組合 - 中心、職能、部門 - 在多行中，百分比分數拆分（例如，40 25 35 三行（或） 50 50 分兩行），該日歷日的總和為 100%。查看源資料集上的示例查詢以了解我的意思。

源資料集的開發方式是 - 每個日歷日必須占任何員工的 100%，并且日歷日??之間沒有間隙。對于員工而言，這 4 列（PayGroupName、PayTypeName、SiteName、Region）的更改僅發生在日歷日級別，而不是 PercentageScore 列級別或中心、功能、部門列級別。

Example:

SELECT *
FROM #SourceDataset
WHERE EmployeeId = 111
AND EverySingleCalendarDate = '2021-03-28'
--100 % Percentage Score in one row--

SELECT *
FROM #SourceDataset
WHERE EmployeeId = 111
AND EverySingleCalendarDate = '2021-04-02'
--100 % Percentage Score in two rows (50   50)--

SELECT *
FROM #SourceDataset
WHERE EmployeeId = 555
AND EverySingleCalendarDate = '2021-04-02'
--100 % Percentage Score in three rows (40   25   35)--

我的目標是跟蹤每個員工在一段時間內（從 3 月 28 日到 4 月 5 日）如何獲得 100% 的百分比分數，這些天的所有 5 名員工的資料都可用，并且在日歷天數中沒有間隔源資料集。

The number of rows for an employee on a given calendar day, will depend on how she attains 100 % Percentage Score, either in one row or multiple rows. The 'number of rows' (on a calendar day for any employee) will essentially depend on the set of 3 columns - Center, Function, Department.

Another important point: the columns - PayGroupName, PayTypeName, SiteName, Region - will ALWAYS have exactly one unique value for an employee on a given calendar day. So the number of rows for an employee on a given calendar is NOT determined by these columns - PayGroupName, PayTypeName, SiteName, Region.

This said, the data under each of these columns (PayGroupName, PayTypeName, SiteName, Region) can change between calendar days for an employee, and we need to track this too. See example queries on the source dataset below:

SELECT *
FROM #SourceDataset
WHERE EmployeeId = 333
AND EverySingleCalendarDate BETWEEN '2021-03-28' AND '2021-03-30'
--PayGroupName and PayTypeName change between days for this employee (333)--

SELECT *
FROM #SourceDataset
WHERE EmployeeId = 555
AND EverySingleCalendarDate BETWEEN '2021-04-03' AND '2021-04-04'
/*
PayGroupName and SiteName change between days for this employee (555);
and its' very important to to note that for an employee, the change for these 4 columns
(PayGroupName, PayTypeName, SiteName, Region) can happen only at the calendar day level, NOT at the
level of Percentage Score or at the level of the columns - Center, Function, Department.

In essence, the change for these 4 columns (PayGroupName, PayTypeName, SiteName, Region) for an
employee can happen ONLY at the level of a calendar day.
*/

A few weeks back, I posted a question on this same topic, but the data set was easier, with exactly one row for an employee on any given calendar day.

Today, I have a similar but a challenging situation, in which there can be multiple rows for an employee on a given calendar day. This is based on how an employee (say Employee ID = 111) attains 100 % Percentage Score, for a calendar day, say Mar 28, 2021.

I have the desired output dataset, both as a temp table using T-SQL and as a tabular output, so that you can get an absolute clear picture of the problem I have at hand.

Desired Output:

CREATE TABLE #DesiredOutput

(

EmployeeId int,
PayGroupName varchar(100),
PayTypeName varchar(100),
SiteName varchar(100),
Region varchar(100),
PercentageScore int,
Center int,
[Function] varchar(100),
Department varchar(100),
MinDate date,
MaxDate date

)


INSERT INTO #DesiredOutput

VALUES


(111,   'Biweekly',         'Salaried', 'Shop Floor',    'NYC', 100,    17, 'WQ',   'FG',   '3/28/2021',    '4/1/2021'),
(111,   'Biweekly',         'Salaried', 'Shop Floor',    'NYC', 50, 56, 'WQ',   'FG',   '4/2/2021', '4/5/2021'),
(111,   'Biweekly',         'Salaried', 'Shop Floor',    'NYC', 50, 80, 'WQ',   'FG',   '4/2/2021', '4/5/2021'),
(222,   'Monthly',              'PartTime', 'Office Room',   'POR', 100,    19, 'WQ',   'FG',   '3/28/2021',    '3/30/2021'),
(222,   'Monthly',          'PartTime', 'Office Room',   'POR', 100,    20, 'WR',   'FY',   '3/31/2021',    '4/1/2021'),
(222,   'Monthly',          'PartTime', 'Office Room',   'POR', 25, 19, 'RH',   'FG',   '4/2/2021', '4/3/2021'),
(222,   'Monthly',              'PartTime', 'Office Room',   'POR', 75, 19, 'DP',   'FG',   '4/2/2021', '4/3/2021'),
(222,   'Monthly',          'PartTime', 'Office Room',   'POR', 100,    19, 'WQ',   'FG',   '4/4/2021', '4/5/2021'),
(333,   'Weekly',           'Contract', 'Store',     'ATL', 100,    19, 'WQ',   'FG',   '3/28/2021',    '3/28/2021'),
(333,   'Weekly PT',            'Contract', 'Store',     'ATL', 100,    19, 'WQ',   'FG',   '3/29/2021',    '3/29/2021'),
(333,   'Weekly PT',            'Temporary',    'Store',         'ATL', 100,    19, 'WQ',   'FG',   '3/30/2021',    '3/30/2021'),
(333,   'Weekly',           'Contract', 'Store',         'ATL', 100,    19, 'WQ',   'FG',   '3/31/2021',    '4/1/2021'),
(333,   'Weekly',           'Contract', 'Store',     'ATL', 40, 19, 'WQ',   'PQ',   '4/2/2021', '4/5/2021'),
(333,   'Weekly',           'Contract', 'Store',     'ATL', 60, 19, 'WQ',   'AD',   '4/2/2021', '4/5/2021'),
(444,   'Weekly',           'FT',           'StoreHouse',    'MIA', 100,    19, 'WQ',   'FG',   '3/28/2021',    '3/28/2021'),
(444,   'Weekly',           'FT-Optional',  'StoreHouse',    'MIA', 100,    19, 'WQ',   'FG',   '3/29/2021',    '3/30/2021'),
(444,   'Weekly',           'FT',           'StoreHouse',    'MIA', 100,    19, 'WQ',   'FG',   '3/31/2021',    '4/1/2021'),
(444,   'Weekly',           'FT',           'StoreHouse',    'MIA', 50, 10, 'AB',   'FG',   '4/2/2021', '4/3/2021'),
(444,   'Weekly',           'FT',           'StoreHouse',    'MIA', 50, 11, 'HP',   'FG',   '4/2/2021', '4/3/2021'),
(444,   'Weekly',           'FT',           'StoreHouse',    'MIA', 65, 29, 'RR',   'FG',   '4/4/2021', '4/4/2021'),
(444,   'Weekly',           'FT',           'StoreHouse',    'MIA', 35, 37, 'QQ',   'FG',   '4/4/2021', '4/4/2021'),
(444,   'Weekly',           'FT',           'StoreHouse',    'MIA', 50, 10, 'AB',   'FG',   '4/5/2021', '4/5/2021'),
(444,   'Weekly',           'FT',           'StoreHouse',    'MIA', 50, 11, 'HP',   'FG',   '4/5/2021', '4/5/2021'),
(555,   'Weekly',           'FT',           'StoreHouse1',   'RIC', 100,    19, 'WQ',   'FG',   '3/28/2021',    '3/28/2021'),
(555,   'WeeklyOptional',   'PT',           'StoreHouse2',   'RIC', 100,    19, 'WQ',   'FG',   '3/29/2021',    '3/29/2021'),
(555,   'Weekly',           'FT',           'StoreHouse1',   'RIC', 100,    19, 'WQ',   'FG',   '3/30/2021',    '4/1/2021'),
(555,   'Weekly',           'FT',           'StoreHouse1',   'RIC', 40, 23, 'BB',   'MA',   '4/2/2021', '4/3/2021'),
(555,   'Weekly',           'FT',           'StoreHouse1',   'RIC', 25, 24, 'CV',   'RU',   '4/2/2021', '4/3/2021'),
(555,   'Weekly',           'FT',           'StoreHouse1',   'RIC', 35, 25, 'FJ',   'BN',   '4/2/2021', '4/3/2021'),
(555,   'Weekly-NonOptional',   'FT',           'StoreHouse156', 'RIC', 33, 23, 'BB',   'MA',   '4/4/2021', '4/4/2021'),
(555,   'Weekly-NonOptional',   'FT',           'StoreHouse156', 'RIC', 33, 24, 'CV',   'RU',   '4/4/2021', '4/4/2021'),
(555,   'Weekly-NonOptional',   'FT',           'StoreHouse156', 'RIC', 34, 25, 'FJ',   'BN',   '4/4/2021', '4/4/2021'),
(555,   'Weekly',           'FT',           'StoreHouse1',   'RIC', 40, 23, 'BB',   'MA',   '4/5/2021', '4/5/2021'),
(555,   'Weekly',           'FT',           'StoreHouse1',   'RIC', 25, 24, 'CV',   'RU',   '4/5/2021', '4/5/2021'),
(555,   'Weekly',           'FT',           'StoreHouse1',   'RIC', 35, 25, 'FJ',   'BN',   '4/5/2021', '4/5/2021')



SELECT *
FROM #DesiredOutput
ORDER BY EmployeeId, MinDate

I need to use #SourceDataset and convert it to #DesiredOutput.

I need some advice.

For example, the #DesiredOutput (in tabular form) looks as below for an employee (111), and for all employees:

EmployeeId PayGroupName PayTypeName SiteName Region PercentageScore Center Function Department MinDate MaxDate
111 Biweekly Salaried Shop Floor NYC 100 17 WQ FG 3/28/2021 4/1/2021
111 Biweekly Salaried Shop Floor NYC 50 56 WQ FG 4/2/2021 4/5/2021
111 Biweekly Salaried Shop Floor NYC 50 80 WQ FG 4/2/2021 4/5/2021
EmployeeId PayGroupName PayTypeName SiteName Region PercentageScore Center Function Department MinDate MaxDate
111 Biweekly Salaried Shop Floor NYC 100 17 WQ FG 3/28/2021 4/1/2021
111 Biweekly Salaried Shop Floor NYC 50 56 WQ FG 4/2/2021 4/5/2021
111 Biweekly Salaried Shop Floor NYC 50 80 WQ FG 4/2/2021 4/5/2021
222 Monthly PartTime Office Room POR 100 19 WQ FG 3/28/2021 3/30/2021
222 Monthly PartTime Office Room POR 100 20 WR FY 3/31/2021 4/1/2021
222 Monthly PartTime Office Room POR 25 19 RH FG 4/2/2021 4/3/2021
222 Monthly PartTime Office Room POR 75 19 DP FG 4/2/2021 4/3/2021
222 Monthly PartTime Office Room POR 100 19 WQ FG 4/4/2021 4/5/2021
333 Weekly Contract Store ATL 100 19 WQ FG 3/28/2021 3/28/2021
333 WeeklyPT Contract Store ATL 100 19 WQ FG 3/29/2021 3/29/2021
333 WeeklyPT Temporary Store ATL 100 19 WQ FG 3/30/2021 3/30/2021
333 Weekly Contract Store ATL 100 19 WQ FG 3/31/2021 4/1/2021
333 Weekly Contract Store ATL 40 19 WQ PQ 4/2/2021 4/5/2021
333 Weekly Contract Store ATL 60 19 WQ AD 4/2/2021 4/5/2021
444 Weekly FT StoreHouse MIA 100 19 WQ FG 3/28/2021 3/28/2021
444 Weekly FT-Optional StoreHouse MIA 100 19 WQ FG 3/29/2021 3/30/2021
444 Weekly FT StoreHouse MIA 100 19 WQ FG 3/31/2021 4/1/2021
444 Weekly FT StoreHouse MIA 50 10 AB FG 4/2/2021 4/3/2021
444 Weekly FT StoreHouse MIA 50 11 HP FG 4/2/2021 4/3/2021
444 Weekly FT StoreHouse MIA 65 29 RR FG 4/4/2021 4/4/2021
444 Weekly FT StoreHouse MIA 35 37 QQ FG 4/4/2021 4/4/2021
444 Weekly FT StoreHouse MIA 50 10 AB FG 4/5/2021 4/5/2021
444 Weekly FT StoreHouse MIA 50 11 HP FG 4/5/2021 4/5/2021
555 Weekly FT StoreHouse1 RIC 100 19 WQ FG 3/28/2021 3/28/2021
555 WeeklyOptional PT StoreHouse2 RIC 100 19 WQ FG 3/29/2021 3/29/2021
555 Weekly FT StoreHouse1 RIC 100 19 WQ FG 3/30/2021 4/1/2021
555 Weekly FT StoreHouse1 RIC 40 23 BB MA 4/2/2021 4/3/2021
555 Weekly FT StoreHouse1 RIC 25 24 CV RU 4/2/2021 4/3/2021
555 Weekly FT StoreHouse1 RIC 35 25 FJ BN 4/2/2021 4/3/2021
555 Weekly-NonOPTional FT StoreHouse156 RIC 33 23 BB MA 4/4/2021 4/4/2021
555 Weekly-NonOPTional FT StoreHouse156 RIC 33 24 CV RU 4/4/2021 4/4/2021
555 Weekly-NonOPTional FT StoreHouse156 RIC 34 25 FJ BN 4/4/2021 4/4/2021
555 Weekly FT StoreHouse1 RIC 40 23 BB MA 4/5/2021 4/5/2021
555 Weekly FT StoreHouse1 RIC 25 24 CV RU 4/5/2021 4/5/2021
555 Weekly FT StoreHouse1 RIC 35 25 FJ BN 4/5/2021 4/5/2021

Any idea on how to develop the desired output?

The table data at the lower end is the full table, which may be difficult to scroll through; so I have a smaller version for Employee ID = 111 at the top, and the entire desired output next. Alternately, you can use the T-SQL scripts to get an idea of the #DesiredOutput dataset.

uj5u.com熱心網友回復：

使用的方法：

  • 計算一個標志以指示超過 1 天的差距。
  • 創建基于總和標志的排名。
  • 將其分組，包括計算出的排名。
SELECT EmployeeId, PayGroupName, PayTypeName, SiteName, 
Region, PercentageScore,Center, [Function], Department
, MIN(EverySingleCalendarDate) MinDate
, MAX(EverySingleCalendarDate) MaxDate
INTO #tmpResults
FROM (
  SELECT *
  , Rnk = SUM(Flag) OVER (PARTITION BY EmployeeId, PayGroupName, PayTypeName, SiteName, 
Region, PercentageScore,Center, [Function], Department ORDER BY EverySingleCalendarDate)
  FROM
  (
     SELECT *
     , Flag = IIF(1=DATEDIFF(day, LAG(EverySingleCalendarDate) OVER (PARTITION BY EmployeeId, PayGroupName, PayTypeName, SiteName, 
Region, PercentageScore,Center, [Function], Department ORDER BY EverySingleCalendarDate), EverySingleCalendarDate),0,1)
     FROM #SourceDataset
  ) q1
) q2
GROUP BY EmployeeId, PayGroupName, PayTypeName, SiteName, 
Region, PercentageScore,Center, [Function], Department, Rnk
ORDER BY EmployeeId, MinDate;

SELECT * FROM #tmpResults;

--
-- Differences
--
SELECT * FROM #tmpResults
EXCEPT
SELECT * FROM #DesiredOutput;

SELECT * FROM #DesiredOutput
EXCEPT
SELECT * FROM #tmpResults;
GO

員工編號 | 支付組名稱 | 支付型別名稱 | 站點名稱 | 地區 | 百分比分數 | 中心 | 功能 | 部門 | 最小日期 | 最大日期
---------: | :----------- | :---------- | :------- | :----- | --------------: | -----: | :------- | :--------- | :------ | :------

員工編號 | 支付組名稱 | 支付型別名稱 | 站點名稱 | 地區 | 百分比分數 | 中心 | 功能 | 部門 | 最小日期 | 最大日期
---------: | :----------- | :---------- | :------- | :----- | --------------: | -----: | :------- | :--------- | :------ | :------

db<>在這里擺弄

uj5u.com熱心網友回復：

這正是@LukStorms 所提供的，但格式更好，以便任何人都可以使用此代碼。非常感謝您的支持。

SELECT

k.EmployeeId,k.PayGroupName,k.PayTypeName,k.SiteName,k.Region,k.PercentageScore,k.Center,k.[Function],k.Department,

MIN(k.EverySingleCalendarDate) AS MinDate,
MAX(k.EverySingleCalendarDate) AS MaxDate

FROM

(

SELECT

g.*,

SUM(g.Flag) OVER

(

PARTITION BY

g.EmployeeId,g.PayGroupName,g.PayTypeName,g.SiteName,g.Region,g.PercentageScore,g.Center,g.[Function],g.Department

ORDER BY g.EverySingleCalendarDate

) AS Rnk

FROM
 
(

SELECT

*,

Flag = CASE

           WHEN DATEDIFF(dd,

                          LAG(EverySingleCalendarDate,1) OVER

                          (PARTITION BY

                          EmployeeId,PayGroupName,PayTypeName,SiteName,Region,PercentageScore,Center,[Function],Department

                          ORDER BY EverySingleCalendarDate),

                          EverySingleCalendarDate) = 1 THEN 0

            ELSE 1

        END

FROM 

#SourceDataset

) g

) k

GROUP BY

k.EmployeeId,k.PayGroupName,k.PayTypeName,k.SiteName,k.Region,k.PercentageScore,k.Center,k.[Function],k.Department,
k.Rnk
--note the Rnk column in the GROUP BY
--ORDER BY k.EmployeeId, MinDateRange

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/374190.html

標籤：sql-server tsql partition gaps-and-islands row-number

上一篇：從一場比賽中在主場進球最多的球隊中選擇英格蘭球員的數量

下一篇：當關鍵字的where子句中選擇了不在表中的列的值時，選擇具有所需值的表中的一行？

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • GPU虛擬機創建時間深度優化

    **?桔妹導讀：**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題，由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視，實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶，一 ......

    uj5u.com 2020-09-10 06:09:13 more
  • 可編程網卡芯片在滴滴云網路的應用實踐

    **?桔妹導讀：**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高，采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐，遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......

    uj5u.com 2020-09-10 06:10:21 more
  • 滴滴資料通道服務演進之路

    **?桔妹導讀：**滴滴資料通道引擎承載著全公司的資料同步，為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加，資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程，遇到的問題以及今后的規劃。 #1. 背景 資料，對于任何一家互聯網公司來說都是非常重要的資產，公司的大資料 ......

    uj5u.com 2020-09-10 06:11:05 more
  • 滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三

    **桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......

    uj5u.com 2020-09-10 06:11:29 more
  • MPP (Massively Parallel Processing)大規模并行處理

    1、什么是mpp？ MPP (Massively Parallel Processing)，即大規模并行處理，在資料庫非共享集群中，每個節點都有獨立的磁盤存盤系統和記憶體系統，業務資料根據資料庫模型和應用特點劃分到各個節點上，每臺資料節點通過專用網路或者商業通用網路互相連接，彼此協同計算，作為整體提供 ......

    uj5u.com 2020-09-10 06:11:41 more
  • 滴滴資料倉庫指標體系建設實踐

    **桔妹導讀：**指標體系是什么？如何使用OSM模型和AARRR模型搭建指標體系？如何統一流程、規范化、工具化管理指標體系？本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標，系統化的組織起來，通 ......

    uj5u.com 2020-09-10 06:12:52 more
  • 單表千萬行資料庫 LIKE 搜索優化手記

    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......

    uj5u.com 2020-09-10 06:13:25 more
  • 滴滴Ceph分布式存盤系統優化之鎖優化

    **桔妹導讀：**Ceph是國際知名的開源分布式存盤系統，在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統， ......

    uj5u.com 2020-09-10 06:14:51 more
  • es~通過ElasticsearchTemplate進行聚合~嵌套聚合

    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章，這一次主要寫一個嵌套的聚合，例如先對sex集合，再對desc聚合，最后再對age求和，共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by，avg，sum等函式,Aggregation ......

    uj5u.com 2020-09-10 06:14:59 more
  • 爬蟲日志監控 -- Elastc Stack（ELK）部署

    傻瓜式部署，只需替換IP與用戶 導讀： 現ELK四大組件分別為：Elasticsearch（核心）、logstash（處理）、filebeat（采集）、kibana（可視化） 下載均在https://www.elastic.co/cn/downloads/下tar包，各組件版本最好一致，配合fdm會 ......

    uj5u.com 2020-09-10 06:15:05 more
最新发布
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:33:24 more
  • MySQL中binlog備份腳本分享

    關于MySQL的二進制日志（binlog），我們都知道二進制日志（binlog）非常重要，尤其當你需要point to point災難恢復的時侯，所以我們要對其進行備份。關于二進制日志（binlog)的備份,可以基于flush logs方式先切換binlog，然后拷貝&壓縮到到遠程服務器或本地服務器 ......

    uj5u.com 2023-04-20 08:28:06 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:27:27 more
  • 快取與資料庫雙寫一致性幾種策略分析

    本文將對幾種快取與資料庫保證資料一致性的使用方式進行分析。為保證高并發性能，以下分析場景不考慮執行的原子性及加鎖等強一致性要求的場景，僅追求最終一致性。 ......

    uj5u.com 2023-04-20 08:26:48 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:26:35 more
  • 云時代，MySQL到ClickHouse資料同步產品對比推薦

    ClickHouse 在執行分析查詢時的速度優勢很好的彌補了MySQL的不足，但是對于很多開發者和DBA來說，如何將MySQL穩定、高效、簡單的同步到 ClickHouse 卻很困難。本文對比了 NineData、MaterializeMySQL（ClickHouse自帶）、Bifrost 三款產品... ......

    uj5u.com 2023-04-20 08:26:29 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:25:13 more
  • Redis 報”OutOfDirectMemoryError“（堆外記憶體溢位)

    Redis 報錯“OutOfDirectMemoryError（堆外記憶體溢位) ”問題如下： 一、報錯資訊： 使用 Redis 的業務介面 ，產生 OutOfDirectMemoryError（堆外記憶體溢位），如圖： 格式化后的報錯資訊： { "timestamp": "2023-04-17 22: ......

    uj5u.com 2023-04-20 08:24:54 more
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:24:03 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:23:11 more

Copyright © 2010-2020 有解無憂