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 ---