We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

cumulative calculation with partition by

seenall
seenall asked
on
Medium Priority
1,131 Views
Last Modified: 2013-12-07

Typical record set as follows

Friend1    01/01/2009    5
Friend1    03/01/2009    4
Friend1    07/01/2009    6
Friend1    09/01/2009    3
Friend1    12/01/2009    6
Friend1    13/01/2009    4

Now - I want to find the first date when the accumulated timespent is equal or greater than 20. - so in the example above on 12 January the accumulated time was 24

The query should then halt at 12 and move onto the next friend.

Might this be done with an Analytical function? - I am using Oracle RDBMS with Data Warehousing
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Try this query:

This should help you out..
WITH cte ( col1, date1, count1, cnt) as
(SELECT col1, date1, count1, row_number() OVER ( ORDER BY date1 ASC) cnt
FROM urtable ) 
select col1, date1, count1, cumulative from 
(
SELECT t1.col1, t1.date1, t1.count1, (SELECT sum(cte.count1) FROM cte WHERE cte.cnt <= t1.cnt) cumulative
FROM cte t1) as tmp
where cumulative <= 20

Open in new window

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
try this...

you should only need to query the table once.
The query above should work also but it looks like it was written for sql server where there isn't an analytic version of SUM which is more efficient.

of course, don't trust, instead test,
time all suggestions and use autotrace to measure the activity of each to determine which does less work on your system.
SELECT friend, eventdate, timespent
  FROM (SELECT friend,
               eventdate,
               timespent,
               sumtime,
               ROW_NUMBER() OVER (PARTITION BY friend ORDER BY eventdate) rn
          FROM (SELECT friend,
                       eventdate,
                       timespent,
                       SUM(timespent)
                           OVER (PARTITION BY friend ORDER BY eventdate ROWS UNBOUNDED PRECEDING)
                           sumtime
                  FROM yourtable)
         WHERE sumtime >= 20)
 WHERE rn = 1

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks very much for this - I think I understand the partition by statement now - until the next time :D - Great work
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
glad I could help.  analytics are definitely an awesome set of tools to learn.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.