I hav a table that holds sports betting lines that i am using in a pseudo SSIS environment, and i'm trying to update one of two kinds of rows in the data of the table.
create TABLE #leadtest
I have parsed some data from a online source of betting lines, and the data reads one team for each row; the visitor team sits in one row above the home team (rotation numbers are perfectly segregated to be an odd number for the visitor team, an even number for the home team). In the gamedate columns , the row that holds the visiting teams' data has the date of the game (ex: Sept-23) , while the gamedate row that corresponds with the home team shows the time of day the game begins (ex: 1:00pm). I need to use lead or lag function that can be a segment of an update operation that would put the gamedate info from the hometeam into the gamedate row for the visiting team, as i want both visitor and home team to have the same Sept-23 date info there.
update #leadtest set [gamedate] = (select
Lead([gamedate], 1) OVER(
ORDER BY [gamedate] ASC))
..renders all null values for every gamedate row , not the updated gamedate for the home team row
gamedate teamname rotation bettingline
sept-23 BYU 146 -17.5
sept-23 WYO 147 56.5
NULL BYU 146 -17.5
NULL WYO 147 56.5
i can't think of another real good way to do this at the moment, what am I missing with LEAD of LAG function?
Thanks in advance for any help