Thanks for the question, Srivatsan.

Asked: April 30, 2010 - 10:46 am UTC

Last updated: May 06, 2010 - 1:52 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Tom,
Cant seem to frame this question in SQL. Did search through this site but didnt find a solution (the thread on stragg with ordered was the closest but the rollup of the null rows to the last non null for from and time is still presenting a problem). Any ideas?

SQL> select * from v$version;

BANNER

----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi PL/SQL Release 10.2.0.3.0 - Production

CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

5 rows selected.

SQL> create table cust
2 (custno number
3 ,cdate date
4 ,from_time varchar2(10)
5 ,to_time varchar2(10)
6 ,seq number
7 ,comments varchar2(100));

Table created.

SQL> insert into cust values (1,'01-JAN-2010','05:00','08:00',1,'5-8 comment line 1');

1 row created.

SQL> insert into cust values (1,'01-JAN-2010',null,null,2,'5-8 comment line 2');

1 row created.

SQL> insert into cust values (1,'01-JAN-2010',null,null,3,'5-8 comment line 3');

1 row created.

SQL> insert into cust values (1,'01-JAN-2010','08:00','10:00',4,'8-10 line 1');

1 row created.

SQL> insert into cust values (1,'01-JAN-2010',null,null,5,'8-10 line 2');

1 row created.

SQL> insert into cust values (1,'01-JAN-2010','10:00','12:00',6,'10-12 line 1');

1 row created.

SQL> insert into cust values (1,'01-JAN-2010','12:00','3:00',7,'12-3 line 1');

1 row created.

SQL> insert into cust values (1,'02-JAN-2010','08:00','09:00',1,'jan2 8-9 ine1');

1 row created.

SQL> insert into cust values (1,'02-JAN-2010','09:00','14:00',2,'jan2 9-14 line1');

1 row created.

SQL> insert into cust values (1,'02-JAN-2010',null,null,3,'jan2 9-14 line 2');

1 row created.

SQL> insert into cust values (1,'02-JAN-2010',null,null,4,'jan2 9-14 line 3');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from cust order by custno, cdate, seq;

CUSTNO CDATE FROM_TIME TO_TIME SEQ COMMENTS
--------------- --------- ---------- ---------- --------------- ------------------------------------
1 01-JAN-10 05:00 08:00 1 5-8 comment line 1
1 01-JAN-10 2 5-8 comment line 2
1 01-JAN-10 3 5-8 comment line 3
1 01-JAN-10 08:00 10:00 4 8-10 line 1
1 01-JAN-10 5 8-10 line 2
1 01-JAN-10 10:00 12:00 6 10-12 line 1
1 01-JAN-10 12:00 3:00 7 12-3 line 1

1 02-JAN-10 08:00 09:00 1 jan2 8-9 ine1
1 02-JAN-10 09:00 14:00 2 jan2 9-14 line1
1 02-JAN-10 3 jan2 9-14 line 2
1 02-JAN-10 4 jan2 9-14 line 3

11 rows selected.

The requirement is to concatenate the COMMENTS to the last not null value for from_time and to_time ordered by the seq column for each custno cdate and from

and to times. Tried various things with analytics and stragg but cannot quite nail the query needed. The ouput below is what is needed (added line spacing

for clarity)

CUSTNO CDATE FROM_TIME TO_TIME COMMENTS
--------------- --------- ---------- ---------- ------------------------------------
1 01-JAN-10 05:00 08:00 5-8 comment line 1, 5-8 comment line 2, 5-8 comment line 3

1 01-JAN-10 08:00 10:00 8-10 line 1, 8-10 line 2

1 01-JAN-10 10:00 12:00 10-12 line 1

1 01-JAN-10 12:00 3:00 12-3 line 1

1 02-JAN-10 08:00 09:00 jan2 8-9 ine1

1 02-JAN-10 09:00 14:00 jan2 9-14 line1, jan2 9-14 line 2, jan2 9-14 line 3

11 rows selected.

SQL> spool off

and Tom said...

ops$tkyte%ORA10GR2> select custno, cdate,
  2         from_time,
  3         last_value(from_time ignore nulls) over (partition by custno, cdate order by seq) new_from,
  4         to_time,
  5         last_value(to_time ignore nulls) over (partition by custno, cdate order by seq) new_to,
  6             seq, comments
  7    from cust
  8   order by custno, cdate, seq;

    CUSTNO CDATE     FROM_TIME  NEW_FROM   TO_TIME    NEW_TO            SEQ COMMENTS
---------- --------- ---------- ---------- ---------- ---------- ---------- ----------------------------------------
         1 01-JAN-10 05:00      05:00      08:00      08:00               1 5-8 comment line 1
         1 01-JAN-10            05:00                 08:00               2 5-8 comment line 2
         1 01-JAN-10            05:00                 08:00               3 5-8 comment line 3
         1 01-JAN-10 08:00      08:00      10:00      10:00               4 8-10 line 1
         1 01-JAN-10            08:00                 10:00               5 8-10 line 2
         1 01-JAN-10 10:00      10:00      12:00      12:00               6 10-12 line 1
         1 01-JAN-10 12:00      12:00      3:00       3:00                7 12-3 line 1
         1 02-JAN-10 08:00      08:00      09:00      09:00               1 jan2 8-9 ine1
         1 02-JAN-10 09:00      09:00      14:00      14:00               2 jan2 9-14 line1
         1 02-JAN-10            09:00                 14:00               3 jan2 9-14 line 2
         1 02-JAN-10            09:00                 14:00               4 jan2 9-14 line 3

11 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> column comm format a40
ops$tkyte%ORA10GR2> select custno, cdate, new_from, new_to, stragg( comments ) comm
  2    from (
  3  select custno, cdate,
  4         from_time,
  5         last_value(from_time ignore nulls) over (partition by custno, cdate order by seq) new_from,
  6         to_time,
  7         last_value(to_time ignore nulls) over (partition by custno, cdate order by seq) new_to,
  8             seq, comments
  9    from cust
 10         )
 11   group by custno, cdate, new_from, new_to
 12   order by custno, cdate, new_from;

    CUSTNO CDATE     NEW_FROM   NEW_TO     COMM
---------- --------- ---------- ---------- ----------------------------------------
         1 01-JAN-10 05:00      08:00      5-8 comment line 1,5-8 comment line 2,5-
                                           8 comment line 3

         1 01-JAN-10 08:00      10:00      8-10 line 1,8-10 line 2
         1 01-JAN-10 10:00      12:00      10-12 line 1
         1 01-JAN-10 12:00      3:00       12-3 line 1
         1 02-JAN-10 08:00      09:00      jan2 8-9 ine1
         1 02-JAN-10 09:00      14:00      jan2 9-14 line1,jan2 9-14 line 2,jan2 9-
                                           14 line 3


6 rows selected.

Rating

(10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Should have doubted the power of anaylytics :)

Srivatsan Srinivasan, April 30, 2010 - 11:14 am UTC

Brilliant! (didnt think of the ignore nulls clause)

A great many thanks!

April 30, 2010 - 11:21 am UTC

You meant of course

Should NOT have doubted....

analytics rock and roll...

Followup: Got lucky with the ordering

Srivatsan Srinivasan, April 30, 2010 - 5:49 pm UTC

The order of the comments (by seq column) luckily matched the seq in the test case. Is there a way to force the comments to be concatenated in the order of the seq column or reverse. (when applied to my real query the order of concatenation was random)

For e.g can your query be rewritten to concatenate the strings in the reverse order of the seq column, This will show that we can apply an order to the concatenated columns. Is there a way to get the following results

CUSTNO CDATE FROM_TIME TO_TIME COMMENTS
--------------- --------- ---------- ---------- ------------------------------------
1 01-JAN-10 05:00 08:00 5-8 comment line 3, 5-8 comment line 2, 5-8 comment line 1

1 01-JAN-10 08:00 10:00 8-10 line 2, 8-10 line 1

1 01-JAN-10 10:00 12:00 10-12 line 1

1 01-JAN-10 12:00 3:00 12-3 line 1

1 02-JAN-10 08:00 09:00 jan2 8-9 ine1

1 02-JAN-10 09:00 14:00 jan2 9-14 line 3, jan2 9-14 line 2, jan2 9-14 line1

Thanks

May 06, 2010 - 10:27 am UTC

ops$tkyte%ORA11GR2> select custno, cdate, new_from, new_to, max( comm ) new_comments
  2    from (
  3  select custno, cdate, new_from, new_to,
  4         stragg( comments ) over (partition by custno, cdate, new_from, new_to
  5                                      order by seq DESC) comm
  6    from (
  7  select custno, cdate,
  8         from_time,
  9         last_value(from_time ignore nulls) over (partition by custno, cdate order by seq) new_from,
 10         to_time,
 11         last_value(to_time ignore nulls) over (partition by custno, cdate order by seq) new_to,
 12         seq, comments
 13    from cust
 14         )
 15         )
 16   group by custno, cdate, new_from, new_to
 17   order by custno, cdate, new_from;

    CUSTNO CDATE     NEW_FROM   NEW_TO     NEW_COMMENTS
---------- --------- ---------- ---------- ------------------------------
         1 01-JAN-10 05:00      08:00      5-8 comment line 3,5-8 comment
                                            line 2,5-8 comment line 1

         1 01-JAN-10 08:00      10:00      8-10 line 2,8-10 line 1
         1 01-JAN-10 10:00      12:00      10-12 line 1
         1 01-JAN-10 12:00      3:00       12-3 line 1
         1 02-JAN-10 08:00      09:00      jan2 8-9 ine1
         1 02-JAN-10 09:00      14:00      jan2 9-14 line 3,jan2 9-14 lin
                                           e 2,jan2 9-14 line1


6 rows selected.

Solution?

Srivatsan Srinivasan, May 03, 2010 - 11:28 am UTC

Maybe there is cleaner solution, but this seems to work!
NOTE: USED ||| as seperator for stragg

SQL> select custno, cdate, new_from, new_to, regexp_replace(wmsys.wm_concat(comm), '\[\[[0-9]+\]\]','') comments from (
  2     select distinct custno, cdate, new_from, new_to, regexp_substr(comm, '[^|||]+', 1, level) comm
  3         from
  4         (select custno, cdate, new_from, new_to, snapshots.NISTRCAT( comments ) comm
  5        from (
  6      select custno, cdate,
  7             from_time,
  8             last_value(from_time ignore nulls) over (partition by custno, cdate order by
  9  seq ) new_from,
 10             to_time,
 11             last_value(to_time ignore nulls) over (partition by custno, cdate order by
 12  seq ) new_to,
 13                 seq, '[['||lpad(seq,5,'0')||']]'||comments comments
 14        from cust
 15            )
 16      group by custno, cdate, new_from, new_to)
 17         connect by regexp_substr(comm, '[^|||]+', 1, level) is not null
 18         order by lpad(custno,10,'0')||to_char(cdate,'YYYYMMDD')||new_from||new_to||comm desc
 19         )
 20  group by custno, cdate, new_from, new_to
 21  /

         CUSTNO CDATE     NEW_FROM   NEW_TO     COMMENTS
--------------- --------- ---------- ---------- ----------------------------------------------------
              1 01-JAN-10 05:00      08:00      5-8 comment line 3,5-8 comment line 2,5-8 comment line 1
              1 01-JAN-10 08:00      10:00      8-10 line 2,8-10 line 1
              1 01-JAN-10 10:00      12:00      10-12 line 1
              1 01-JAN-10 12:00      3:00       12-3 line 2
              1 02-JAN-10 08:00      09:00      jan2 8-9 ine1
              1 02-JAN-10 09:00      14:00      jan2 9-14 line 3,jan2 9-14 line 2,jan2 9-14 line1

6 rows selected.

May 06, 2010 - 11:58 am UTC

see above for alternate solution

Correction

Srivatsan Srinivasan, May 03, 2010 - 4:32 pm UTC

Change "snapshots.NISTRCAT" to "stragg" function in the above solution, the former is a customization of the stragg function.

Can analytics do this?

DS, May 04, 2010 - 10:44 am UTC

OK Tom, This seems a simple analytic but I am stuck.


SQL> select * from v$version
  2  ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


SQL> create table bedactivity (
  2  stayid     number(11),
  3  bedactivityid number(11),
  4  startdatetime  date,
  5  enddatetime    date,
  6  wardid         number(11)
  7  );

SQL> insert into bedactivity values (1,1,to_date('2010/05/04','YYYY/MM/DD'),to_date('2010/05/05','YYYY/MM/DD'),5);

1 row created.

SQL> insert into bedactivity values (1,2,to_date('2010/05/05','YYYY/MM/DD'),to_date('2010/05/07','YYYY/MM/DD'),5);

1 row created.

SQL> insert into bedactivity values (1,3,to_date('2010/05/07','YYYY/MM/DD'),to_date('2010/05/08','YYYY/MM/DD'),6);

1 row created.

SQL> insert into bedactivity values (1,4,to_date('2010/05/08','YYYY/MM/DD'),to_date('2010/05/10','YYYY/MM/DD'),5);

1 row created.

SQL> select * from bedactivity order by bedactivityid;

    STAYID BEDACTIVITYID STARTDATE ENDDATETI     WARDID
---------- ------------- --------- --------- ----------
         1             1 04-MAY-10 05-MAY-10          5
         1             2 05-MAY-10 07-MAY-10          5
         1             3 07-MAY-10 08-MAY-10          6
         1             4 08-MAY-10 10-MAY-10          5

The result set I want to produce is:


    STAYID  STARTDATE ENDDATETI     WARDID
----------  --------- --------- ----------
         1  04-MAY-10 07-MAY-10          5
         1  07-MAY-10 08-MAY-10          6
         1  08-MAY-10 10-MAY-10          5

I cannot partition by stayid, wardid as the 2 terms in wardid 5 for the patient stay are considered separate.

Thanks-you,

May 06, 2010 - 1:23 pm UTC

.. The result set I want to produce is: ...

I always look for "logic" or "psuedo code" or "specification" after those words and am always disappointed when I see a "picture", which doesn't tell us HOW the data came to be :( So ambiguous.

I made some assumptions here.

One of them is bedactivityid is unique within at least stayid (stayid,bedactivityid are unique together)

ops$tkyte%ORA11GR2> select stayid, min(sdt), max(edt), wardid
  2    from (
  3  select stayid, bid, sdt, edt, wardid,
  4         last_value( grp ignore nulls ) over (partition by stayid, wardid order by bid) maxgrp
  5    from (
  6  select stayid, bedactivityid bid,
  7         startdatetime sdt, enddatetime edt, wardid,
  8         case when lag(enddatetime) over (partition by stayid,wardid order by bedactivityid) <> startdatetime
  9                  then bedactivityid
 10              when lag(enddatetime) over (partition by stayid,wardid order by bedactivityid) is null
 11                  then bedactivityid
 12          end grp
 13    from bedactivity
 14         )
 15             )
 16   group by stayid, wardid, maxgrp
 17   order by 1, 2
 18  /

    STAYID MIN(SDT)  MAX(EDT)      WARDID
---------- --------- --------- ----------
         1 04-MAY-10 07-MAY-10          5
         1 07-MAY-10 08-MAY-10          6
         1 08-MAY-10 10-MAY-10          5

answer!

Srivatsan Srinivasan, May 04, 2010 - 3:31 pm UTC

SQL> select * from (
  2   select distinct stayid,
  3   (case when startdatetime = lag(enddatetime) over (partition by stayid, wardid order by startdatetime)
  4   then  lag(startdatetime) over (partition by stayid, wardid order by startdatetime)
  5   else startdatetime end) startdatetime,
  6   (case when enddatetime = lead(startdatetime) over (partition by stayid, wardid order by startdatetime)
  7   then  lead(enddatetime) over (partition by stayid, wardid order by startdatetime)
  8   else enddatetime end) enddatetime,
  9    wardid
 10   from bedactivity
 11   )
 12   order by 1,2;



         STAYID STARTDATE ENDDATETI          WARDID
--------------- --------- --------- ---------------
              1 04-MAY-10 07-MAY-10               5
              1 07-MAY-10 08-MAY-10               6
              1 08-MAY-10 10-MAY-10               5

3 rows selected.

Correction

Srivatsan Srinivasan, May 04, 2010 - 4:14 pm UTC

Ignore the previous solution, does not work for overlapping and multiple overlaps. Use query below

select stayid, wardid, min(startdatetime) st, max(enddatetime) en
from (
select stayid, startdatetime, enddatetime, max(rown) over (partition by stayid, wardid order by startdatetime) maxrow, wardid
from (
select stayid, startdatetime, enddatetime,bedactivityid,
(case when lag(enddatetime) over (partition by stayid, wardid order by startdatetime) < startdatetime
then row_number() over (partition by stayid, wardid order by startdatetime) end) rown,
wardid
from bedactivity
)
)
group by stayid, wardid, maxrow
order by 1,2,3

Thanks Srivatsan!

DS, May 04, 2010 - 5:07 pm UTC

Thanks, Srivatsan. I came up with this:

select stayid,wardid, st,ed from (
select y.*, first_value(startdatetime) over (partition by y.stayid, y.maxrown order by y.startdatetime) as st,
            last_value (enddatetime) over (partition by y.stayid, y.maxrown order by y.startdatetime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as ed
            from (
select x.*,
       max(x.rown) over (partition by x.stayid order by x.startdatetime) as maxrown
     from (
select a.*,decode( lag(a.wardid) over (partition by stayid order by a.startdatetime),wardid,
       to_number(null),
       row_number() over (partition by stayid order by a.startdatetime)) as rown
from bedactivity a
) x
)y
) where rown is not null

I wonder if Tom has a neater solution!

DS, May 05, 2010 - 3:01 am UTC

Is there a better solution Tom? we require something with best possible performance - thanks for your help.

May 06, 2010 - 1:52 pm UTC

see above

IGNORE NULLS

Duke Ganote, June 02, 2010 - 8:17 am UTC

Nice feature, but I think I was doing something similar back in 8i/9i with:

with t1 as (
  select  1 as SortKey,2 as Val from dual union all
  select  0,null from dual union all
  select  2,null from dual union all
  select  5,   4 from dual union all
  select  9,null from dual union all
  select 11,   6 from dual union all
  select 12,null from dual union all
  select 14,null from dual union all
  select 16,   5 from dual union all
  select 17,null from dual union all
  select 20,   3 from dual union all
  select 21,null from dual union all
  select 22,   4 from dual)
select first_value(val) over (order by
          case when val is null
               then sortkey+99999999
               else sortkey
          end ASC ) mockery
     , first_value(val IGNORE NULLS) over (order by
          sortkey ASC ) ignorance
     , case when val is null
            then sortkey+99999999
            else sortkey
        end as sortie
     , t1.*
  from t1
 order by sortie
/
MOCKERY  IGNORANCE     SORTIE    SORTKEY        VAL
------- ---------- ---------- ---------- ----------
      2          2          1          1          2
      2          2          5          5          4
      2          2         11         11          6
      2          2         16         16          5
      2          2         20         20          3
      2          2         22         22          4
      2              99999999          0
      2          2  100000001          2
      2          2  100000008          9
      2          2  100000011         12
      2          2  100000013         14
      2          2  100000016         17
      2          2  100000020         21

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Should have doubted the power of anaylytics :)

Followup: Got lucky with the ordering

Solution?

Correction

Can analytics do this?

answer!

Correction

Thanks Srivatsan!

IGNORE NULLS

More to Explore

Analytics

Can analytics solve this

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Should have doubted the power of anaylytics :)

Followup: Got lucky with the ordering

Solution?

Correction

Can analytics do this?

answer!

Correction

Thanks Srivatsan!

IGNORE NULLS

More to Explore

Analytics