Re: Single Query for getting Range Values
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: afatkulin@xxxxxxxxx
- Date: Mon, 05 Jul 2010 21:41:32 +0200
Alex,
A count is rarely a good identifier. You can have several, distinct,
contiguous periods with the same number of rows.
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
Alex Fatkulin wrote:
> SQL> select acy_cd, min(start_dt), max(end_dte)
> 2 from (
> 3 select acy_cd, start_dt, end_dte, sum(win) over (partition by
> acy_cd order by start_dt) win_group
> 4 from (
> 5 select acy_cd, start_dt, end_dte,
> 6 case when start_dt != lag(end_dte) over (partition by acy_cd
> order by start_dt) then 1 else 0 end win
> 7 from test_acy
> 8 order by acy_cd, start_dt
> 9 ) ) group by acy_cd, win_group;
>
> ACY_CD MIN(START_DT) MAX(END_DTE)
> ------ ------------- ------------
> AB 01/01/2010 04/01/2010
> AB 06/01/2010 07/01/2010
> AB 09/01/2010 11/01/2010
> CD 11/01/2010 12/01/2010
>
> On Mon, Jul 5, 2010 at 11:14 AM, Peter Hitchman <pjhoraclel@xxxxxxxxx
> <mailto:pjhoraclel@xxxxxxxxx>> wrote:
>
> Hi,
> I had been trying to do this using analytics, but I could only get
> each column of dates using seperate queries and could not figure
> out how to join them, the left outer join to get the start date is
> inspired.
>
> Pete
>
>
> --
> Alex Fatkulin,
> http://afatkulin.blogspot.com
> http://www.linkedin.com/in/alexfatkulin
--
//www.freelists.org/webpage/oracle-l
Other related posts:
