Help with Group By logic to return correct record

wembleybear · June 10, 2020, 12:05pm

I have a table CONTITEMS which shows the contracts that a unique item has been on. Each unique item can only be active on 1 contract at any given time. In this case, the STATUS field will be either 1, 6 or 8. Sometimes the item is not on an active contract and in this case, the STATUS will be either 2 or 5.

What I need to retrieve is the record for each unique item once only in the following way:

If the record status is 1,6 or 8 use that record, if not
select the record with the most recent DOCDATE#5 (which is the end date)

Here is an example output for 1 unique item in the CONTITEMS table:

+--------+------------+-------------------------+-------------------------+--------+----------+
| ITEMNO |   CONTNO   |        HIREDATE         |        DOCDATE#5        | STATUS | RECORDER |
+--------+------------+-------------------------+-------------------------+--------+----------+
|   8021 | 00WA000587 | 2013-11-07 00:00:00.000 | 1899-12-30 00:00:00.000 |      1 |   581496 |
|   8021 | 001-016135 | 2013-07-29 00:00:00.000 | 2013-08-02 00:00:00.000 |      2 |   624239 |
|   8021 | 001-016735 | 2013-08-21 00:00:00.000 | 2013-11-06 00:00:00.000 |      2 |   626010 |
+--------+------------+-------------------------+-------------------------+--------+----------+

In this example I need to return the first row as the status is 1. However, if all the statuses were 2, then I would need to return the one with the most recent DOCDATE#5.

Here is my current SQL statement which at present returns only the record with the highest RECORDER which is not necessarily the active one or most recent:

SELECT ct.ITEMNO
	,ct.CONTNO
	,ct.HIREDATE
	,ct.DOCDATE#5
	,ct.STATUS
	,ct.RECORDER
FROM (
	SELECT ci.ITEMNO
		,MAX(ci.RECORDER) RECORDER
	FROM CONTITEMS ci
	WHERE ci.ITEMNO = '8021'
	GROUP BY ci.ITEMNO
	) c
INNER JOIN CONTITEMS ct ON ct.RECORDER = c.RECORDER

Here is a create table and insert script:

CREATE TABLE CONTITEMS (
	ITEMNO VARCHAR(20)
	,CONTNO VARCHAR(10)
	,HIREDATE DATETIME
	,DOCDATE#5 DATETIME
	,STATUS INT
	,RECORDER INT
	)

INSERT INTO CONTITEMS (ITEMNO, CONTNO, HIREDATE, DOCDATE#5,STATUS, RECORDER) VALUES ('8010','001-016580','2013-08-01','2013-12-03',2,582035)

INSERT INTO CONTITEMS (ITEMNO, CONTNO, HIREDATE, DOCDATE#5,STATUS, RECORDER) VALUES ('8010','001-012882','2013-03-08','2013-07-31',2,614380)

INSERT INTO CONTITEMS (ITEMNO, CONTNO, HIREDATE, DOCDATE#5,STATUS, RECORDER) VALUES ('8021','00WA000587','2013-11-07','1899-12-30',1,581496)

INSERT INTO CONTITEMS (ITEMNO, CONTNO, HIREDATE, DOCDATE#5,STATUS, RECORDER) VALUES ('8021','001-016135','2013-07-29','2013-08-02',2,624239)

INSERT INTO CONTITEMS (ITEMNO, CONTNO, HIREDATE, DOCDATE#5,STATUS, RECORDER) VALUES ('8021','001-016735','2013-08-21','2013-11-06',2,624239)

For this data, 2 records should be returned as follows:

+--------+------------+-------------------------+-------------------------+--------+----------+
| ITEMNO |   CONTNO   |        HIREDATE         |        DOCDATE#5        | STATUS | RECORDER |
+--------+------------+-------------------------+-------------------------+--------+----------+
|   8010 | 001-016580 | 2013-08-01 00:00:00.000 | 2013-12-03 00:00:00.000 |      2 |   582035 |
|   8021 | 00WA000587 | 2013-11-07 00:00:00.000 | 1899-12-30 00:00:00.000 |      1 |   581496 |
+--------+------------+-------------------------+-------------------------+--------+----------+

8010 - all records at status 2 so return the most recent
8021 - has a record at status 1 so return that

Any help would be appreciated.

Martyn

harishgg1 · June 10, 2020, 12:32pm

hi martyn

this is something real quick .. without any fancy shortcuts

;with cte as 
(
select *,ROW_NUMBER() over(partition by itemno order by DOCDATE#5 desc ) as rn from CONTITEMS
where ITEMNO in 
(
select itemno from CONTITEMS where status = 2
except
select ITEMNO from CONTITEMS where status = 1
)
) 
select ITEMNO, CONTNO, HIREDATE, DOCDATE#5,STATUS, RECORDER from cte where rn = 1
union all 
select ITEMNO, CONTNO, HIREDATE, DOCDATE#5,STATUS, RECORDER from CONTITEMS where status = 1

wembleybear · June 10, 2020, 12:56pm

Hi Harish

This does indeed do the trick, even when I add in the statuses not shown in my example data.

Many thanks for your help,

Stay safe

Martyn

harishgg1 · June 10, 2020, 1:04pm

hi martyn

if you have other statuses ..

please change this statement
select itemno from CONTITEMS where status = 2
except
select ITEMNO from CONTITEMS where status = 1

to
select itemno from CONTITEMS where status <> 1
except
select ITEMNO from CONTITEMS where status = 1

wembleybear · June 10, 2020, 1:12pm

Hi Harish

Thanks - this whole section of code was originally a sub-query as part of a larger query. How can I join the cte up to this in it's place?

harishgg1 · June 10, 2020, 1:17pm

hi

please see if this link helps

wembleybear · June 10, 2020, 1:25pm

Hi Harish

This was part of an update statement in an SSIS step, the full code of which I've posted below, the section is close to the bottom, now I need to fit it back together if that makes sense. I'll have a look at the link you sent - thanks.

UPDATE fct
SET fct.UtilStatus = cp.UTILSTATUS,
fct.ContStatus = cp.CONTSTATUS,
fct.ItemStatus = cp.ITEMSTATUS,
fct.[Contract] = cp.CONTNO
FROM [LON1-ONE-APP05].[AHS_DW].[DBO].[FactUtilisation_ETL] fct
INNER JOIN
(SELECT cg.ITEMNO
 ,cg.CONTNO
 ,cg.ITEMSTATUS
 ,cg.CONTSTATUS
 ,CASE 
  WHEN cg.ItemStatus = 0
   AND cg.ContStatus IN (
    2
    ,5
    ,88
    )
   THEN 0
  WHEN cg.ItemStatus = 0
   AND cg.ContStatus = 0
   AND cg.HIREDATE != CONVERT(DATE, GETDATE())
   THEN 1
  WHEN cg.ItemStatus = 0
   AND cg.ContStatus = 0
   AND cg.HIREDATE = CONVERT(DATE, GETDATE())
   THEN 2
  WHEN cg.ItemStatus = 1
   AND cg.ContStatus = 1
   THEN 2
  WHEN cg.ItemStatus = 1
   AND cg.ContStatus = 6
   AND cg.DAYSSINCEEND < 0
   THEN 2
  WHEN cg.ItemStatus = 1
   AND cg.ContStatus = 6
   AND cg.DAYSSINCEEND >= 0
   AND cg.Collected = 1
   THEN 9
  WHEN cg.ItemStatus = 1
   AND cg.ContStatus = 6
   AND cg.DAYSSINCEEND >= 0
   AND cg.DAYSSINCEEND <= 3
   AND cg.Collected = 0
   THEN 8
  WHEN cg.ItemStatus = 1
   AND cg.ContStatus = 6
   AND cg.DAYSSINCEEND > 3
   AND cg.Collected = 0
   THEN 3
  WHEN cg.ItemStatus = 2
   AND cg.ContStatus IN (
    2
    ,5
    ,8
    )
   THEN 4
  WHEN cg.ItemStatus = 3
   AND cg.ContStatus IN (
    2
    ,5
    ,88
    )
   THEN 5
  WHEN cg.ItemStatus = 3
   AND cg.ContStatus = 8
   THEN 9
  WHEN cg.ItemStatus = 9
   AND cg.ContStatus IN (
    0
    ,1
    ,2
    ,5
    ,88
    )
   THEN 6
  WHEN cg.ItemStatus = 10
   AND cg.ContStatus IN (
    0
    ,2
    ,5
    ,8
    ,88
    )
   THEN 7
  ELSE 99
  END  [UTILSTATUS]
FROM (
 SELECT sk.ITEMNO
  ,cl.CONTNO
  ,isnull(cl.[STATUS], 88) CONTSTATUS
  ,sk.[STATUS] ITEMSTATUS
  ,cl.HIREDATE
  ,cl.DOCDATE#5
  ,cl.EDATE
  ,CASE 
   WHEN cl.EDate >= CONVERT(DATE, GETDATE())
    THEN - 1
   ELSE ISNULL((
      SELECT SUM(CASE 
         WHEN dd.CalWork = 1
          THEN 1
         ELSE 0
         END)
      FROM [LON1-ONE-APP05].[AHS_DW].dbo.DimDate dd WITH (NOLOCK)
      WHERE dd.CalCode = 'BH'
       AND dd.CalDate > cl.EDate
       AND dd.CalDate <= CONVERT(DATE, GETDATE())
      ), 0)
   END [DAYSSINCEEND]
  ,CASE 
   WHEN cd.ENDTIME IS NOT NULL
    THEN 1
   ELSE 0
   END [COLLECTED]
 FROM [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].[STOCK] sk
 LEFT JOIN [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].[STOCKPROFILES] sp ON sp.RECKEY = sk.GRPCODE
 LEFT JOIN (SELECT st.ITEMNO, ct.CONTNO, Isnull(ct.STATUS,88) STATUS, ct.HIREDATE, ct.DOCDATE#5, ct.EDATE

FROM [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].[STOCK] st
LEFT JOIN
(SELECT sb.ITEMNO
 ,CASE 
  WHEN ct.CONTNO IS NULL
   THEN st.CONTNO
  ELSE ct.CONTNO
  END CONTNO
 ,CASE 
  WHEN ct.CONTNO IS NULL
   THEN st.[STATUS]
  ELSE ct.[STATUS]
  END [STATUS]
 ,CASE 
  WHEN ct.CONTNO IS NULL
   THEN st.HIREDATE
  ELSE ct.HIREDATE
  END [HIREDATE]
 ,CASE 
  WHEN ct.CONTNO IS NULL
   THEN st.DOCDATE#5
  ELSE ct.DOCDATE#5
  END DOCDATE#5
 ,CASE 
  WHEN ct.CONTNO IS NULL
   THEN st.OHREC
  ELSE ct.OHREC
  END OHREC
 ,CASE 
  WHEN ct.CONTNO IS NULL
   THEN st.RECORDER
  ELSE ct.RECORDER
  END RECORDER
 ,CASE 
  WHEN ct.CONTNO IS NULL
   THEN st.EDATE
  ELSE ct.EDATE
  END EDATE
FROM [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].[STOCK] sb
LEFT JOIN (
 SELECT ci.ITEMNO
  ,ci.CONTNO
  ,ci.[STATUS]
  ,ci.HIREDATE
  ,ci.DOCDATE#5
  ,ci.OHREC
  ,ci.RECORDER 
  ,oh.EDATE
 FROM [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].[CONTITEMS] ci
 LEFT JOIN [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].[OHInfo] oh ON oh.RECID = ci.OHREC
 WHERE ci.STATUS = 1
 ) ct ON ct.ITEMNO = sb.ITEMNO
LEFT JOIN (
 SELECT f.ITEMNO
  ,f.CONTNO
  ,f.STATUS
  ,f.HIREDATE
  ,f.DOCDATE#5
  ,f.OHREC
  ,f.RECORDER
  ,oh.EDATE
 FROM (
  SELECT ITEMNO
   ,CASE WHEN ITEMNO = '8007' AND MAX(RECORDER) = 614383 THEN 582036
    WHEN ITEMNO = 'B158' AND MAX(RECORDER) = 600079 THEN 580159
 WHEN ITEMNO = 'B210' AND MAX(RECORDER) = 619378  THEN 582691
 WHEN ITEMNO = 'SL160' AND MAX(RECORDER) = 694124  THEN 580757
 WHEN ITEMNO = '01BK0039' AND MAX(RECORDER) = 693603 THEN 581043
 WHEN ITEMNO = 'SW88' AND MAX(RECORDER) = 599537  THEN 578945
 WHEN ITEMNO = '8010' AND MAX(RECORDER) = 614380 THEN 582035
 WHEN ITEMNO = '8021' AND MAX(RECORDER) = 626010 THEN 581496
 WHEN ITEMNO = '8021' AND MAX(RECORDER) = 624239 THEN 581496
    ELSE MAX(RECORDER) END RECORDER
  FROM [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].[CONTITEMS]
  GROUP BY ITEMNO
  ) AS x
 INNER JOIN [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].[CONTITEMS] f ON f.ITEMNO = x.ITEMNO
  AND f.RECORDER = x.RECORDER
 LEFT JOIN [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].[OHInfo] oh ON oh.RECID = f.OHREC
 ) st ON st.ITEMNO = sb.ITEMNO


WHERE sb.[UNIQUE] = 1) ct on ct.ITEMNO = st.ITEMNO)  cl ON cl.ITEMNO = sk.ITEMNO
 LEFT JOIN [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[REPORTS].[COLLECTED] cd ON cd.ITEMNO = sk.ITEMNO
 WHERE sk.[UNIQUE] = 1
  AND sk.ACTIVE = 1
  AND sk.[TYPE] = 0
  AND sp.UTFLAG = 1
  AND sk.PATTEST <> 1
 ) cg) cp on cp.ITEMNO = fct.StockAsset

harishgg1 · June 10, 2020, 1:56pm

hi

please see if this helps .. its skeleton..to give idea

update 
set 
from ( select statement ) a
join 
(
    ;with cte as 
     (select *
      )
     select from cte 
)  b
on a.   =    b.

wembleybear · June 10, 2020, 2:59pm

Hi Harish

I've tried the following but it does not work, so I'm obviously missing something:

UPDATE fce

SET fce.CONTRACT = ct.CONTNO

FROM FactUtilisation_ETL fce

INNER JOIN

(;with cte as 
(
select *,ROW_NUMBER() over(partition by itemno order by DOCDATE#5 desc ) as rn from [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].CONTITEMS
where ITEMNO in 
(
select itemno from [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].CONTITEMS where status IN (2,5)
except
select ITEMNO from [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].CONTITEMS where status IN (1,6,8)
)
) 
select ITEMNO, CONTNO, HIREDATE, DOCDATE#5,STATUS, RECORDER from cte where rn = 1
union all 
select ITEMNO, CONTNO, HIREDATE, DOCDATE#5, STATUS, RECORDER from [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].CONTITEMS WHERE STATUS IN (1,6,8) AND ITEMNO <> '') ct

ON fce.StockAsset = ct.ITEMNO

It complains about the ( before the ;With and the final ) as well.

I've tried a few variations but can't get it to run.

harishgg1 · June 10, 2020, 3:09pm

hi

please try this ..

this is the way for using CTE

;with cte as 
(
select *,ROW_NUMBER() over(partition by itemno order by DOCDATE#5 desc ) as rn from [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].CONTITEMS
where ITEMNO in 
(
select itemno from [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].CONTITEMS where status IN (2,5)
except
select ITEMNO from [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].CONTITEMS where status IN (1,6,8)
) ,ct as 
(
select ITEMNO, CONTNO, HIREDATE, DOCDATE#5,STATUS, RECORDER from cte where rn = 1
union all 
select ITEMNO, CONTNO, HIREDATE, DOCDATE#5, STATUS, RECORDER from [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].CONTITEMS WHERE STATUS IN (1,6,8) AND ITEMNO <> '')
)
UPDATE fce
SET fce.CONTRACT = ct.CONTNO
FROM FactUtilisation_ETL fce
INNER JOIN
ON fce.StockAsset = ct.ITEMNO

wembleybear · June 10, 2020, 3:16pm

Hi

Still not quite working, I'm getting some errors when I try to run it:

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near ','.

Msg 102, Level 15, State 1, Line 14

Incorrect syntax near ')'.

Msg 156, Level 15, State 1, Line 19

Incorrect syntax near the keyword 'ON'

harishgg1 · June 10, 2020, 3:22pm

try this

;with cte as 
(
select *,ROW_NUMBER() over(partition by itemno order by DOCDATE#5 desc ) as rn from [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].CONTITEMS
where ITEMNO in 
(
select itemno from [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].CONTITEMS where status IN (2,5)
except
select ITEMNO from [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].CONTITEMS where status IN (1,6,8)
) 
),ct as 
(
select ITEMNO, CONTNO, HIREDATE, DOCDATE#5,STATUS, RECORDER from cte where rn = 1
union all 
select ITEMNO, CONTNO, HIREDATE, DOCDATE#5, STATUS, RECORDER from [LON1-ONE-APP03\INSPHIRE].[IHDATA2].[DBO].CONTITEMS WHERE STATUS IN (1,6,8) AND ITEMNO <> ''
)
UPDATE fce
SET fce.CONTRACT = ct.CONTNO
FROM FactUtilisation_ETL fce
INNER JOIN
ON fce.StockAsset = ct.ITEMNO

wembleybear · June 10, 2020, 3:32pm

Hi, thanks - I made a slight amendment and it worked! Just needed the alias ct after the INNER JOIN.

Thank you for all you help today. I am going to break the larger Update statement down and do it in individual steps in SSIS as I think that will make for easier troubleshooting in the future.

Best regards
Martyn

harishgg1 · June 10, 2020, 3:51pm

hi martyn

what you said ....
I am going to break the larger Update statement down and do it in individual steps in SSIS as I think that will make for easier troubleshooting in the future.

this road of breaking it up into small manageable pieces
and putting it back together !!!

has been travelled on ... by Many Many People
in the history of mankind ..

Please google search
Lots of different techniques .. would be nice to know !!

meta data etc etc etc