troubleshooting Question

SQL Question for Running Totals

Avatar of digitalwise
digitalwise asked on
Oracle DatabaseSQL
1 Comment1 Solution112 ViewsLast Modified:
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)) 

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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros