I want to delete records which don't lie between MIN and MAX from another (nullable) column in the table.
Here is the sql fiddle to build schema and sample data:
http://www.sqlfiddle.com/#!3/14686
I have been trying something like this (which ofcourse doesn't work):
DELETE FROM MeterReadings
WHERE ScheduledReadingDate NOT BETWEEN MIN(ActualReadingDate) and MAX(ActualReadingDate)
GROUP BY MeterId
How do I achieve this?
You can first select min and max dates and then do the 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
Try this
;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)
