Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    5

    Unanswered: Update records base on dates

    I have a table like this one, with PurchaseID be the primary key.

    Code:
    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:

    Code:
    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:
    Code:
    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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @t TABLE (
       PurchaseID     INT       NOT NULL
    ,  ItemID         INT       NOT NULL
    ,  Qty            INT       NOT NULL
    ,  DatePurchased  DATE      NOT NULL
    )
    
    INSERT INTO @t (PurchaseID, ItemID
    ,  Qty, DatePurchased) VALUES
       ( 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')
    
    ; WITH cte AS (
    SELECT PurchaseID, ItemID, DatePurchased
    ,  Row_Number() OVER (PARTITION BY ItemID ORDER BY DatePurchased) AS n
       FROM @t
    )
    SELECT
       PurchaseID, ItemID
    ,  CASE WHEN 1 = n THEN 20 ELSE 5 END AS Qty
    ,  DatePurchased
       FROM cte
       ORDER BY PurchaseID
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I guess audits of your inventory will now be useless. Pat did provide you a nice way to botch it up. By the way Pat are there legal disclaimers for the help we provide anyone?
    Dave

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh no, the audits of the inventory will now be critical! With code like this to monkey up the data, they'll have to do a full audit in order to have any data that they can trust!

    I guess that I'd never considered the need for a legal disclaimer when the code does exactly what was requested. If the user wants to run their data through a blender, I'll gleefully help them load it and stand by to watch while they flip the switch!

    Too many times we "read between the lines" and find things that the user never meant or intended. I'm pretty sure that this was a concocted example that has little or no connection to the real world.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2011
    Posts
    5
    I actually needed to update the table not just view it so I changed the select statement into Update statement, like so:

    Code:
    Update cte
    Set Qty = 
    	Case 
    		When n = 1 Then 20 Else 5
    	End
    It works for me. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •