主頁 > 軟體設計 > MicrosoftSQLServer2016-T-SQL語言-'Gaps&Islands'問題-棘手的磁區

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

2021-12-02 20:00:15 軟體設計

我有 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/ruanti/371565.html

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

上一篇：COUNT作為Window函式在SQLServer上引發錯誤msg4403

下一篇：向開發分支提交新更改的問題

標籤雲
其他(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)

熱門瀏覽
  • 面試突擊第一季，第二季，第三季

    第一季必考 https://www.bilibili.com/video/BV1FE411y79Y?from=search&seid=15921726601957489746 第二季分布式 https://www.bilibili.com/video/BV13f4y127ee/?spm_id_fro ......

    uj5u.com 2020-09-10 05:35:24 more
  • 第三單元作業總結

    1.前言 這應該是本學期最后一次寫作業總結了吧。總體來說，對作業的節奏也差不多掌握了，作業做起來的效率也更高了。雖然和之前的作業一樣，作業中都要用到新的知識，但是相比之前，更加懂得了如何利用工具以及資料。雖然之間卡過殼，但總體而言，這幾次作業還算完成的比較好。 2.作業程序總結 相比前兩個單元，此單 ......

    uj5u.com 2020-09-10 05:35:41 more
  • 北航OO（2020）第四單元博客作業暨課程總結博客

    北航OO（2020）第四單元博客作業暨課程總結博客 本單元作業的架構設計 在本單元中，由于UML圖具有比較清晰的樹形結構，因此我對其中需要進行查詢操作的元素進行了包裝，在樹的父節點中存盤所有孩子的參考。考慮到性能問題，我采用了快取機制，一次查詢后盡可能快取已經遍歷過的資訊，以減少遍歷次數。 本單元我 ......

    uj5u.com 2020-09-10 05:35:48 more
  • BUAA_OO_第四單元

    一、UML決議器設計 ? 先看下題目：第四單元實作一個基于JDK 8帶有效性檢查的UML(Unified Modeling Language)類圖，順序圖，狀態圖分析器 MyUmlInteraction，實際上我們要建立一個有向圖模型，UML中的物件（元素）可能與同級元素連接，也可與低級元素相連形成 ......

    uj5u.com 2020-09-10 05:35:54 more
  • 6.1邏輯運算子

    邏輯運算子 1. && 短路與 運算式1 && 運算式2 01.運算式1為true并且運算式2也為true 整體回傳為true 02.運算式1為false，將不會執行運算式2 整體回傳為false 03.只要有一個運算式為false 整體回傳為false 2. || 短路或 運算式1 || 運算式2 ......

    uj5u.com 2020-09-10 05:35:56 more
  • BUAAOO 第四單元 & 課程總結

    1. 第四單元：StarUml檔案決議 本單元采用了圖模型決議UML。 UML檔案可以抽象為圖、子圖、邊的邏輯結構。 在實作中，圖的節點包括類、介面、屬性，子圖包括狀態圖、順序圖等。 采用了三次遍歷UML元素的方法建圖，第一遍遍歷建點，第二、三次遍歷設定屬性、連邊，實作圖物件的初始化。這里借鑒了一些 ......

    uj5u.com 2020-09-10 05:36:06 more
  • 談談我對C# 多型的理解

    面向物件三要素：封裝、繼承、多型。 封裝和繼承，這兩個比較好理解，但要理解多型的話，可就稍微有點難度了。今天，我們就來講講多型的理解。 我們應該經常會看到面試題目：請談談對多型的理解。 其實呢，多型非常簡單，就一句話：呼叫同一種方法產生了不同的結果。 具體實作方式有三種。 一、多載 多載很簡單。 p ......

    uj5u.com 2020-09-10 05:36:09 more
  • Python 資料驅動工具：DDT

    背景 python 的unittest 沒有自帶資料驅動功能。 所以如果使用unittest，同時又想使用資料驅動，那么就可以使用DDT來完成。 DDT是 “Data-Driven Tests”的縮寫。 資料：http://ddt.readthedocs.io/en/latest/ 使用方法 dd. ......

    uj5u.com 2020-09-10 05:36:13 more
  • Python里面的xlrd模塊詳解

    那我就一下面積個問題對xlrd模塊進行學習一下： 1.什么是xlrd模塊？ 2.為什么使用xlrd模塊？ 3.怎樣使用xlrd模塊？ 1.什么是xlrd模塊？ ?python操作excel主要用到xlrd和xlwt這兩個庫，即xlrd是讀excel，xlwt是寫excel的庫。 今天就先來說一下xl ......

    uj5u.com 2020-09-10 05:36:28 more
  • 當我們創建HashMap時，底層到底做了什么？

    jdk1.7中的底層實作程序（底層基于陣列+鏈表） 在我們new HashMap()時，底層創建了默認長度為16的一維陣列Entry[ ] table。當我們呼叫map.put(key1,value1)方法向HashMap里添加資料的時候： 首先，呼叫key1所在類的hashCode()計算key1 ......

    uj5u.com 2020-09-10 05:36:38 more
最新发布
  • 【中介者設計模式詳解】C/Java/JS/Go/Python/TS不同語言實作

    * 中介者模式是一種行為型設計模式，它可以用來減少類之間的直接依賴關系，
    * 將物件之間的通信封裝到一個中介者物件中，從而使得各個物件之間的關系更加松散。
    * 在中介者模式中，物件之間不再直接相互互動，而是通過中介者來中轉訊息。 ......

    uj5u.com 2023-04-20 08:20:47 more
  • 露天煤礦現場調研和交流案例分享

    他們集團的資訊化公司及研究院在一個礦區正在做智能礦山的統一平臺的 試點，專案投資大概1億，包括了礦山的各方面的內容，顯示得我們這次交流有點多余。他們2年前開始做智能礦山的規劃，有很多煤礦行業專家的加持，他們的描述是非常完美，但是去年底應該上線的平臺，現在還沒有看到影子。他們確實有很多場景需求，但是被... ......

    uj5u.com 2023-04-20 08:20:25 more
  • 《社區人員管理》實戰案例設計&個人案例分享

    設計是一個讓人夢想成真程序，開始編碼、測驗、除錯之前進行需求分析和架構設計，才能保證關鍵方面都做正確 ......

    uj5u.com 2023-04-20 08:20:17 more
  • 軟體架構生態化-多角色交付的探索實踐

    作為一個技術架構師，不僅僅要緊跟行業技術趨勢，還要結合研發團隊現狀及痛點，探索新的交付方案。在日常中，你是否遇到如下問題 “ 業務需求排期長研發是瓶頸；非研發角色感受不到研發技改提效的變化；引入ISV 團隊又擔心質量和安全，培訓周期長“等等，基于此我們探索了一種新的技術體系及交付方案來解決如上問題。 ......

    uj5u.com 2023-04-20 08:20:10 more
  • 【中介者設計模式詳解】C/Java/JS/Go/Python/TS不同語言實作

    * 中介者模式是一種行為型設計模式，它可以用來減少類之間的直接依賴關系，
    * 將物件之間的通信封裝到一個中介者物件中，從而使得各個物件之間的關系更加松散。
    * 在中介者模式中，物件之間不再直接相互互動，而是通過中介者來中轉訊息。 ......

    uj5u.com 2023-04-20 08:19:44 more
  • 露天煤礦現場調研和交流案例分享

    他們集團的資訊化公司及研究院在一個礦區正在做智能礦山的統一平臺的 試點，專案投資大概1億，包括了礦山的各方面的內容，顯示得我們這次交流有點多余。他們2年前開始做智能礦山的規劃，有很多煤礦行業專家的加持，他們的描述是非常完美，但是去年底應該上線的平臺，現在還沒有看到影子。他們確實有很多場景需求，但是被... ......

    uj5u.com 2023-04-20 08:19:07 more
  • 《社區人員管理》實戰案例設計&個人案例分享

    設計是一個讓人夢想成真程序，開始編碼、測驗、除錯之前進行需求分析和架構設計，才能保證關鍵方面都做正確 ......

    uj5u.com 2023-04-20 08:18:57 more
  • 軟體架構生態化-多角色交付的探索實踐

    作為一個技術架構師，不僅僅要緊跟行業技術趨勢，還要結合研發團隊現狀及痛點，探索新的交付方案。在日常中，你是否遇到如下問題 “ 業務需求排期長研發是瓶頸；非研發角色感受不到研發技改提效的變化；引入ISV 團隊又擔心質量和安全，培訓周期長“等等，基于此我們探索了一種新的技術體系及交付方案來解決如上問題。 ......

    uj5u.com 2023-04-20 08:18:49 more
  • 05單件模式

    #經典的單件模式 public class Singleton { private static Singleton uniqueInstance; //一個靜態變數持有Singleton類的唯一實體。 // 其他有用的實體變數寫在這里 //構造器宣告為私有，只有Singleton可以實體化這個類! ......

    uj5u.com 2023-04-19 08:42:51 more
  • 【架構與設計】常見微服務分層架構的區別和落地實踐

    軟體工程的方方面面都遵循一個最基本的道理：沒有銀彈，架構分層模型更是如此，每一種都有各自優缺點，所以請根據不同的業務場景，并遵循簡單、可演進這兩個重要的架構原則選擇合適的架構分層模型即可。 ......

    uj5u.com 2023-04-19 08:42:41 more

Copyright © 2010-2020 有解無憂