Group results by item showing most recent date and extra info

Transact-SQL
1

I have a query that links 3 tables from our ERP system, showing service records by itemno. I want to see the most recent service date for each item, but also show the name of that most recent service. I can group by itemno, which is fine, but when I add the service name in, I get duplicate itemnos because the service names are different. How can I get around this to achieve the result I need?

Here are the tables and some sample data:

CREATE TABLE JOBHDR (JOBNO VARCHAR(10) NOT NULL PRIMARY KEY, ACTDATE DATE NOT NULL, RECID VARCHAR(32) NOT NULL);

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00IP139508', '12/11/2015', '02384B518B1942BCA3E57986CE65D3CC');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('5000017004', '22/03/2016', '074DAABD432D49D0B5FF5A6DC8AC5FF6');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00HU008275', '13/04/2015', '0E55168A39914D0095E42123C54EE5D8');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('5000016993', '22/03/2016', '18CFDD9FAD3848AC8EAEC296AF18A69C');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00IP138531', '04/11/2015', '29F62B22385E413CA59DB6D7C3117C38');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00ER003569', '19/08/2015', '32C700AAD4C74E88B0F344771CBFC81A');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00IP143798', '16/12/2015', '3DC40B3F33A148E996AF02ECEFC13D82');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00ER005513', '11/02/2016', '42D02EABFBF04C3592FE8E416D4505CF');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00IP116597', '05/05/2015', '447C909999FD49259FD63F489B23BB66');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00HU010433', '30/07/2015', '55D6B333DC82446893A88C5D09F4DFC9');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00IP129458', '17/08/2015', '576139F0C85C4E8E9DB2D4EE7E92B7A8');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00ER003748', '03/09/2015', '6CCC875DF25A4316B73C4911A3B61B9B');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00IP128379', '07/08/2015', '851D7990EFEF4CC0A1EBE8194DBD56F0');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00IP150397', '16/02/2016', '866986BFFE75483F8DDBA92529A1971B');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00IP120858', '09/06/2015', 'A91AAAA71A4244B8B2B664E5825794EA');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00IP120327', '03/06/2015', 'B7AF21C8EF3B4366A3AF5688F8A67FE8');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00IP142810', '08/12/2015', 'BC57AB424AC440EBA220D67C654C5798');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00IP140339', '19/11/2015', 'DCCDC02446244422938E5BC9908B72F4');

INSERT INTO JOBHDR (JOBNO, ACTDATE, RECID)
VALUES ('00ER004546', '06/11/2015', 'EB798164F7D54E1B8846B67889B2B4CF');

CREATE TABLE JOBITEMS (ITEMNO VARCHAR(8) NOT NULL PRIMARY KEY, PARID VARCHAR(32) NOT NULL);

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0011', '02384B518B1942BCA3E57986CE65D3CC');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0007', '074DAABD432D49D0B5FF5A6DC8AC5FF6');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('00GA0003', '0E55168A39914D0095E42123C54EE5D8');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0002', '18CFDD9FAD3848AC8EAEC296AF18A69C');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('00GA0004', '29F62B22385E413CA59DB6D7C3117C38');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0004', '32C700AAD4C74E88B0F344771CBFC81A');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0012', '3DC40B3F33A148E996AF02ECEFC13D82');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('00GA0010', '42D02EABFBF04C3592FE8E416D4505CF');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('00GA0003', '447C909999FD49259FD63F489B23BB66');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0003', '55D6B333DC82446893A88C5D09F4DFC9');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0011', '576139F0C85C4E8E9DB2D4EE7E92B7A8');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0014', '6CCC875DF25A4316B73C4911A3B61B9B');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0006', '851D7990EFEF4CC0A1EBE8194DBD56F0');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('00GA0005', '866986BFFE75483F8DDBA92529A1971B');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0002', 'A91AAAA71A4244B8B2B664E5825794EA');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0006', 'B7AF21C8EF3B4366A3AF5688F8A67FE8');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0014', 'BC57AB424AC440EBA220D67C654C5798');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0013', 'DCCDC02446244422938E5BC9908B72F4');

INSERT INTO JOBITEMS (ITEMNO, PARID)
VALUES ('03EX0008', 'EB798164F7D54E1B8846B67889B2B4CF');

CREATE TABLE JOBHDRPROFILES (RECKEY VARCHAR(10) NOT NULL PRIMARY KEY, JOBTYPE VARCHAR(17) NOT NULL);

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00ER003569', '500 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00ER003748', '250 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00ER004546', '500 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00ER005513', '2000 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00HU008275', '1000 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00HU010433', '500 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00IP116597', '250 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00IP120327', '500 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00IP120858', '500 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00IP128379', '500 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00IP129458', '250 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00IP138531', '500 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00IP139508', '500 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00IP140339', '1000 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00IP142810', '500 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00IP143798', '500 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('00IP150397', '500 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('5000016993', '500 Hour Service');

INSERT INTO JOBHDRPROFILES (RECKEY, JOBTYPE)
VALUES ('5000017004', '500 Hour Service');

This is the output I would like to see:

+----------+-------------------+------------+
| itemno | jobtype | actdate |
+----------+-------------------+------------+
| 00GA0003 | 250 Hour Service | 05/05/2015 |
| 00GA0004 | 500 Hour Service | 04/11/2015 |
| 00GA0005 | 500 Hour Service | 16/02/2016 |
| 00GA0010 | 2000 Hour Service | 11/02/2016 |
| 03EX0002 | 500 Hour Service | 22/03/2016 |
| 03EX0003 | 500 Hour Service | 30/07/2015 |
| 03EX0004 | 500 Hour Service | 19/08/2015 |
| 03EX0006 | 500 Hour Service | 07/08/2015 |
| 03EX0007 | 500 Hour Service | 22/03/2016 |
| 03EX0008 | 500 Hour Service | 06/11/2015 |
| 03EX0011 | 500 Hour Service | 12/11/2015 |
| 03EX0012 | 500 Hour Service | 16/12/2015 |
| 03EX0013 | 1000 Hour Service | 19/11/2015 |
| 03EX0014 | 500 Hour Service | 08/12/2015 |
+----------+-------------------+------------+

And here is my query as it stands now, returning more duplicate itemno as the service names are different:

SELECT ji.itemno, jp.jobtype, MAX(jh.actdate) actdate
FROM jobitems ji
LEFT JOIN JobHdr jh ON jh.recid = ji.parid
LEFT JOIN JobHdrProfiles jp ON jp.RECKEY = jh.jobno
WHERE jp.jobtype LIKE '%SERV%'
GROUP BY itemno, jp.jobtype
ORDER BY itemno`

Many thanks
Martyn

2

Try this:

select itemno
      ,jobtype
      ,actdate
  from (select ji.itemno
              ,jp.jobtype
              ,jh.actdate
              ,row_number() over(partition by ji.itemno order by jh.actdate desc) as rn
          from jobitems as ji
               left outer join jobhdr as jh
                            on jh.recid=ji.parid
               left outer join jobhdrprofiles as jp
                            on jp.reckey=jh.jobno
       ) as a
 where rn=1
;
1 Like
3

Thank you - that worked perfectly!

Martyn