Update records base on dates - SQL Server Forums

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]

henryvuong
Starting Member

8 Posts

Posted - 2014-05-15 : 21:19:38

Nice, it works for me. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources