January 24, 2020 at 4:01 pm
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
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