Count Consecutive Codes I'm searching for a query (cannot figure it out myself at the moment with the help of already found queries around here. The quest I have is the following one: I have a view that gives following output  

  ResourceID | ReferenceDate | CodeID 
 ------------|---------------|-------- 
         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 


I want to group the CodeID's with a sort by ResourceID and then ReferenceDate. With this result I want to get something like the following table  

  ResourceID | CodeID | Count 
 ------------|--------|------- 
         893 |      1 |     1 
         656 |      1 |     2 
         353 |      1 |     1 
  ...
         191 |      5 |     3 
         655 |      6 |     2 
         892 |      6 |     1 


This to get to a result where we can say that ResourceNumber x has y consecutive days CodeID z.

EDIT: The question looks like this one but with the difference (as far as I see it) that I need to have the count combination or ResourceID and CodeID: Determine consecutive occurrences of values



Code to create sample data for testing:

CREATE TABLE dbo.Resources
(
    ResourceID INT NOT NULL,
    ReferenceDate DATE NOT NULL,
    CodeID INT NOT NULL
);
GO

INSERT INTO dbo.Resources
    (ResourceID, ReferenceDate, CodeID)
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);
If I've understood correctly you need to set a group every time CodeID changes. You can try by using LAG() function.


WITH ct1 AS
(
  SELECT
      ResourceID, ReferenceDate, CodeID,
      IIF(COALESCE(LAG(CodeID) OVER (ORDER BY ResourceID, ReferenceDate), 0) &lt;&gt; CodeID
          AND 
          COALESCE(LAG(ResourceID) OVER (ORDER BY ResourceID, ReferenceDate), 0) &lt;&gt; 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;

GO


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


db&lt;&gt;fiddle here