Delete Rows not between Min and Max dates from another column

Question

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?

http://stackoverflow.com/questions/17539145/is-it-possible-to-delete-from-multiple-tables-in-the-same-sql-statement — Sandip Bantawa, Nov 23 '15 at 10:49

score 2 · Accepted Answer · answered Nov 23 '15 at 10:50

2

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

answered Nov 23 '15 at 10:50

Giorgi Nakeuri

35,155
8
47
75

great! exactly what I was looking for. :) – Pradeep Kumar Nov 23 '15 at 10:58

score 1 · Answer 2 · answered Nov 23 '15 at 10:47

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)

doesn't delete anything. I get message: (0 row(s) affected) – Pradeep Kumar Nov 23 '15 at 10:55 — Pradeep Kumar, Nov 23 '15 at 10:55

Delete Rows not between Min and Max dates from another column

2 Answers2