SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Resources (ResourceID int, ReferenceDate date, CodeID int);
INSERT INTO Resources VALUES
( 893, '2018-05-14', 1 ),
( 656, '2018-05-07', 1 ),
( 656, '2018-05-22', 1 ),
( 353, '2018-05-07', 1 ),
( 530, '2018-05-07', 1 ),
( 541, '2018-05-07', 1 ),
( 755, '2018-05-07', 1 ),
(  55, '2018-05-07', 1 ),
( 382, '2018-05-07', 1 ),
( 224, '2018-05-07', 1 ),
(1115, '2018-05-07', 1 ),
( 191, '2018-05-07', 5 ),
( 191, '2018-05-08', 5 ),
( 191, '2018-05-09', 5 ),
( 655, '2018-07-06', 6 ),
( 655, '2018-07-10', 6 ),
( 892, '2018-07-06', 6 );
17 rows affected

SELECT
    ResourceID, ReferenceDate, CodeID,
    IIF(COALESCE(LAG(CodeID) OVER (ORDER BY ResourceID, ReferenceDate), 0) <> CodeID, 1, NULL) Rst
FROM
    Resources


ResourceID
ReferenceDate
CodeID
Rst


55
07/05/2018 00:00:00
1
1

191
07/05/2018 00:00:00
5
1

191
08/05/2018 00:00:00
5
null

191
09/05/2018 00:00:00
5
null

224
07/05/2018 00:00:00
1
1

353
07/05/2018 00:00:00
1
null

382
07/05/2018 00:00:00
1
null

530
07/05/2018 00:00:00
1
null

541
07/05/2018 00:00:00
1
null

655
06/07/2018 00:00:00
6
1

655
10/07/2018 00:00:00
6
null

656
07/05/2018 00:00:00
1
1

656
22/05/2018 00:00:00
1
null

755
07/05/2018 00:00:00
1
null

892
06/07/2018 00:00:00
6
1

893
14/05/2018 00:00:00
1
1

1115
07/05/2018 00:00:00
1
null


WITH ct1 AS
(
  SELECT
      ResourceID, ReferenceDate, CodeID,
      IIF(COALESCE(LAG(CodeID) OVER (ORDER BY ResourceID, ReferenceDate), 0) <> CodeID
          AND 
          COALESCE(LAG(ResourceID) OVER (ORDER BY ResourceID, ReferenceDate), 0) <> ResourceID, 
         1, NULL) Rst
  FROM
      Resources
)
, ct2 AS
  (
    SELECT
        ResourceID, ReferenceDate, CodeID,
        SUM(Rst) OVER (ORDER BY ResourceID, ReferenceDate) as Grp
    FROM
        ct1
   )
   SELECT
       ResourceID, CodeID, COUNT(*) CntCodeID
   FROM
       ct2
   GROUP BY
       ResourceID, CodeID, Grp;


ResourceID
CodeID
CntCodeID


55
1
1

191
5
3

224
1
1

353
1
1

382
1
1

530
1
1

541
1
1

655
6
2

656
1
2

755
1
1

892
6
1

893
1
1

1115
1
1


Warning: Null value is eliminated by an aggregate or other SET operation.

ResourceID	ReferenceDate	CodeID	Rst
55	07/05/2018 00:00:00	1	1
191	07/05/2018 00:00:00	5	1
191	08/05/2018 00:00:00	5	null
191	09/05/2018 00:00:00	5	null
224	07/05/2018 00:00:00	1	1
353	07/05/2018 00:00:00	1	null
382	07/05/2018 00:00:00	1	null
530	07/05/2018 00:00:00	1	null
541	07/05/2018 00:00:00	1	null
655	06/07/2018 00:00:00	6	1
655	10/07/2018 00:00:00	6	null
656	07/05/2018 00:00:00	1	1
656	22/05/2018 00:00:00	1	null
755	07/05/2018 00:00:00	1	null
892	06/07/2018 00:00:00	6	1
893	14/05/2018 00:00:00	1	1
1115	07/05/2018 00:00:00	1	null