August 13, 2024 at 1:24 am
I have a table with odds from sportsbook, and I need to populate a gamedate value that is null . The gamedate in question coincides with the even numbered rotation rows of the data, below are samples of what i'm working with
Create [dbo].gameresults (
gamedate date null,
team varchar(50) null,
rotation int null,
odds float null
)
insert into gameresults(gamedate,team,rotation,odds) values ('2023-10-1','NYJ',101,null)
insert... values (null,'DET',102,-7)
insert... values ('2023-10-1','LAR',103,-1)
insert... values (null,'PGH',104,null)
insert... values ('2023-10-2',WAS',205,null)
insert...values(null,'PHI',206,-6.5)
using the lag function, i'm using this update script to try to put a value in the gamedate of the ones that are null:
update [dbo].[gameresults] set [gamedate] = (select LAG([gamedate],1) OVER (ORDER BY [rotation]) where rotation%2 = 1) where [gamedate] is null
each of the gamedates that are null are guaranteed to have a rotation number that is EVEN
when running the query, the system says the rows have been updated, but checking the data, they are still null. The order by I am assigning rotation
Any help greatly appreciated!
August 13, 2024 at 7:04 am
If you convert your UPDATE to a SELECT, you will see that you cannot filter the way that you are trying. The LAG works within your filtered results.
SELECT g.gamedate
, g.team
, g.rotation
, g.odds
, lagDate = (select LAG(g.gamedate,1) OVER (ORDER BY g.rotation) where rotation%2 = 1)
FROM dbo.gameresults AS g
where g.gamedate is NULL;
To achieve what you are attempting, you need to create a CTE with the calculate column, and update the CTE.
WITH cteData AS (
SELECT g.gamedate
, lagDate = LAG( g.gamedate, 1 ) OVER ( ORDER BY g.rotation )
FROM dbo.gameresults AS g
)
UPDATE cteData
SET gamedate = lagDate
WHERE gamedate IS NULL;
August 13, 2024 at 7:04 am
duplicate post deleted
August 15, 2024 at 3:11 pm
Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply