sql - TSQL: how to delete last record from query result if record is NULL -

background: using stored procedure below retrieve hourly time series data.

select *    (       select cast(localdatetime smalldatetime) [datetime], datavalue, variableid          datavalues          siteid = 2 , variableid in(1,3,30)          ) tabledate    pivot (sum(datavalue) variableid in ([1],[3],[30]))    pivottable order [datetime] 

usually data written database @ top of hour, , stored procedure executed @ 5 minutes past hour retrieve existing values.

the problem data retrieval on top of hour fails 1 of many sites , no new data written datavalues table site. however, recent hour still present in datavalues table because other sites wrote values it. results in query result has record recent hour fields null. code handles query results can deal hourly data null whatever reason except in cases last record null fields.

question: there simple way remove last record of query result (once pivoted per procedure above) if fields of record null?

you can using row_number():

with cte (       select *       (select cast(localdatetime smalldatetime) [datetime], datavalue, variableid             datavalues             siteid = 2 , variableid in(1,3,30)            ) tabledate       pivot (sum(datavalue) variableid in ([1],[3],[30])) pivottable       order [datetime]      ) select [datetime], [1], [3], [30] (select cte.*, row_number() on (order [datetime] desc) seqnum       cte      ) t seqnum = 1 , [1] null , [3] null , [30] null; 

however, think easiest way filter before pivot:

      select *       (select cast(localdatetime smalldatetime) [datetime], datavalue, variableid             datavalues             datavalue not null             siteid = 2 , variableid in (1, 3, 30)            ) tabledate       pivot (sum(datavalue) variableid in ([1],[3],[30])) pivottable       order [datetime] 

this has potential disadvantage of removing other rows nulls in them. if problem, first method works better.