locked
update older records with most current recordset RRS feed

  • Question

  • I want to update my history records with the most current capturedate in my table based upon match of Item and Cust. The field dm_rpt_curfcast needs to get updated(older records in table) with the most current record for Item and Customer. The 9/1 record would update the 8/1 and 7/1 records in the example data below. If no match found for the older records compared to current than update all older records with 'N/A' in dm_rpt_curfcast field. The 5/1 record has no match to current so update 575 to N/A.

    DROP TABLE #dm_reporting_history

    CREATE TABLE [dbo].[#dm_reporting_history](

                    [dm_rpt_capturedate] DATETIME NOT NULL,

                    [dm_rpt_item] [VARCHAR](18) NOT NULL,

                    [dm_rpt_customer] [VARCHAR](20) NOT NULL,

        [dm_rpt_curfcast] [VARCHAR](8) NOT NULL)

     

     

    INSERT INTO #dm_reporting_history

    VALUES('09-01-2011','tr56','br14','875')

    GO

    INSERT INTO #dm_reporting_history

    VALUES('08-01-2011','tr56','br14','675')

    GO

    INSERT INTO #dm_reporting_history

    VALUES('07-01-2011','tr56','br14','575')

     

    INSERT INTO #dm_reporting_history

    VALUES('05-01-2011','tr56','br15','575')

     

    SELECT * FROM

    #dm_reporting_history

    Thanks.

    Tuesday, October 11, 2011 2:09 PM

Answers

  • Try

    IF OBJECT_ID('tempdb..#dm_reporting_history','U') IS NOT NULL DROP TABLE #dm_reporting_history
    CREATE TABLE [dbo].[#dm_reporting_history](
                    [dm_rpt_capturedate] datetime NOT NULL,
                    [dm_rpt_item] [VARCHAR](18) NOT NULL,
                    [dm_rpt_customer] [VARCHAR](20) NOT NULL,
        [dm_rpt_curfcast] [VARCHAR](8) NOT NULL)
     
     
    INSERT INTO #dm_reporting_history
    VALUES('09-01-2011','tr56','br14','875')
    GO
    INSERT INTO #dm_reporting_history
    VALUES('08-01-2011','tr56','br14','675')
    GO
    INSERT INTO #dm_reporting_history
    VALUES('07-01-2011','tr56','br14','575')
     
    INSERT INTO #dm_reporting_history
    VALUES('05-01-2011','tr56','br15','575')
     
    SELECT * FROM
    #dm_reporting_history
    
    ;WITH cte AS (SELECT D.[dm_rpt_capturedate], D.dm_rpt_customer, D.dm_rpt_item, D.dm_rpt_curfcast,  
    COUNT(*) OVER (PARTITION BY
    D.dm_rpt_customer, D.dm_rpt_item) AS CntRecs,
    Latest.dm_rpt_curfcast AS Last_curfCast
    FROM #dm_reporting_history D 
    OUTER APPLY (SELECT TOP (1) * FROM #dm_reporting_history D1 WHERE D1.dm_rpt_customer = D.dm_rpt_customer 
    AND D1.dm_rpt_item = D.dm_rpt_item 
    ORDER BY D1.dm_rpt_capturedate DESC) Latest)
    
    UPDATE cte SET dm_rpt_curfcast = CASE WHEN CntRecs = 1 THEN 'N/A' ELSE Last_curfCast END
    
    SELECT * FROM #dm_reporting_history 
    
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by hart60 Tuesday, October 11, 2011 3:19 PM
    Tuesday, October 11, 2011 2:22 PM

All replies

  • Try

    IF OBJECT_ID('tempdb..#dm_reporting_history','U') IS NOT NULL DROP TABLE #dm_reporting_history
    CREATE TABLE [dbo].[#dm_reporting_history](
                    [dm_rpt_capturedate] datetime NOT NULL,
                    [dm_rpt_item] [VARCHAR](18) NOT NULL,
                    [dm_rpt_customer] [VARCHAR](20) NOT NULL,
        [dm_rpt_curfcast] [VARCHAR](8) NOT NULL)
     
     
    INSERT INTO #dm_reporting_history
    VALUES('09-01-2011','tr56','br14','875')
    GO
    INSERT INTO #dm_reporting_history
    VALUES('08-01-2011','tr56','br14','675')
    GO
    INSERT INTO #dm_reporting_history
    VALUES('07-01-2011','tr56','br14','575')
     
    INSERT INTO #dm_reporting_history
    VALUES('05-01-2011','tr56','br15','575')
     
    SELECT * FROM
    #dm_reporting_history
    
    ;WITH cte AS (SELECT D.[dm_rpt_capturedate], D.dm_rpt_customer, D.dm_rpt_item, D.dm_rpt_curfcast,  
    COUNT(*) OVER (PARTITION BY
    D.dm_rpt_customer, D.dm_rpt_item) AS CntRecs,
    Latest.dm_rpt_curfcast AS Last_curfCast
    FROM #dm_reporting_history D 
    OUTER APPLY (SELECT TOP (1) * FROM #dm_reporting_history D1 WHERE D1.dm_rpt_customer = D.dm_rpt_customer 
    AND D1.dm_rpt_item = D.dm_rpt_item 
    ORDER BY D1.dm_rpt_capturedate DESC) Latest)
    
    UPDATE cte SET dm_rpt_curfcast = CASE WHEN CntRecs = 1 THEN 'N/A' ELSE Last_curfCast END
    
    SELECT * FROM #dm_reporting_history 
    
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by hart60 Tuesday, October 11, 2011 3:19 PM
    Tuesday, October 11, 2011 2:22 PM
  • <Disregard>

    Well, you cannot set a datetime column to 'N/A', and we cannot use NULL mark either because the column is not nullable. May be using an anchor date like '19000101'.

    Try:

    with rs as (
    select dm_rpt_item, dm_rpt_customer, max(dm_rpt_capturedate) as max_capturedate
    from #dm_reporting_history
    group by dm_rpt_item, dm_rpt_customer
    having min(dm_rpt_capturedate) <> max(dm_rpt_capturedate)
    )
    update A
    set A.dm_rpt_capturedate = coalesce(B.max_capturedate, '19000101')
    from #dm_reporting_history as A left outer join rs as B
    on A.dm_rpt_item = B.dm_rpt_item and A.dm_rpt_customer = B.dm_rpt_customer;

    </Disregard>

    -------------------------

    After re-reading the problem, here is my suggestion.

    with rs as (
    SELECT
    	dm_rpt_item, 
    	dm_rpt_customer,
    	dm_rpt_curfcast,
    	ROW_NUMBER() OVER(PARTITION BY dm_rpt_item, dm_rpt_customer ORDER BY dm_rpt_capturedate DESC) AS rn,
    	MIN(dm_rpt_capturedate) OVER(PARTITION BY dm_rpt_item, dm_rpt_customer) AS min_capturedate,
    	MAX(dm_rpt_capturedate) OVER(PARTITION BY dm_rpt_item, dm_rpt_customer) AS max_capturedate
    from #dm_reporting_history
    )
    UPDATE
    	A
    SET
    	A.dm_rpt_curfcast = coalesce(B.dm_rpt_curfcast, 'N/A')
    FROM
    	#dm_reporting_history as A 
    	left outer join 
    	rs as B
    	on A.dm_rpt_item = B.dm_rpt_item 
    	and A.dm_rpt_customer = B.dm_rpt_customer
    	AND B.rn = 1
    	AND B.min_capturedate <> B.max_capturedate;
    GO


     


    AMB

    Some guidelines for posting questions...

    Tuesday, October 11, 2011 2:28 PM
  • Alejandro,

    Please re-read the problem's definition. He wanted to update the varchar field, not the date field.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, October 11, 2011 2:31 PM
  • Tuesday, October 11, 2011 2:40 PM
  • HI hart !

    You may also use the below query to get your desired output;

    ;WITH CTE AS 
    (
    SELECT dm_rpt_item , dm_rpt_customer ,dm_rpt_capturedate,dm_rpt_curfcast 
     , ROW_NUMBER() OVER(PARTITION BY dm_rpt_item , dm_rpt_customer ORDER BY dm_rpt_capturedate DESC) Rn , COUNT(*) OVER(PARTITION BY dm_rpt_item , dm_rpt_customer) AS CountNo
    FROM #dm_reporting_history
    )
    UPDATE T SET T.dm_rpt_curfcast = 
     (CASE WHEN C.CountNo = 1 THEN 'N/A' ELSE C.dm_rpt_curfcast END)
    FROM CTE C
    INNER JOIN #dm_reporting_history T ON T.dm_rpt_item = C.dm_rpt_item AND T.dm_rpt_customer = C.dm_rpt_customer
    WHERE C.Rn = 1 AND T.dm_rpt_item = C.dm_rpt_item AND T.dm_rpt_customer = C.dm_rpt_customer
    

     

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks, Hasham

    Tuesday, October 11, 2011 3:17 PM
    Answerer
  • many thanks to ALL replies... Naomi your solution worked great !!! Thanks...
    Tuesday, October 11, 2011 3:20 PM
  • Have you vtried the one i have provided.
    Tuesday, October 11, 2011 3:21 PM
    Answerer