--- Begin Message ---
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: Alex Fatkulin <afatkulin@xxxxxxxxx>
- Date: Mon, 05 Jul 2010 21:49:32 +0200
Well, sum (case ... end) is very like a count, when the case returns
either 0 or 1.
Unless I have missed something, if you have this data:
create table test_acy(acy_cd varchar2(5),start_dt date,end_dte date);
insert into test_acy values('AB','01Jan2010','02Jan2010');
insert into test_acy values('AB','02Jan2010','03Jan2010');
insert into test_acy values('AB','03Jan2010','04Jan2010');
insert into test_acy values('AB','06Jan2010','07Jan2010');
insert into test_acy values('AB','07Jan2010','08Jan2010');
insert into test_acy values('AB','08Jan2010','09Jan2010');
insert into test_acy values('AB','11Jan2010','12Jan2010');
insert into test_acy values('AB','12Jan2010','13Jan2010');
insert into test_acy values('CD','11Jan2010','12Jan2010');
With a gap for Jan 5th and Jan 10th, you have two groups made of three
rows for the same acy_cd, and unless I have missed something myself your
query will miss the Jan 5th gap.
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:
> can you elaborate?
>
> I don't see any counts in my query?
>
> On Mon, Jul 5, 2010 at 3:41 PM, Stephane Faroult <sfaroult@xxxxxxxxxxxx>
> wrote:
>
>> 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
>>>
>>
>>
>
>
>
>
--- End Message ---
