0

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);
  • Please tag your SQL Server version. – McNets May 6 at 13:52
  • 1
    Looks like trivial SELECT ResourceID, CodeID, COUNT(*) FROM table GROUP BY ResourceID, CodeID. – Akina May 6 at 13:54
  • Could you elaborate on what you mean by "Consecutive"? Resource 656 has a gap of 15 days between it's dates. – Cowthulhu May 6 at 14:14
  • @Cowthulhu It's just the consecutive records sorted by date, it does not always mean that the dates will follow up nicely. Sorry for not being complete on this. – TimVK May 6 at 14:43
  • with a sort by ResourceID and then ReferenceDate - I'm not following your output, then. You have CodeID 1 with ResourceID 893, then 656, then 353. Those are not in order by ResourceID, and the dates corresponding to those ResourceIDs are also not used in any obvious way for sorting. Please provide better sample data that covers edge cases (like when a different code does interrupt the sequence and how that affects the count). – Aaron Bertrand May 6 at 14:51
2

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

  • Hi, thanks for the information though it is still just counting the number of codes as I see it with this code. I've added some test records to the db<>fiddle here: dbfiddle.uk/…. Resource 191 has a codeID = 1 in between the Code 5 records and still it is just counting all the code 5 together instead of CodeID = 5 x 3, CodeID = 1 x 1, CodeID = 5 x 3. – TimVK May 10 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.