SQL Question for Running Totals

digitalwise
digitalwise used Ask the Experts™
on
I have the following SQL which really well to pull the sum of the registrations by week

SELECT
 EVT_CFG_ITEM.DESCRIPTION,
  sum(EVT_REG_ITEM.QTY),
  cast((CASE
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-09-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 1'
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-16-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 2'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-23-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 3'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-30-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 4'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-07-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 5'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-14-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 6'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-21-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 7'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-28-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 8'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('01-04-2018 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 9'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('01-11-2018 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 10'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('01-18-2018 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 11'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('01-25-2018 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 12'
                    ELSE 'NONE'
               END) as VARCHAR(100)) as rangedate   FROM
  EVT_REG_ITEM RIGHT JOIN EVT_CFG_ITEM ON (EVT_CFG_ITEM.EVENT_ID=EVT_REG_ITEM.EVENT_ID and EVT_CFG_ITEM.ITEM_ID=EVT_REG_ITEM.ITEM_ID)
  
WHERE
  (
   EVT_CFG_ITEM.EVENT_ID  IN  ( '1081'  )
   AND
   EVT_REG_ITEM.CANCEL_REASON  Is Null  
     )
GROUP BY
 EVT_CFG_ITEM.DESCRIPTION, 
   cast((CASE
                 WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-09-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 1'
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-16-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 2'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-23-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 3'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-30-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 4'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-07-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 5'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-14-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 6'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-21-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 7'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-28-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 8'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('01-04-2018 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 9'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('01-11-2018 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 10'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('01-18-2018 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 11'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('01-25-2018 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 12'
                    ELSE 'NONE'
               END) as VARCHAR(100)) 

Open in new window


but the client wants a running total - so 10 in week 1 and then 13 in week 2 because 3 more people registered and then 26 in week 3 before 13 more people registered.    Originally I had the start and end of the weeks but that didn't do the running totals.   So I changed it to 11/3/17 which is the start of the report but that didn't do anything to the output - but with the case statement that makes sense.     I am limited because I need a three column output (this is in a report writer solution) - event name, number registered and the week name.   Otherwise I would just do subqueries or something else like that.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Running totals is pretty ease with the window clause on the aggregate functions like SUM, COUNT, etc...

Here is an example:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1793764100346222947

I'm not sure I understand the query you posted well enough to try and guess at the SQL for a copy/paste solution.

I also don't understand the need for the hard-coded values.  Generating the 13 weeks as columns should be a simple PIVOT without a lot of CASE and hard coding.

To get you a copy and paste solution, we would need raw sample data and expected results.  Then we can create a test case and post working SQL.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial