Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query to get date ranges from a list of dates (Oracle 10g Solaris)
icon5.gif  SQL Query to get date ranges from a list of dates [message #421868] Fri, 11 September 2009 05:02 Go to next message
suchafunkymonkey
Messages: 3
Registered: September 2009
Junior Member
Hi all.

I have a tricky SQL problem that you may be able to help me out with. I have a single table with looks like this:

create table sa_bom_empty_teams (
team_id number(5),
daterange date);

In the table I have a list of team_ids and single dates which would look like the following:

team_id date_range
1 01/01/2007
1 02/01/2007
1 03/01/2007
1 10/01/2007
1 11/01/2007
1 12/01/2007
1 01/01/2009
1 02/01/2009
2 01/01/2007
2 02/01/2007
3 12/01/2007
3 13/01/2007
3 14/01/2007

What I would like to get is a list of the team_ids and the daterange for each
team where there was no break in the date.
e.g.
Team_id From To
1 01/01/2007 03/01/2007
1 10/01/2007 12/01/2007
1 01/01/2009 02/01/2009
2 01/01/2007 02/01/2007
3 12/01/2007 14/01/2007

I'd like to avoid doing it with pl/sql (if possible). It seems a very simple thing to do, but I haven't figured it out yet.

Thanks.
Re: SQL Query to get date ranges from a list of dates [message #421874 is a reply to message #421868] Fri, 11 September 2009 05:17 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You could select:

- the max date
- the min date
- max date minus min date
- count of dates

If you then compare the difference between the max and min date with the count, you see if there are enough dates or dates missing.
Re: SQL Query to get date ranges from a list of dates [message #421875 is a reply to message #421874] Fri, 11 September 2009 05:19 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah, nevermind.I mis-understood the question, it seems.

You could try possibly try to find sequences with no gaps with the lead/lag function.
Re: SQL Query to get date ranges from a list of dates [message #421876 is a reply to message #421868] Fri, 11 September 2009 05:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
create table test_063 (team_id  number, date_range  date);

insert into test_063 values (1	 ,to_date('01/01/2007','dd/mm/yyyy'));
insert into test_063 values (1	 ,to_date('02/01/2007','dd/mm/yyyy'));
insert into test_063 values (1	 ,to_date('03/01/2007','dd/mm/yyyy'));
insert into test_063 values (1	 ,to_date('10/01/2007','dd/mm/yyyy'));
insert into test_063 values (1	 ,to_date('11/01/2007','dd/mm/yyyy'));
insert into test_063 values (1	 ,to_date('12/01/2007','dd/mm/yyyy'));
insert into test_063 values (1	 ,to_date('01/01/2009','dd/mm/yyyy'));
insert into test_063 values (1	 ,to_date('02/01/2009','dd/mm/yyyy'));
insert into test_063 values (2	 ,to_date('01/01/2007','dd/mm/yyyy'));
insert into test_063 values (2	 ,to_date('02/01/2007','dd/mm/yyyy'));
insert into test_063 values (3	 ,to_date('12/01/2007','dd/mm/yyyy'));
insert into test_063 values (3	 ,to_date('13/01/2007','dd/mm/yyyy'));
insert into test_063 values (3	 ,to_date('14/01/2007','dd/mm/yyyy'));

commit;

select team_id
      ,min(date_range) date_from
      ,max(date_range) date_to
from  (select team_id
             ,date_range
             ,date_range - row_number() over (partition by team_id order by date_range) grp
       from   test_063)
group by team_id,grp
order by team_id,date_from;
Re: SQL Query to get date ranges from a list of dates [message #421878 is a reply to message #421868] Fri, 11 September 2009 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has been asked many times, please search before posting.

Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: SQL Query to get date ranges from a list of dates [message #421884 is a reply to message #421876] Fri, 11 September 2009 06:17 Go to previous messageGo to next message
suchafunkymonkey
Messages: 3
Registered: September 2009
Junior Member
This appears to work except when the team_id gets to 5 digits:
Team From To
155 27/02/2009 17/03/2009
155 19/03/2009 31/12/2013
156 14/02/2009 31/12/2013
157 28/02/2009 31/12/2013
174 30/01/2009 23/02/2009
174 27/03/2009 31/12/2013
198 01/01/2007 02/11/2008
198 06/11/2008 18/11/2008
199 01/01/2007 05/11/2008
50000 01/01/2007 02/01/2007
50000 01/01/2007 01/01/2007
50000 02/01/2007 03/01/2007
50000 03/01/2007 04/01/2007
50000 04/01/2007 05/01/2007
50000 05/01/2007 06/01/2007
50000 06/01/2007 07/01/2007
50000 07/01/2007 08/01/2007
50000 08/01/2007 09/01/2007
50000 09/01/2007 10/01/2007
50000 10/01/2007 11/01/2007
Re: SQL Query to get date ranges from a list of dates [message #421885 is a reply to message #421884] Fri, 11 September 2009 06:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's nothing to do with the size of the team id - there's something up with the data.

Can you post the actual data in the table for team_id 50000.
Re: SQL Query to get date ranges from a list of dates [message #421886 is a reply to message #421884] Fri, 11 September 2009 06:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Got it - You've got a time component on the dates for that id.

Either correct the data, or use this as a query:
select team_id
      ,min(date_range) date_from
      ,max(date_range) date_to
from  (select team_id
             ,date_range
             ,trunc(date_range) - row_number() over (partition by team_id order by date_range) grp
       from   test_063)
group by team_id,grp
order by team_id,date_from;
Re: SQL Query to get date ranges from a list of dates [message #421887 is a reply to message #421886] Fri, 11 September 2009 06:29 Go to previous message
suchafunkymonkey
Messages: 3
Registered: September 2009
Junior Member
I found the cause, for some reason the teams with IDs over 50000 had duplicates for each day. Once they were removed, everything worked.

Thanks a lot.
Previous Topic: confusion in passing parameters to function
Next Topic: additional condition
Goto Forum:
  


Current Time: Wed Sep 28 05:50:58 CDT 2016

Total time taken to generate the page: 0.10426 seconds