CODEWITHSUNDEEP
sql-serversql-server-2008
Pradeep Kumar
Pradeep Kumar

Reputation: 6979

Delete Rows not between Min and Max dates from another column

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?

Upvotes: 4

Views: 998

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

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

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93734

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)

Upvotes: 1

Related Questions