Delete rows not between Min and Max dates from another column

Question

I want to delete records that do not lie between MIN and MAX from another column (zero) in the table.

Here is the sql script to create the schema and sample data:

http://www.sqlfiddle.com/#!3/14686

I tried something like this (which of course does not work):

DELETE FROM MeterReadings
WHERE ScheduledReadingDate NOT BETWEEN MIN(ActualReadingDate) and MAX(ActualReadingDate)
GROUP BY MeterId

How do I achieve this?

Answer 1

You can first select the minimum and maximum dates, and then perform a comparison:

with cte as(select *, 
                   min(ActualReadingDate) over(partition by meterid) as MinDate, 
                   max(ActualReadingDate) over(partition by meterid) as MaxDate               
            from meterreadings)
delete from cte 
where ScheduledReadingDate not between MinDate and MaxDate

Answer 2

try it

;WITH cte
     AS (SELECT Min(ActualReadingDate) m_ActualReadingDate,
                Max(ActualReadingDate) ma_ActualReadingDate,
                MeterId
         FROM   MeterReadings
         GROUP  BY MeterId)
DELETE M
FROM   MeterReadings M
WHERE  EXISTS (SELECT 1
               FROM   cte c
               WHERE  c.MeterId = m.MeterId
                      AND m.ScheduledReadingDate < m_ActualReadingDate
                      AND m.ScheduledReadingDate > ma_ActualReadingDate)