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);
SELECT ResourceID, CodeID, COUNT(*) FROM table GROUP BY ResourceID, CodeID
.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).