Suppose the table structure looks like this:
Tsheets |
Field name |
Field type |
Constraints |
Id |
CHAR (10) |
PRIMARY KEY |
Start_date |
DATE |
CHECK (start_date<= end_date) |
End_date |
DATE |
|
Enter as:
1, ' 1997-01-01 ', ' 1997-01-03 '
2, ' 1997-01-02 ', ' 1997-01-04 '
3, ' 1997-01-04 ', ' 1997-01-05 '
4, ' 1997-01-06 ', ' 1997-01-09 '
5, ' 1997-01-09 ', ' 1997-01-09 '
6, ' 1997-01-09 ', ' 1997-01-09 '
7, ' 1997-01-12 ', ' 1997-01-15 '
8, ' 1997-01-13 ', ' 1997-01-14 '
9, ' 1997-01-14 ', ' 1997-01-14 '
10, ' 1997-01-17 ', ' 1997-01-17 '
The output is:
Start_date end_date
1997-01-01 1997-01-05
1997-01-06 1997-01-09
1997-01-12 1997-01-15
1997-01-17 1997-01-17
1 SELECTX.start_date,MIN(x.end_date) asend_date2 from(SELECTt1.start_date,t2.end_date3 fromTsheets asT1,tsheets asT2,tsheets asT3WHERET1.end_date<=T2.end_dateGROUP byt1.start_date,t2.end_date4 having MAX( Case5 when(t1.start_date>t3.start_date6 andT1.start_date<=t3.end_date)7 OR(t2.end_date>=t3.start_date8 andT2.end_date<t3.end_date)9 Then 1 ELSE 0 END)= 0) asXTen GROUP byX.start_date
Problems with SQL Merge time period