RE: Oracle Analytic Function - Suggestion/Tips needed

From: <Christopher.Taylor2_at_parallon.net>
Date: Thu, 18 Oct 2012 07:31:36 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88562E4819D_at_NADCWPMSGCMS10.hca.corpad.net>

Good point - I left that out.
The start_Dates/end_Dates can be the same or not for each grouping of Bs.

Here's whats happening:

Group A, C, D run "serially", while the members in group B run in parallel - their start dates may or may not be the exact same HH:MI:SS.

I came up with a solution using a few replace(replace(replace( ))) statements on the B groups to get me down to what I need.

The B groups have the following detailed name structure:

First B group in the 1st Set:
"Nightly Calc-0123 [Thread 1 of 3]"
"Nightly Calc-0123 [Thread 2 of 3]"
"Nightly Calc-0123 [Thread 3 of 3]"

Second B group in the 1st Set:
"Nightly Calc-0223 [Thread 1 of 3]"
"Nightly Calc-0223 [Thread 2 of 3]"
"Nightly Calc-0223 [Thread 3 of 3]"

After looking at the data, I just needed to replace the " [Thread x of x]" with empty string. I could probably use regexp_replace but I settled for a string of replace commands to be quick and dirty.

Thanks for the help!

Chris

From: Ghassan Salem [mailto:salem.ghassan_at_gmail.com] Sent: Thursday, October 18, 2012 7:23 AM To: Taylor Christopher - Nashville
Cc: oracle-l_at_freelists.org
Subject: Re: Oracle Analytic Function - Suggestion/Tips needed

Chris,
you did not specify how to order your data, but it seems to be on start_date. The other thing missing is that if start_date for each group is the same. It seems so from your data, but it may not be always the case.

If the above assumption is good, use something like this select a.*, row_number() over (partition by name,start_date order by .....) from ....

If the assumption is not true, you need to tell us how to find the 'groups'. On Thu, Oct 18, 2012 at 1:44 PM, <Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>> wrote: Version 10.2.0.4

I've been going round and round with this.

Here's a sample of my data and what I need out of it.

NAME....DETAILED_NAME....START_DATE________.....END_DATE_________
A.......Aaaaaaaaaaaaa....10/17/12 00:00:00......10/17/12 00:00:05
B.......Bbbbbbbbbbbb1....10/17/12 00:00:06......10/17/12 00:00:10
B.......Bbbbbbbbbbbb2....10/17/12 00:00:06......10/17/12 00:00:15
B.......Bbbbbbbbbbbb3....10/17/12 00:00:06......10/17/12 00:00:12
C.......Cccccccccccc1....10/17/12 00:00:16......10/17/12 00:00:20
B.......Bbbbbbbbbbbb1....10/17/12 00:00:21......10/17/12 00:00:30
B.......Bbbbbbbbbbbb2....10/17/12 00:00:21......10/17/12 00:00:30
B.......Bbbbbbbbbbbb3....10/17/12 00:00:21......10/17/12 00:00:28
D.......Dddddddddddd1....10/17/12 00:00:31......10/17/12 00:00:40

I need to be able to number (rank) the As,Bs,Cs,Ds having the rank start over for each group that appears more than once so that it looks like this:

NAME....RANK
A.........1.
B.........1.
B.........2.
B.........3.
C.........1.
B.........1.
B.........2.
B.........3.
D.........1.

Basically I need the RANK to start over at 1 for the 4th B and I cannot get my partitioning to work out right.

Any thoughts?

Chris

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 18 2012 - 14:31:36 CEST