Lead Updates Not Working, Only Rendering Nulls

  • 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

  • This was removed by the editor as SPAM

  • 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 ...

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • "...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

  • 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.

  • 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;

    • This reply was modified 3 months ago by  Steve Collins. Reason: Even/odd were reversed in the first sentence

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Ken McKelvey wrote:

    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

  • 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

  • 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

  • A correct validation will be of great help to you.

  • Kevbrock12 wrote:

    A correct validation will be of great help to you.

    Please explain why.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • 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