CODEWITHSUNDEEP
sql-server
HaPhan
HaPhan

Reputation: 321

Cleaning up old record to a specific date: How to select the old record?

I posted a question here, which I now need to perform. I edited it a few times to match the current requirement, and now I think i will make it clearer as a final solution for me as well.

My table:

Items   | Price  |  UpdateAt
  1     |  2000  | 02/02/2015
  2     |  4000  | 06/04/2015
  1     |  2500  | 05/25/2015
  3     |  2150  | 07/05/2015
  4     |  1800  | 07/05/2015
  5     |  5540  | 08/16/2015
  4     |  1700  | 12/24/2015
  5     |  5200  | 12/26/2015
  2     |  3900  | 01/01/2016
  4     |  2000  | 06/14/2016

As you can see, this is a table that keeps items' price as well as their old price before the last update.

Now I need to find the rows which :

Why those conditions? Because I need to perform a cleanup on that table off of those records that older than 1 year, while still maintain the full item list.

So with those conditions, the result from the above table should be :

Items   | Price  |  UpdateAt
  1     |  2000  | 02/02/2015
  2     |  4000  | 06/04/2015
  4     |  1800  | 07/05/2015

The update at 02/02/2015 of item 1 should be selected, while the update no. 2 at 05/25/2015, though still over 1 year old, should not because it is the most up-to-date price for item 1. Item 3 isn't in the list because it never been updated, hence its price remain the same until now so i don't need to clean it up.

At first i think it wouldn't be so hard, and i think I've already had an answer but as I proceed, it isn't something that easy anymore.

@Tim Biegeleisen provided me with an answer in the last question, but it doesn't select the items which price doesn't change over the year at all, which i'm having to deal with now.

I need a solution to effectively clean up the table - it isn't necessary to follow 3 conditions above if it can produce the same result as I need : Records that needs to be deleted.

Upvotes: 2

Views: 87

Answers (2)

Jatin Patel
Jatin Patel

Reputation: 2104

try this,

DECLARE @Prices TABLE(Items   INT, Price  DECIMAL(10,2),  UpdateAt DATETIME)

INSERT INTO @Prices
VALUES
       (1, 2000, '02/02/2015')
      ,(2, 4000, '06/04/2015')
      ,(1, 2500, '05/25/2015')
      ,(3, 2150, '07/05/2015')
      ,(4, 1800, '07/05/2015')
      ,(5, 5540, '08/16/2015')
      ,(4, 1700, '12/24/2015')
      ,(5, 5200, '12/26/2015')
      ,(2, 3900, '01/01/2016')
      ,(4, 2000, '06/14/2016')


SELECT p.Items, p.Price, p.UpdateAt
FROM @Prices p
LEFT JOIN ( SELECT 
                p1.Items,
                p1.UpdateAt,
                ROW_NUMBER() OVER (PARTITION BY p1.Items ORDER BY p1.UpdateAt DESC) AS RowNo
            FROM @Prices p1     
            ) AS hp ON hp.Items = p.Items
                AND hp.UpdateAt = p.UpdateAt
WHERE hp.RowNo > 1  -- spare one price for each item at any date
    AND p.UpdateAt < DATEADD(YEAR, -1, GETDATE())   -- remove only prices older than a year

the result is:

Items       Price                                   UpdateAt                
----------- --------------------------------------- ----------------------- 
1           2000.00                                 2015-02-02 00:00:00.000 
2           4000.00                                 2015-06-04 00:00:00.000 
4           1800.00                                 2015-07-05 00:00:00.000

Upvotes: 2

rverdelli
rverdelli

Reputation: 48

This query will return the dataset you're looking for:

SELECT t1.Items, t1.Price, t1.UpdateAt
FROM
(
    SELECT 
        t2.Items, 
        t2.Price, 
        t2.UpdateAt, 
        ROW_NUMBER() OVER (PARTITION BY t2.Items ORDER BY t2.UpdateAt DESC) AS rn
    FROM [Table] AS t2
) AS t1
WHERE t1.rn > 1
AND t1.UpdateAt < DATEADD(year, -1, GETDATE())

Upvotes: 1

Related Questions