Post reply

Query Help Needed

  • January 24, 2020 at 4:01 pm

    #3720207

    To all:

    I am trying to create a query that will be able to identify each Customers, Widgets based on the most recent modified date only. I have a history log table that tracks each change, however, I want to create a report that will show the most up-to-date information only.

    Here is my sample query followed by desired results.  And help is sincerely appreaicted and many thanks in advance!

    -- DROP TABLE #t
    CREATE TABLE #t (CustNo int, WidgetID int, LastModDt datetime)
    INSERT INTO #t (CustNo, WidgetID, LastModDt) VALUES (5, 14, '01/05/2018')
    INSERT INTO #t (CustNo, WidgetID, LastModDt) VALUES (5, 14, '01/20/2020')
    INSERT INTO #t (CustNo, WidgetID, LastModDt) VALUES (10, 14, '01/01/2020')
    INSERT INTO #t (CustNo, WidgetID, LastModDt) VALUES (10, 44, '03/11/2014')
    INSERT INTO #t (CustNo, WidgetID, LastModDt) VALUES (10, 44, '04/04/2019')
    INSERT INTO #t (CustNo, WidgetID, LastModDt) VALUES (20, 99, '06/14/2017')
    INSERT INTO #t (CustNo, WidgetID, LastModDt) VALUES (20, 3,  '06/14/2013')
    INSERT INTO #t (CustNo, WidgetID, LastModDt) VALUES (20, 4,  '06/14/2013')
    INSERT INTO #t (CustNo, WidgetID, LastModDt) VALUES (20, 4,  '06/14/2019')
    - SELECT * FROM #t

    -- DESIRE RESULTS:

    -- CustNo WidgetID LastModDt

    -- 5, 14,01/20/2020

    -- 10,14,01/01/2020

    -- 10,44,04/04/2019

    -- 20, 99,06/14/2017

    -- 20,3,06/14/2013

    -- 20,4,06/14/2019

  • January 24, 2020 at 4:18 pm

    #3720217

    Is this a homework question?  What have you already tried?  It seems to me that you need a simple GROUP BY query.

    John

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply