使用ROW_NUMBER() Over查找最接近0或更早日期的匹配。

huangapple go评论61阅读模式
英文:

Using ROW_NUMBER() Over to find closest date match to 0 or earlier

问题

以下是您要翻译的内容:

"I have a need to compare all the recorded dates in one column to a deadline date in the other column. I can do a ROW_NUMBER() OVER to find the closest date, however, I cannot go past the deadline date. I know I can add in the WHERE clause "WHERE recorded_date <= deadline_date", but I need to show all the data for auditing purposes.

Here is the CREATE TABLE:

CREATE TABLE Vitals (
Player	nvarchar(50),
Vital	nvarchar(20),
Val	int,
Entry_Date	datetime,
Game_Date datetime
);

Here is the INSERT Values:

INSERT INTO Vitals (Player, Vital, Val, Entry_Date, Game_Date)

VALUES

('George', 'Weight', 227, '20230525 12:02:10', '6/1/23 20:00:00'),
('George', 'Weight', 226, '20230421 16:10:22', '6/1/23 20:00:00'),
('George', 'Weight', 227, '20230508 14:27:01', '6/1/23 20:00:00'),
('George', 'Weight', 228, '20230527 08:45:55', '6/1/23 20:00:00'),
('George', 'Weight', 230, '20230601 11:42:23', '6/1/23 20:00:00'),
('George', 'Weight', 229, '20230605 17:07:27', '6/1/23 20:00:00'),
('George', 'Weight', 223, '20230612 07:30:02', '6/1/23 20:00:00'),
('George', 'Weight', 228, '20230601 18:10:05', '6/1/23 20:00:00'),
('George', 'Weight', 229, '20230524 10:15:19', '6/1/23 20:00:00'),
('George', 'Weight', 228, '20230506 11:20:13', '6/1/23 20:00:00'),
('George', 'Weight', 230, '20230602 16:18:33', '6/1/23 20:00:00')

Here is the SQL Code I am currently using:

SELECT
 Player
,Vital
,Val
,Entry_Date
,Game_Date
,DATEDIFF(DAY, Game_Date, Entry_Date) 'Days Diff'
,ROW_NUMBER() OVER (PARTITION BY Player ORDER BY ABS(DATEDIFF(DAY, Game_Date, Entry_Date)) ASC) RN

FROM Vitals;

Here is what I am I trying to achieve:

Player Vital Val Entry_Date Game_Date Days Diff RN
George Weight 228 6/1/23 18:10 6/1/23 20:00 0 1
George Weight 230 6/1/23 11:42 6/1/23 20:00 0 2
George Weight 228 5/27/23 8:45 6/1/23 20:00 -5 3
George Weight 227 5/25/23 12:02 6/1/23 20:00 -7 4
George Weight 229 5/24/23 10:15 6/1/23 20:00 -8 5
George Weight 227 5/8/23 14:27 6/1/23 20:00 -24 6
George Weight 228 5/6/23 11:20 6/1/23 20:00 -26 7
George Weight 226 4/21/23 16:10 6/1/23 20:00 -41 8
George Weight 230 6/2/23 16:18 6/1/23 20:00 1 9
George Weight 229 6/5/23 17:07 6/1/23 20:00 4 10
George Weight 223 6/12/23 7:30 6/1/23 20:00 10 11

As you can see, the row numbering is first ordering the Entry Dates BEFORE Game Date first then ordering the dates after the Game Date (missed the deadline, but have to show by how much to auditors). I have tried different ways to use Case statements in the Order By, but it doesn't work. Any suggestions would be appreciated. Thank you.

p.s., Note the difference between row number 1 and 2. Number 1 is very close to game time. I need to order closest to game time as possible."

英文:

I have a need to compare all the recorded dates in one column to a deadline date in the other column. I can do a ROW_NUMBER() OVER to find the closest date, however, I cannot go past the deadline date. I know I can add in the WHERE clause "WHERE recorded_date <= deadline_date", but I need to show all the data for auditing purposes.

Here is the CREATE TABLE:

CREATE TABLE Vitals (
Player	nvarchar(50),
Vital	nvarchar(20),
Val	int,
Entry_Date	datetime,
Game_Date datetime
);

Here is the INSERT Values:

INSERT INTO Vitals (Player, Vital, Val, Entry_Date, Game_Date)

VALUES

(&#39;George&#39;, &#39;Weight&#39;, 227, &#39;20230525 12:02:10&#39;, &#39;6/1/23 20:00:00&#39;),
(&#39;George&#39;, &#39;Weight&#39;, 226, &#39;20230421 16:10:22&#39;, &#39;6/1/23 20:00:00&#39;),
(&#39;George&#39;, &#39;Weight&#39;, 227, &#39;20230508 14:27:01&#39;, &#39;6/1/23 20:00:00&#39;),
(&#39;George&#39;, &#39;Weight&#39;, 228, &#39;20230527 08:45:55&#39;, &#39;6/1/23 20:00:00&#39;),
(&#39;George&#39;, &#39;Weight&#39;, 230, &#39;20230601 11:42:23&#39;, &#39;6/1/23 20:00:00&#39;),
(&#39;George&#39;, &#39;Weight&#39;, 229, &#39;20230605 17:07:27&#39;, &#39;6/1/23 20:00:00&#39;),
(&#39;George&#39;, &#39;Weight&#39;, 223, &#39;20230612 07:30:02&#39;, &#39;6/1/23 20:00:00&#39;),
(&#39;George&#39;, &#39;Weight&#39;, 228, &#39;20230601 18:10:05&#39;, &#39;6/1/23 20:00:00&#39;),
(&#39;George&#39;, &#39;Weight&#39;, 229, &#39;20230524 10:15:19&#39;, &#39;6/1/23 20:00:00&#39;),
(&#39;George&#39;, &#39;Weight&#39;, 228, &#39;20230506 11:20:13&#39;, &#39;6/1/23 20:00:00&#39;),
(&#39;George&#39;, &#39;Weight&#39;, 230, &#39;20230602 16:18:33&#39;, &#39;6/1/23 20:00:00&#39;)

Here is the SQL Code I am currently using:

SELECT
 Player
,Vital
,Val
,Entry_Date
,Game_Date
,DATEDIFF(DAY, Game_Date, Entry_Date) &#39;Days Diff&#39;
,ROW_NUMBER() OVER (PARTITION BY Player ORDER BY ABS(DATEDIFF(DAY, Game_Date, Entry_Date)) ASC) RN

FROM Vitals;

Here is what I am I trying to achieve:

Player Vital Val Entry_Date Game_Date Days Diff RN
George Weight 228 6/1/23 18:10 6/1/23 20:00 0 1
George Weight 230 6/1/23 11:42 6/1/23 20:00 0 2
George Weight 228 5/27/23 8:45 6/1/23 20:00 -5 3
George Weight 227 5/25/23 12:02 6/1/23 20:00 -7 4
George Weight 229 5/24/23 10:15 6/1/23 20:00 -8 5
George Weight 227 5/8/23 14:27 6/1/23 20:00 -24 6
George Weight 228 5/6/23 11:20 6/1/23 20:00 -26 7
George Weight 226 4/21/23 16:10 6/1/23 20:00 -41 8
George Weight 230 6/2/23 16:18 6/1/23 20:00 1 9
George Weight 229 6/5/23 17:07 6/1/23 20:00 4 10
George Weight 223 6/12/23 7:30 6/1/23 20:00 10 11

As you can see, the row numbering is first ordering the Entry Dates BEFORE Game Date first then ordering the dates after the Game Date (missed the deadline, but have to show by how much to auditors). I have tried different ways to use Case statements in the Order By, but it doesn't work. Any suggestions would be appreciated. Thank you.

p.s., Note the difference between row number 1 and 2. Number 1 is very close to game time. I need to order closest to game time as possible.

答案1

得分: 3

以下是您提供的SQL查询的中文翻译:

使用myData作为
(
    选择  Player
       ,Vital
       ,Val
       ,Entry_Date
       ,Game_Date
       ,DATEDIFF(DAY, Game_Date, Entry_Date) 'Diff'
    Vitals
)
选择 Player
    ,Vital
    ,Val
    ,Entry_Date
    ,Game_Date
    ,Diff as '天数差'
    ,Row_Number() over (Partition by Player
                        Order by case when Diff <= 0 then -1 else 1 end, abs(Diff)
                       ) as RN
myData;

请注意,我只翻译了SQL查询部分,不包括其他附加内容。

英文:
with myData as 
(
    SELECT  Player
       ,Vital
       ,Val
       ,Entry_Date
       ,Game_Date
       ,DATEDIFF(DAY, Game_Date, Entry_Date) &#39;Diff&#39;
    FROM Vitals
)
select Player
    ,Vital
    ,Val
    ,Entry_Date
    ,Game_Date
    ,Diff as &#39;Days Diff&#39;
    ,Row_Number() over (Partition by Player
                        Order by case when Diff &lt;= 0 then -1 else 1 end, abs(Diff)
                       ) as RN
from myData;

Here is DBfiddle demo

Player Vital Val Entry_Date Game_Date Days Diff RN
George Weight 228 2023-06-01 18:10:05.000 2023-06-01 20:00:00.000 0 1
George Weight 230 2023-06-01 11:42:23.000 2023-06-01 20:00:00.000 0 2
George Weight 228 2023-05-27 08:45:55.000 2023-06-01 20:00:00.000 -5 3
George Weight 227 2023-05-25 12:02:10.000 2023-06-01 20:00:00.000 -7 4
George Weight 229 2023-05-24 10:15:19.000 2023-06-01 20:00:00.000 -8 5
George Weight 227 2023-05-08 14:27:01.000 2023-06-01 20:00:00.000 -24 6
George Weight 228 2023-05-06 11:20:13.000 2023-06-01 20:00:00.000 -26 7
George Weight 226 2023-04-21 16:10:22.000 2023-06-01 20:00:00.000 -41 8
George Weight 230 2023-06-02 16:18:33.000 2023-06-01 20:00:00.000 1 9
George Weight 229 2023-06-05 17:07:27.000 2023-06-01 20:00:00.000 4 10
George Weight 223 2023-06-12 07:30:02.000 2023-06-01 20:00:00.000 11 11

huangapple
  • 本文由 发表于 2023年5月18日 02:01:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/76274983.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定