asked on
SQL Question for Running Totals
I have the following SQL which really well to pull the sum of the registrations by week
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.
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.
Oracle DatabaseSQL
Last Comment
slightwv (䄆 Netminder)