Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Update records base on dates

Author  Topic 

henryvuong
Starting Member

8 Posts

Posted - 2014-05-12 : 21:52:31
I have a table like this one, with PurchaseID be the primary key.



PurchaseID ItemID Qty DatePurchased
1 105 15 2010-01-12
2 107 4 2012-11-30
3 105 7 2011-03-09
4 109 25 2011-10-17
5 108 17 2012-04-26
6 104 30 2010-05-25
7 105 18 2014-02-08
8 109 12 2014-04-21
9 105 3 2013-06-05
10 109 9 2013-09-22


I want to change the quantity of each record as follow:

  • If the ItemID appears only once in the table, set the quantity to 20

  • If the ItemID appears more than once, set the quantity of one with newest purchased date to 20, others to 5



For example:

  • ItemID 104 only appears once so the quantity is 20

  • ItemID 105 appear more than once, so the quantity of the record with newest purchased date (2014-02-08, PurchaseID=7) is set to 20, all other records are set to 5




The updated table should look like this:


PurchaseID ItemID Qty DatePurchased
1 105 15 2010-01-12
2 107 20 2012-11-30
3 105 5 2011-03-09
4 109 5 2011-10-17
5 108 20 2012-04-26
6 104 20 2010-05-25
7 105 20 2014-02-08
8 109 20 2014-04-21
9 105 5 2013-06-05
10 109 5 2013-09-22

(In real life, each item would be updated to different quantity numbers, but for simplicity, I only set them at 5 and 20 here.) I would think of something like this:

Update Purchases
Set Quantity =
CASE
WHEN DatePurchased = (max DatePurchased of the same ItemID)
THEN 20
ELSE 5
END


But I can't figure out how to get the max DatePurchased of the same ItemID.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-05-13 : 06:52:40
[code]
-- *** Test Data ***
CREATE TABLE #t
(
PurchaseID int NOT NULL
,ItemID int NOT NULL
,Qty int NOt NULL
,DatePurchased date NOT NULL
);
INSERT INTO #t
VALUES (1, 105, 15, '20100112')
,(2, 107, 4, '20121130')
,(3, 105, 7, '20110309')
,(4, 109, 25, '20111017')
,(5, 108, 17, '20120426')
,(6, 104, 30, '20100525')
,(7, 105, 18, '20140208')
,(8, 109, 12, '20140421')
,(9, 105, 3, '20130605')
,(10, 109, 9, '20130922');
-- *** End Test Data ***

WITH OrderDesc
AS
(
SELECT PurchaseID, Qty
,ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY DatePurchased DESC) AS rn
FROM #t
)
UPDATE OrderDesc
SET Qty = CASE WHEN rn = 1 THEN 20 ELSE 5 END;

select * from #t order by PurchaseID;
[/code]
Go to Top of Page

henryvuong
Starting Member

8 Posts

Posted - 2014-05-15 : 21:19:38
Nice, it works for me. Thanks
Go to Top of Page
   

- Advertisement -