Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Rank/Sequence Query Help

791304Aug 13 2010 — edited Aug 24 2010
I am working to create a 'Current State' calculation for a thresholding tool, answering the question 'How many months has this been Red/Green?' (1 = Red; 6 = Green)

I have a query that returns the following...


START_TIME MIN_TARGET_LEVEL RTOT Trying to Get
01/01/2010 6 1 1
02/01/2010 6 2 2
03/01/2010 6 3 3
04/01/2010 1 1 1
05/01/2010 6 4 1
06/01/2010 1 2 1
07/01/2010 1 3 2

How do I get the sequence to restart after each change in the Result? I have tried playing with counts; lags; running totals and row num to no avail.

Select START_TIME,
MIN_TARGET_LEVEL,
COUNTS,
SUM(COUNTS) OVER (PARTITION BY MIN_TARGET_LEVEL ORDER by START_TIME) as rtot
From (Select DFL_PROD.nt(START_TIME) START_TIME,
MIN_TARGET_LEVEL,
MIN_TARGET_LEVEL LVL1,
MIN_TARGET_LEVEL LVL2,
1 as COUNTS
from DFL_PROD.WSF_OP_7085_28996_ROLLUP_C
Order By START_TIME) SRC
Order by START_TIME
This post has been answered by 737905 on Aug 24 2010
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 21 2010
Added on Aug 13 2010
9 comments
1,568 views