December 5, 2023 at 2:36 am
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
(
gamedate nchar(10),
tteamname varchar(50),
rotation int,
bettingline numeric(18,1)
)
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
should be
gamedate teamname rotation bettingline
sept-23 BYU 146 -17.5
sept-23 WYO 147 56.5
instead
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
December 5, 2023 at 3:32 am
This was removed by the editor as SPAM
December 5, 2023 at 9:16 am
Good job providing the table DDL and desired results. All you need to do now is give us the associated INSERT statements which generate the test data to populate the table ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 5, 2023 at 10:59 am
"...gamedate nvarchar..." ? Really?
Why aren't you using the correct data type Date / time / Datetime2 ?
Those come with a couple of advantages:
- validation
- calculation/conversion options
- small space consumption
- order by results
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 5, 2023 at 11:35 am
update #leadtest set [gamedate] = (select Lead([gamedate], 1) OVER( ORDER BY [gamedate] ASC))
LEAD is a windowed function - what makes you think it is going to work in a subquery?
Create a CTE with LeadGameDate and then update the CTE with gamedate = LeadGameDate. The CTE will, of course, need to comply with the rules for an updatable view.
December 5, 2023 at 1:26 pm
If you want the odd numbered 'rotation' column value(d) rows to be updated from the even numbered rows then LAG would seem the better choice. First tho, I agree with Johan Bijnens, storing 'gamedate' as nchar(10) (instead of DATE) will cause nothing but problems. In the provided code the LEAD function's window is ORDER'ed BY 'gamedate' which is incorrect in more than one way: (1) strings don't sort in date order, and (2) to access the value of the previous (odd) row the ORDER BY would be by the 'rotation' column. Also, it only needs to update the odd rotation value rows
drop table if exists #leadtest;
go
create TABLE #leadtest(
gamedate nchar(10),
tteamname varchar(50),
rotation int,
bettingline numeric(18,1));
insert #leadtest(gamedate, tteamname, rotation, bettingline) values
('sept-23', 'BYU', 146, -17.5),
('11:11', 'WYO', 147, 56.5);
with lag_cte as (
select *, lag(gamedate) over (order by rotation) lag_gd
from #leadtest)
update lag_cte
set gamedate=lag_gd
where rotation%2=1;
select * from #leadtest;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 5, 2023 at 4:16 pm
Zososql wrote:update #leadtest set [gamedate] = (select Lead([gamedate], 1) OVER( ORDER BY [gamedate] ASC))
LEAD is a windowed function - what makes you think it is going to work in a subquery?
Create a CTE with LeadGameDate and then update the CTE with gamedate = LeadGameDate. The CTE will, of course, need to comply with the rules for an updatable view.
This is the crux of your problem. You are using a correlated subquery and that subquery contains exactly ONE row. I assume that you tried it without the subquery and got an error about using LEAD() in an update statement. The correct way to resolve that error is with a CTE rather than a correlated subquery.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 5, 2023 at 5:01 pm
The reason why this is an nchar (string) in this example is the data did not come across in the import as a dated format. Some of the data in gamedates were Augt, not Aug , Sep not Sept, so inconsistencies there from the original raw source. The main concern is making sure i can assign every other row the same as the previous row, then i can do a table wide update in making sure gamedate is a workable date object
December 6, 2023 at 12:05 am
Thanks, as this approach works, I had the rotation #s backwards, home teams are always denoted with an even number, so i did the processing with rotation%2 = 0
December 7, 2023 at 12:02 am
A correct validation will be of great help to you.
December 7, 2023 at 8:37 am
A correct validation will be of great help to you.
Please explain why.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 19, 2023 at 9:52 am
This was removed by the editor as SPAM
January 16, 2024 at 9:47 am
This was removed by the editor as SPAM
January 27, 2024 at 12:36 pm
This was removed by the editor as SPAM
January 27, 2024 at 12:37 pm
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply