• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1100
  • Last Modified:

cumulative calculation with partition by


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
0
seenall
Asked:
seenall
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

0
 
sdstuberCommented:
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

0
 
seenallAuthor Commented:
Thanks very much for this - I think I understand the partition by statement now - until the next time :D - Great work
0
 
sdstuberCommented:
glad I could help.  analytics are definitely an awesome set of tools to learn.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now