I still don't understand what the rule is, you only give samples, not rules.
A rule would be something like 'GameDayNbr is 1 except for the most recent GameDateTime when it is 2. If there are more then one last game then set them all to 2' or
'GameDayNbr is 1 for the first game on a given day and 2 for the subsequent games. If there are two or more games on first in a day then make the game with the smallest Id be 1 and the others 1.5' or
'GameDayNbr is 1 for the first game on a day, two for the second, three for the third, etc. If two games are at the same time then the number should be ???'
'GameDayNbr is 2 for the last game on a day. 1 for the other games. If two games are on at the same time then there set GameDayNbr to -1'
or lots of other possibilities. Here is the SQL for the first of the many possible rules which satisfy the samples you have shown.
UPDATE <tableName>
SET GameDayNbr =
CASE
WHEN GameDateTime = (Select MAX(GameDateTime) from <tableName> THEN 2
ELSE 1
END
Participant
1033 Points
2772 Posts
SQL Query to update all records with the appropriate game day number
Apr 19, 2020 02:48 AM|tvb2727|LINK
Hello,
I'm trying to find if there is a way in SQL Server to update an integer value column to 1 or 2 based on what time a game is by an update query:
Range of values would be 1 - 2 ; you will set the values by the date and time in ascending order
Game Day Date/Time | Game Day Number
So the answers would be
Participant
1320 Points
748 Posts
Re: SQL Query to update all records with the appropriate game day number
Apr 19, 2020 06:47 AM|PaulTheSmith|LINK
What are the possible values and how do you choose which value?
Participant
1033 Points
2772 Posts
Re: SQL Query to update all records with the appropriate game day number
Apr 19, 2020 04:09 PM|tvb2727|LINK
Hey Paul,
Sorry I thought I had that in there. I guess you could of noticed it via my data, but I failed to put that in the text! Thanks for pointing that out!
Range of values would be 1 - 2 ; you will set the values by the time in ascending order
Participant
1320 Points
748 Posts
Re: SQL Query to update all records with the appropriate game day number
Apr 19, 2020 11:18 PM|PaulTheSmith|LINK
OK. Putting the times in order I see
12pm is 1
3pm is 1
5pm is 2
7pm is 1
I can't work out the rule. What would 6pm be? 10am? 4:30om?
Participant
1033 Points
2772 Posts
Re: SQL Query to update all records with the appropriate game day number
Apr 20, 2020 12:09 AM|tvb2727|LINK
Sorry, by date/time
Participant
1320 Points
748 Posts
Re: SQL Query to update all records with the appropriate game day number
Apr 20, 2020 02:27 AM|PaulTheSmith|LINK
I still don't understand what the rule is, you only give samples, not rules.
A rule would be something like 'GameDayNbr is 1 except for the most recent GameDateTime when it is 2. If there are more then one last game then set them all to 2' or
'GameDayNbr is 1 for the first game on a given day and 2 for the subsequent games. If there are two or more games on first in a day then make the game with the smallest Id be 1 and the others 1.5' or
'GameDayNbr is 1 for the first game on a day, two for the second, three for the third, etc. If two games are at the same time then the number should be ???'
'GameDayNbr is 2 for the last game on a day. 1 for the other games. If two games are on at the same time then there set GameDayNbr to -1'
or lots of other possibilities. Here is the SQL for the first of the many possible rules which satisfy the samples you have shown.
UPDATE <tableName> SET GameDayNbr = CASE WHEN GameDateTime = (Select MAX(GameDateTime) from <tableName> THEN 2 ELSE 1 END