Sql-server – Count Consecutive Codes

gaps-and-islandssql serversql server 2014

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);

Best Answer

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) <> 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;

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<>fiddle here

Related Solutions

T-sql – Using Row_Number to find consecutive row count

The general name for this type of query is "gaps and islands". One approach below. If you can have duplicates in the source data you might need dense_rank rather than row_number

WITH DATA(C) AS
(
SELECT 724 UNION ALL
SELECT 727 UNION ALL
SELECT 728 UNION ALL
SELECT 729 UNION ALL
SELECT 735 UNION ALL
SELECT 737 UNION ALL
SELECT 743 UNION ALL
SELECT 744 UNION ALL
SELECT 747 UNION ALL
SELECT 749
), T1 AS
(
SELECT C,
       C - ROW_NUMBER() OVER (ORDER BY C) AS Grp
FROM DATA)
SELECT C,
       ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY C) AS Consecutive
FROM T1

Returns

C           Consecutive
----------- --------------------
724         1
727         1
728         2
729         3
735         1
737         1
743         1
744         2
747         1
749         1

PostgreSQL – Counting 3 Consecutive Unsynced Dates

This counts ranges of at least 3 days with minutes_asleep = 0.
A continuous range of 5 days still counts as 1.
A continuous range of 6 days counts as 2. Etc.
Ignoring all other entries where minutes_asleep is different.

SELECT sum(ct) AS total_count
FROM  (
   SELECT (count(*)/3)::int AS ct  -- integer division truncates as desired + see below
   FROM (
      SELECT user_id
           , the_date - (row_number() OVER (PARTITION BY user_id
                                            ORDER BY the_date))::int AS grp
      FROM   fitness_data
      WHERE  minutes_asleep = 0
      ) sub1
   GROUP  BY user_id, grp
   HAVING count(*) >= 3
   ) sub2
UNION ALL  -- see below
SELECT 0
LIMIT 1;

UNION ALL ... is just to return 0 instead of "no row" where no qualifying ranges are found. You could also use COALESCE in another subselect, but I find this more elegant.

Why (count(*)/3)::int? count() returns bigint. sum(bigint) returns numeric. I am pretty positive we don't need to go that far. Casting back to int, so we get a bigint result. Should be faster, too.

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

Best Answer

Related Solutions

T-sql – Using Row_Number to find consecutive row count

PostgreSQL – Counting 3 Consecutive Unsynced Dates

Related Question