How can I get the StartDate and endDate from a table in SQL?

Question

I have this table. I would like to see the result by startdate and enddate. The bottom image is output. I want output in that format:

CREATE TABLE [dbo].[PayerMode](
    [Location] [int] NOT NULL,
    [Customer] INT NOT NULL,
    [Date] DATE NOT NULL,
    [PayMode] Varchar(50) NOT NULL
)
GO

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/23/2014','Cash');
INSERT INTO PayerMode VALUES (100, 45454, '3/24/2014','Cash');
INSERT INTO PayerMode VALUES (100, 45454, '3/25/2014','Credit');
INSERT INTO PayerMode VALUES (100, 45454, '3/26/2014','Credit');
INSERT INTO PayerMode VALUES (100, 45454, '3/27/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45454, '3/28/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','Cash');
INSERT INTO PayerMode VALUES (100, 45678, '3/25/2014','Credit');
INSERT INTO PayerMode VALUES (100, 45678, '3/26/2014','Credit');
INSERT INTO PayerMode VALUES (100, 45678, '3/27/2014','Credit');
INSERT INTO PayerMode VALUES (100, 45678, '3/28/2014','Cash');
INSERT INTO PayerMode VALUES (100, 45678, '3/29/2014','Cash');
INSERT INTO PayerMode VALUES (100, 45678, '3/30/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45678, '3/31/2014','Insurance');
INSERT INTO PayerMode VALUES (100, 45678, '4/1/2014','Credit');

Now, I am seeing that there is a gap in my dataset. How can I achieve the same result. when there is a gap then the above query is not working. Here is my new dataset and I want the same result with StartDate and EndDate.

CREATE TABLE [dbo].[PayerMode](
    [Location] [int] NOT NULL,
    [Customer] INT NOT NULL,
    [Date] DATE NOT NULL,
    [PayMode] Varchar(50) NOT NULL
)
GO

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

I want result like this.

Location customer StartDate enddate     paymode
100      45454    3/20/2014  3/22/2014  Insurance
100      45454    3/25/2014  3/27/2014  Insurance
100      45454    3/29/2014  3/29/2014  Cash  

Answer 1

This is a type of gaps-and-islands problem. This version is probably most simply solved using the difference of row numbers:

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

Here is a db<>fiddle.

Why this works is a little tricky to explain. But if you look at the results of the subquery, you will see how the difference of row numbers identifies adjacent rows with the same value.

EDIT:

If you want consecutive dates to be combined, the problem is even simpler:

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

Here is a db<>fiddle for this version.