英文:
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
('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.
答案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) 'Diff'
FROM Vitals
)
select Player
,Vital
,Val
,Entry_Date
,Game_Date
,Diff as 'Days Diff'
,Row_Number() over (Partition by Player
Order by case when Diff <= 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 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论