SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Update records base on dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

henryvuong
Starting Member

USA
8 Posts

Posted - 05/12/2014 :  21:52:31  Show Profile  Reply with Quote
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.

Edited by - henryvuong on 05/12/2014 21:53:13

Ifor
Aged Yak Warrior

619 Posts

Posted - 05/13/2014 :  06:52:40  Show Profile  Reply with Quote

-- *** 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;
Go to Top of Page

henryvuong
Starting Member

USA
8 Posts

Posted - 05/15/2014 :  21:19:38  Show Profile  Reply with Quote
Nice, it works for me. Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000