By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE PayerMode (
Location[int] NOT NULL,
Customer INT NOT NULL,
Date DATE NOT NULL,
PayMode Varchar(50) NOT NULL
)
INSERT INTO PayerMode VALUES (100, 45454, '3/20/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45454, '3/21/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45454, '3/22/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45454, '3/25/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45454, '3/26/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45454, '3/27/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45454, '3/29/2014','Cash');
INSERT INTO PayerMode VALUES (100, 45454, '3/30/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45454, '3/31/2014','Credit');
INSERT INTO PayerMode VALUES (100, 45454, '4/1/2014','Credit');
INSERT INTO PayerMode VALUES (100, 45678, '3/20/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45678, '3/21/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45678, '3/22/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45678, '3/23/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45678, '3/24/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45678, '3/27/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45678, '3/28/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45678, '3/31/2014','Credit');
INSERT INTO PayerMode VALUES (100, 45678, '4/1/2014','Credit');
19 rows affected
select location, customer, paymode, min(date), max(date)
from (select pm.*,
row_number() over (partition by location, customer order by date) as seqnum,
row_number() over (partition by location, customer, paymode order by date) as seqnum_2
from payermode pm
) pm
group by (seqnum - seqnum_2), location, customer, paymode
order by location, customer, min(date);
| location | customer | paymode | (No column name) | (No column name) |
|---|---|---|---|---|
| 100 | 45454 | Insurance | 2014-03-20 | 2014-03-27 |
| 100 | 45454 | Cash | 2014-03-29 | 2014-03-29 |
| 100 | 45454 | Insurance | 2014-03-30 | 2014-03-30 |
| 100 | 45454 | Credit | 2014-03-31 | 2014-04-01 |
| 100 | 45678 | Insurance | 2014-03-20 | 2014-03-28 |
| 100 | 45678 | Credit | 2014-03-31 | 2014-04-01 |
select location, customer, paymode, min(date), max(date)
from (select pm.*,
row_number() over (partition by location, customer, paymode order by date) as seqnum
from payermode pm
) pm
group by dateadd(day, -seqnum, date), location, customer, paymode
order by location, customer, min(date);
| location | customer | paymode | (No column name) | (No column name) |
|---|---|---|---|---|
| 100 | 45454 | Insurance | 2014-03-20 | 2014-03-22 |
| 100 | 45454 | Insurance | 2014-03-25 | 2014-03-27 |
| 100 | 45454 | Cash | 2014-03-29 | 2014-03-29 |
| 100 | 45454 | Insurance | 2014-03-30 | 2014-03-30 |
| 100 | 45454 | Credit | 2014-03-31 | 2014-04-01 |
| 100 | 45678 | Insurance | 2014-03-20 | 2014-03-24 |
| 100 | 45678 | Insurance | 2014-03-27 | 2014-03-28 |
| 100 | 45678 | Credit | 2014-03-31 | 2014-04-01 |