| ■No94935 (ファイター さん) に返信
> Name別に、STime順で並べて以下のようにしたいのですが
> 同一Nameの開始時間（Stime）が最終時間の時の Code です。
> MINUTES STime, ETime の差（分）で同一Nameで合計します。
Window関数を使うのがわかりやすいかな、と思います。
まず、提示されたデータ
DECLARE @table TABLE (
[Name] VARCHAR(8) NOT NULL,
[Code] INT NOT NULL,
[STime] DATETIME2 NOT NULL,
[ETime] DATETIME2 NOT NULL,
[MINUTES] INT NOT NULL
);
INSERT INTO @table ( [Name], [Code], [STime], [ETime], [MINUTES] ) VALUES
( 'name1', 4, '2020-04-13 11:20:49.153', '2020-04-13 12:09:10.733', 49 ),
( 'name1', 7, '2020-04-13 12:53:43.697', '2020-04-13 13:37:59.773', 44 ),
( 'name2', 4, '2020-04-09 12:04:06.270', '2020-04-09 12:09:18.353', 5 ),
( 'name2', 4, '2020-04-09 12:53:51.683', '2020-04-09 13:04:03.547', 11 ),
( 'name2', 7, '2020-04-13 11:13:15.313', '2020-04-13 11:20:48.140', 7 ),
( 'name3', 4, '2020-04-20 15:05:56.470', '2020-04-20 15:32:07.353', 27 )
;
に対して、
・[Name]ごとの[MINUTES]の合計
・[Name]ごとの[STime]逆順に並べた場合の行番号
を取得します。
SELECT
[Name], [Code], [STime], [ETime],
SUM([MINUTES]) OVER ( PARTITION BY [Name] ) [SUM_MINUTES], -- [Name]単位で[MINUTES]をSUM
ROW_NUMBER() OVER ( PARTITION BY [Name] ORDER BY [STime] DESC ) [ROWNUM] -- [Name]単位で[STime]逆順に並べた時のROW_NUMBER
FROM @table;
とすると、
Name Code STime ETime SUM_MINUTES ROWNUM
-------- ----------- --------------------------- --------------------------- ----------- --------------------
name1 7 2020-04-13 12:53:43.6970000 2020-04-13 13:37:59.7730000 93 1
name1 4 2020-04-13 11:20:49.1530000 2020-04-13 12:09:10.7330000 93 2
name2 7 2020-04-13 11:13:15.3130000 2020-04-13 11:20:48.1400000 23 1
name2 4 2020-04-09 12:53:51.6830000 2020-04-09 13:04:03.5470000 23 2
name2 4 2020-04-09 12:04:06.2700000 2020-04-09 12:09:18.3530000 23 3
name3 4 2020-04-20 15:05:56.4700000 2020-04-20 15:32:07.3530000 27 1
が得られます。
あとは必要なのがROWNUM = 1のものだけなので、先のSQLをWITHに放り込むかサブクエリにしてやれば出来上がりです。
WITH [TARGET] AS (
SELECT
[Name], [Code], [STime], [ETime],
SUM([MINUTES]) OVER ( PARTITION BY [Name] ) [SUM_MINUTES],
ROW_NUMBER() OVER ( PARTITION BY [Name] ORDER BY [STime] DESC ) [ROWNUM]
FROM @table
)
SELECT
[Name], [Code], [STime], [ETime], [SUM_MINUTES] [MINUTES]
FROM
[TARGET]
WHERE
[ROWNUM] = 1
ORDER BY
[Name];
Name Code STime ETime MINUTES
-------- ----------- --------------------------- --------------------------- -----------
name1 7 2020-04-13 12:53:43.6970000 2020-04-13 13:37:59.7730000 93
name2 7 2020-04-13 11:13:15.3130000 2020-04-13 11:20:48.1400000 23
name3 4 2020-04-20 15:05:56.4700000 2020-04-20 15:32:07.3530000 27
|