I have the following query
x
SELECT tb2.event_id, tb2.MENU_HINT, tb2.EVENT_NAME, tb2.Expr1003, tb2.CountWinnerNotPrice55to89Runners0to1 AS columnCount, tb2.SumWinnerNotPrice55to89Runners0to1 AS columSum, lead(tb2.SumWinnerNotPrice55to89Runners0to1,1) OVER( order by tb2.expr1003) as lead1FROM [dbo].[tblData2] tb2WHERE tb2.Expr1003 = '01/01/2018 ';and returns this table:
╔═══════════╦══════════════════════════╦═════════════════╦════════════╦═════════════╦══════════╦═══════╗║ event_id ║ MENU_HINT ║ EVENT_NAME ║ Expr1003 ║ columnCount ║ columSum ║ lead1 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466815 ║ AUS / Long (AUS) 1st Jan ║ R4 1400m Hcap ║ 01/01/2018 ║ 1 ║ 85.00 ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466809 ║ AUS / Long (AUS) 1st Jan ║ R1 1400m Mdn ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466868 ║ AUS / MBdg (AUS) 1st Jan ║ R7 1206m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466189 ║ AUS / Inve (AUS) 1st Jan ║ R7 1400m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466864 ║ AUS / MBdg (AUS) 1st Jan ║ R5 1406m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138386493 ║ AUS / Rand (AUS) 1st Jan ║ R2 1600m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466862 ║ AUS / MBdg (AUS) 1st Jan ║ R4 904m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ 82.53 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138386464 ║ AUS / Flem (AUS) 1st Jan ║ R5 1700m Hcap ║ 01/01/2018 ║ 1 ║ 82.53 ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466813 ║ AUS / Long (AUS) 1st Jan ║ R3 1400m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138386488 ║ AUS / Asct (AUS) 1st Jan ║ R9 1200m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ 57.45 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466522 ║ AUS / Morn (AUS) 1st Jan ║ R4 1200m Hcap ║ 01/01/2018 ║ 1 ║ 57.45 ║ 65.00 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138386468 ║ AUS / Flem (AUS) 1st Jan ║ R7 2800m Listed ║ 01/01/2018 ║ 1 ║ 65.00 ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466181 ║ AUS / Inve (AUS) 1st Jan ║ R3 1010m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466187 ║ AUS / Inve (AUS) 1st Jan ║ R6 1600m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138386486 ║ AUS / Asct (AUS) 1st Jan ║ R8 2400m Grp2 ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138386480 ║ AUS / Asct (AUS) 1st Jan ║ R5 1600m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ 65.00 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138386478 ║ AUS / Asct (AUS) 1st Jan ║ R4 1800m Grp3 ║ 01/01/2018 ║ 1 ║ 65.00 ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138386497 ║ AUS / Rand (AUS) 1st Jan ║ R4 1200m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138386476 ║ AUS / Asct (AUS) 1st Jan ║ R3 2200m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466177 ║ AUS / Inve (AUS) 1st Jan ║ R1 1010m Mdn ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466856 ║ AUS / MBdg (AUS) 1st Jan ║ R1 904m Mdn ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466518 ║ AUS / Morn (AUS) 1st Jan ║ R2 1200m 3yo ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138386470 ║ AUS / Flem (AUS) 1st Jan ║ R8 1400m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138386466 ║ AUS / Flem (AUS) 1st Jan ║ R6 1200m Grp3 ║ 01/01/2018 ║ 0 ║ NULL ║ 66.51 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466191 ║ AUS / Inve (AUS) 1st Jan ║ R8 1400m Cup ║ 01/01/2018 ║ 1 ║ 66.51 ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466798 ║ AUS / Tera (AUS) 1st Jan ║ R5 1125m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ 70.00 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138466821 ║ AUS / Long (AUS) 1st Jan ║ R7 1800m Hcap ║ 01/01/2018 ║ 1 ║ 70.00 ║ 72.44 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════╣║ 138386462 ║ AUS / Flem (AUS) 1st Jan ║ R4 2000m Hcap ║ 01/01/2018 ║ 1 ║ 72.44 ║ 72.42 ║╚═══════════╩══════════════════════════╩═════════════════╩════════════╩═════════════╩══════════╩═══════╝now, what I’m trying to do is to calculate the lead of the first non null value on the field. for example, for event_id = 138466815 the columnCount is 85. I want the “lead1” field to show 82.53, which is the next non null value below. and that then later, for event_id = **138386464 ** “lead 1 to show 57.45 (the next not null value below).
I’m not sure if I should pass a parameter to the offset parameter of the lead function, but I don’t know which one should be, maybe something with Coalesce? I wasn’t able to do it.
just to put it a bit more clear, this is what I expect the lead1 column to show:
╔═══════════╦══════════════════════════╦═════════════════╦════════════╦═════════════╦══════════╦═══════════════╗║ event_id ║ MENU_HINT ║ EVENT_NAME ║ Expr1003 ║ columnCount ║ columSum ║ lead1 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466815 ║ AUS / Long (AUS) 1st Jan ║ R4 1400m Hcap ║ 01/01/2018 ║ 1 ║ 85.00 ║ 82.53 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466809 ║ AUS / Long (AUS) 1st Jan ║ R1 1400m Mdn ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466868 ║ AUS / MBdg (AUS) 1st Jan ║ R7 1206m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466189 ║ AUS / Inve (AUS) 1st Jan ║ R7 1400m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466864 ║ AUS / MBdg (AUS) 1st Jan ║ R5 1406m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138386493 ║ AUS / Rand (AUS) 1st Jan ║ R2 1600m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466862 ║ AUS / MBdg (AUS) 1st Jan ║ R4 904m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138386464 ║ AUS / Flem (AUS) 1st Jan ║ R5 1700m Hcap ║ 01/01/2018 ║ 1 ║ 82.53 ║ 57.45 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466813 ║ AUS / Long (AUS) 1st Jan ║ R3 1400m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138386488 ║ AUS / Asct (AUS) 1st Jan ║ R9 1200m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466522 ║ AUS / Morn (AUS) 1st Jan ║ R4 1200m Hcap ║ 01/01/2018 ║ 1 ║ 57.45 ║ 65.00 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138386468 ║ AUS / Flem (AUS) 1st Jan ║ R7 2800m Listed ║ 01/01/2018 ║ 1 ║ 65.00 ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466181 ║ AUS / Inve (AUS) 1st Jan ║ R3 1010m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466187 ║ AUS / Inve (AUS) 1st Jan ║ R6 1600m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138386486 ║ AUS / Asct (AUS) 1st Jan ║ R8 2400m Grp2 ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138386480 ║ AUS / Asct (AUS) 1st Jan ║ R5 1600m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138386478 ║ AUS / Asct (AUS) 1st Jan ║ R4 1800m Grp3 ║ 01/01/2018 ║ 1 ║ 65.00 ║ 66.51 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138386497 ║ AUS / Rand (AUS) 1st Jan ║ R4 1200m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138386476 ║ AUS / Asct (AUS) 1st Jan ║ R3 2200m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466177 ║ AUS / Inve (AUS) 1st Jan ║ R1 1010m Mdn ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466856 ║ AUS / MBdg (AUS) 1st Jan ║ R1 904m Mdn ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466518 ║ AUS / Morn (AUS) 1st Jan ║ R2 1200m 3yo ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138386470 ║ AUS / Flem (AUS) 1st Jan ║ R8 1400m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138386466 ║ AUS / Flem (AUS) 1st Jan ║ R6 1200m Grp3 ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466191 ║ AUS / Inve (AUS) 1st Jan ║ R8 1400m Cup ║ 01/01/2018 ║ 1 ║ 66.51 ║ 70.00 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466798 ║ AUS / Tera (AUS) 1st Jan ║ R5 1125m Hcap ║ 01/01/2018 ║ 0 ║ NULL ║ NULL ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138466821 ║ AUS / Long (AUS) 1st Jan ║ R7 1800m Hcap ║ 01/01/2018 ║ 1 ║ 70.00 ║ 72.44 ║╠═══════════╬══════════════════════════╬═════════════════╬════════════╬═════════════╬══════════╬═══════════════╣║ 138386462 ║ AUS / Flem (AUS) 1st Jan ║ R4 2000m Hcap ║ 01/01/2018 ║ 1 ║ 72.44 ║ next not null ║╚═══════════╩══════════════════════════╩═════════════════╩════════════╩═════════════╩══════════╩═══════════════╝Also, I’m not sure if there is a way to count the amount of rows between the first not null value in “columnCount” and the second not null value. for example to have 7 as the first count, 3 as the second and 1 as the third.
Advertisement
Answer
You seem to want the next value where columnCount = 1, so partition by that column:
lead(tb2.SumWinnerNotPrice55to89Runners0to1, 1) over (partition by columnCount order by tb2.expr1003 ) as lead1