I was looking for a solution for the below mentioned scenario. So my table structure is like this ; Table name : energy_readings

So from this , I want to get the number of nulls for the last record of same equipment_id and meter_id. (Should only consider the nulls of the last record of same equipment_id and meter_id)

EX : Here , the last reading for equipment 1 and meter 1 is a null , therefore it should be considered for the count. Also the last reading(Latest Date) for equipment 1 and meter 2 is a null , should be considered for count. But even though equipment 2 and meter 1 has a null , it is not the last record (Latest Date) , therefore should not be considered for the count.

Thus , this should be the result ;

Hope I was clear with the question. Thank you!

CodePudding user response：

You can use CTE like below. CTE LatestRecord will get latest record for equipment_id & meter_id. Later you can join it with your current table and use WHERE to filter out record with null values only.

;WITH LatestRecord AS (
    SELECT equipment_id, meter_id, MAX(reading_date) AS reading_date
    FROM energy_readings
    GROUP BY equipment_id, meter_id
)
SELECT er.meter_id, COUNT(1) AS [Count]
FROM energy_readings er
    JOIN LatestRecord lr
        ON lr.equipment_id = er.equipment_id
            AND lr.meter_id = er.meter_id
            AND lr.reading_date = er.reading_date
WHERE er.readings IS NULL
GROUP BY er.meter_id

CodePudding user response：

with records as(
select equ_id,meter_id,reading_date,readings,
RANK() OVER(PARTITION BY meter_id,equ_id
            order by reading_date) Count

from equipment order by equ_id
)

select equ_id,count(counter)
from
(

select  equ_id,meter_id,reading_date,readings,MAX(Count) as counter
from records                      
 group by meter_id,equ_id 
 order by equ_id
 ) where readings IS NULL group by equ_id

Explanation:-

1. records will order data by reading_date and will give counting as 1,2,3..
2. select max of count from records
3. select count of counter where reading is null

Partition by will give counting as shown in image

Result