Роман Кириллов Роман Кириллов - 3 years ago 234
SQL Question

How to delete oldest week(period) in MS SQL database?

MS SQL 2008R2 Express, I have database - one table with rows. And when db size became about 10gb I need to clean one oldest week in this table (I dont know the date). Could you help me with script? Thanks.

UPD1.
There is column DateAndTime, so i make query like this in VBA:

strSQL = "SELECT DateAndTime
,TagName
,Val
,SetPoint
,Limit_H
,Limit_L
,Result
FROM dbo.Statistic
WHERE DateAndTime BETWEEN CAST('" & TimeBegin & "' AS datetime) AND CAST( '" & TimeEnd & " ' AS datetime)
Order By DateAndTime desc , (CASE WHEN ISNUMERIC(TagName)=1 THEN CAST(CAST(TagName AS float) AS INT)END ) desc"

Answer Source

As Ankit suggested, use the min() of the date and add a week

delete 
from dbo.statistic
where dateandtime between min(dateandtime) 
                    and dateadd(week, 1, min(dateandtime))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download