[Fwd: Re: Single Query for getting Range Values]

Problem with overquoting.
--- 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 ---

Other related posts: