locked
SQL Query to update all records with the appropriate game day number RRS feed

  • Question

  • User-1188570427 posted

    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

    GameDateTime GameDayNbr
    3/29/2020 5:00 PM null
    3/29/2020 12:00 PM null
    4/1/2020 7:00 PM null
    4/2/2020 3:00 PM null

    So the answers would be 

    GameDateTime GameDayNbr
    3/29/2020 5:00 PM 2
    3/29/2020 12:00 PM 1
    4/1/2020 7:00 PM 1
    4/2/2020 3:00 PM 1

    Sunday, April 19, 2020 2:48 AM

Answers

  • User303363814 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 20, 2020 2:27 AM

All replies

  • User303363814 posted

    What are the possible values and how do you choose which value?

    Sunday, April 19, 2020 6:47 AM
  • User-1188570427 posted

    What are the possible values and how do you choose which value?

    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

    Sunday, April 19, 2020 4:09 PM
  • User303363814 posted

    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? 

    Sunday, April 19, 2020 11:18 PM
  • User-1188570427 posted

    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? 

    Sorry, by date/time

    Monday, April 20, 2020 12:09 AM
  • User303363814 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 20, 2020 2:27 AM