CODEWITHSUNDEEP
sqlsql-serverdate
Heisenberg_008
Heisenberg_008

Reputation: 21

SQL:How can I find unique date ranges from a given a set of date ranges?

I have a table of users along with their date ranges in INT format(yyyymmdd) as below:

Id  Name    StartDate   EndDate
1   abc     20160216    20160217
1   abc     20160228    20160228
1   abc     20160301    20160301
1   abc     20160301    20160302
1   abc     20160301    20170401
1   abc     20160302    20160302
1   abc     20160303    20160303
1   abc     20160303    20170401
3   def     20160217    20160217
3   def     20160218    20160229
3   def     20160218    20160229
3   def     20160225    20160225
3   def     20160229    20160229
3   def     20160302    20160302

I need to find consecutive unique date ranges for every user(without any duplicates). The output should like this

 Id Name    StartDate   EndDate
1   abc     20160216    20160217
1   abc     20160228    20160228
1   abc     20160301    20170401
3   def     20160217    20160229
3   def     20160302    20160302

With this script you can create the table:

CREATE TABLE #data
(
 Id INT,
 Name VARCHAR(50),
 StartDate INT,
 EndDate INT,
)
INSERT INTO #data VALUES(1,'abc',20160216,20160217)
INSERT INTO #data VALUES(1,'abc',20160228,20160228)
INSERT INTO #data VALUES(1,'abc',20160301,20160301)
INSERT INTO #data VALUES(1,'abc',20160301,20160302)
INSERT INTO #data VALUES(1,'abc',20160301,20170401)
INSERT INTO #data VALUES(1,'abc',20160302,20160302)
INSERT INTO #data VALUES(1,'abc',20160303,20160303)
INSERT INTO #data VALUES(1,'abc',20160303,20170401)
INSERT INTO #data VALUES(3,'def',20160217,20160217)
INSERT INTO #data VALUES(3,'def',20160218,20160229)
INSERT INTO #data VALUES(3,'def',20160218,20160229)
INSERT INTO #data VALUES(3,'def',20160225,20160225)
INSERT INTO #data VALUES(3,'def',20160229,20160229)
INSERT INTO #data VALUES(3,'def',20160302,20160302)

EDIT: In Short, If I have data like this

Id  Name    StartDate   EndDate
1   abc     20160216    20160217
**1 abc     20160301    20160302
1   abc     20160301    20160303**

I need output like below.

Id  Name    StartDate   EndDate
1   abc     20160216    20160217
**1 abc     20160301    20160303**

Upvotes: 1

Views: 76

Answers (2)

Luis G. Lino
Luis G. Lino

Reputation: 163

Guess I you'll need to use SELECT DISTINCT, which searches only the unique values from the column of the table.

Is that what you're looking for? I didn't get exactly how do you want the output...

Upvotes: 1

Seth McClaine
Seth McClaine

Reputation: 10040

You should be able to concat StartDate and EndDate and look for the concated value to be distinct.

select id, StartDate, EndDate, distinct(contactDate) from (Select id, StartDate, EndDate, CONCAT_WS('', StartDate, EndDate) AS concatDate from Data)

Edit

Actually I think you can do it without concating as well...

Select id, StartDate, EndDate, distinct(StartDate, EndDate) from data

Upvotes: 1

Related Questions