CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functions
NickoRoboto
NickoRoboto

Reputation: 1

Using ISNULL function with LEAD function: having trouble

I'm having trouble combining the LEAD and ISNULL function. The basic query is like this:

DECLARE @ReportTimeStart DATETIME = '2019-04-25 06:50'
DECLARE @ReportTimeEnd DATETIME = '2019-04-25 23:59';

SELECT
    [tbl_tg_machinestate_ndx],
    [Machine_State],
    [t_stamp],
    LEAD([t_stamp]) OVER (ORDER BY [tbl_tg_machinestate_ndx]) NextStateTime
FROM [db_SPE_Carrig].[dbo].[tbl_TG_MachineState]
WHERE [t_stamp] BETWEEN @ReportTimeStart AND @ReportTimeEnd

This gives me a long list like this:

tbl_tg_machinestate_ndx    Machine_State    t_stamp                    NextStateTime
5893                       130              2019-04-25 23:44:49.160    2019-04-25 23:46:34.670
5894                       170              2019-04-25 23:46:34.670    2019-04-25 23:50:01.370
5895                       100              2019-04-25 23:50:01.370    NULL

I want to use the ISNULL() function to replace that last NULL value with the end time of my report. Like this:

SELECT
    [tbl_tg_machinestate_ndx],
    [Machine_State],
    [t_stamp],
    ISNULL(
    (LEAD([t_stamp]) OVER (ORDER BY [tbl_tg_machinestate_ndx]) NextStateTime),
    @ReportTimeEnd)
  FROM [db_SPE_Carrig].[dbo].[tbl_TG_MachineState]
  WHERE [t_stamp] BETWEEN @ReportTimeStart AND @ReportTimeEnd

But unfortunately this doesn't work. Any help much appreciated.

Upvotes: 0

Views: 850

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272246

LEAD function accepts a "default" value that is returned when it crosses the end of the window. Use it like so:

SELECT
    [tbl_tg_machinestate_ndx],
    [Machine_State],
    [t_stamp],
    LEAD([t_stamp], 1, @ReportTimeEnd) OVER (ORDER BY [tbl_tg_machinestate_ndx]) AS NextStateTime
    -- ----------------^
FROM [db_SPE_Carrig].[dbo].[tbl_TG_MachineState]
WHERE [t_stamp] BETWEEN @ReportTimeStart AND @ReportTimeEnd

This approach is useful if you want to distinguish between the last value and a null value.

Upvotes: 2

Sergey Menshov
Sergey Menshov

Reputation: 3906

Try this:

  SELECT
    [tbl_tg_machinestate_ndx],
    [Machine_State],
    [t_stamp],
    ISNULL(LEAD([t_stamp]) OVER (ORDER BY [tbl_tg_machinestate_ndx]),@ReportTimeEnd) NextStateTime
  FROM [db_SPE_Carrig].[dbo].[tbl_TG_MachineState]
  WHERE [t_stamp] BETWEEN @ReportTimeStart AND @ReportTimeEnd

I think you have a sintax error here:

ISNULL(
    (LEAD([t_stamp]) OVER (ORDER BY [tbl_tg_machinestate_ndx]) NextStateTime),
    @ReportTimeEnd)

Upvotes: 0

Related Questions