how find collective range in two period in database

Question

i have a table in database , and i want write query code to select collective date between two record .

this is sample code :

table is :

CREATE TABLE `user` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `startdate` date NOT NULL,
  `enddate` date NOT NULL,
  PRIMARY KEY (`userid`)
)

example 1 and i have 2 record in table .

(userid =>1 , startdate => '2012-02-02' , enddate => '2012-10-02') (userid =>2 , startdate => '2012-04-02' , enddate => '2012-09-02')

i want select query to give range '2012-04-02' until '2012-09-02'

example 2 and i have 2 record in table .

(userid =>1 , startdate => '2012-02-02' , enddate => '2012-02-12') (userid =>2 , startdate => '2012-04-02' , enddate => '2012-09-02')

i want select query to give null range

is there best query for this?

What means 'select collective date between two record ' ? Can you post sample with data? Also, what have you tried? — dani herrera, Dec 19 '12 at 10:15

valex · Accepted Answer · 2012-12-19T11:11:31.603

1

If I've got it right and you need time period that is an intersection of periods from these rows:

select * from
(
    select max(startdate) d1, min(enddate) d2 from user
) where d1<=d2

edited Dec 19 '12 at 11:11

answered Dec 19 '12 at 10:29

valex

if my data is (userid =>1 , startdate => '2012-02-02' , enddate => '2012-02-10') (userid =>2 , startdate => '2012-04-02' , enddate => '2012-09-02') this code can not return collective date – aya Dec 19 '12 at 10:41
I've fixed query to handle case like that – valex Dec 19 '12 at 11:11
thanks valex i think you should `as t` after ( select max(startdate) d1, min(enddate) d2 from user ) – aya Dec 19 '12 at 11:20
@aya If you use MySQL then YES - you should give a name for a subquery in FROM class. – valex Dec 19 '12 at 12:18

Mariappan Subramanian · Answer 2 · 2012-12-19T10:24:55.873

0

I guess this is what you want,

  select userid,min(startdate),max(enddate),abs(max(enddate)-min(startdate)) as range 
  from user group by userid;

edited Dec 19 '12 at 10:24

answered Dec 19 '12 at 10:19

2 Answers2