Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
SELECT yourdate, CASE WHEN n = 1 THEN bbls ELSE special END bbls, rate
FROM (SELECT *
FROM yourtable
MODEL
DIMENSION BY(ROW_NUMBER() OVER (ORDER BY yourdate) rn)
MEASURES(yourdate, bbls, rate, bbls running_total, TO_NUMBER(NULL) special)
RULES
(running_total [rn > 1] =
CASE
WHEN (running_total[CV() - 2] + bbls[CV() - 1]) > 100
THEN
bbls[CV()]
WHEN (running_total[CV() - 1] + bbls[CV()]) > 100
THEN
(running_total[CV() - 1] + bbls[CV()]) - 100
ELSE
(running_total[CV() - 1] + bbls[CV()])
END,
special [rn > 1] =
CASE
WHEN (running_total[CV() - 1] + bbls[CV()]) > 100
THEN
(running_total[CV() - 1] + bbls[CV()]) - 100
ELSE
NULL
END)),
(SELECT 1 n FROM DUAL
UNION ALL
SELECT 2 FROM DUAL)
WHERE n = 1 OR (n = 2 AND special IS NOT NULL)
ORDER BY yourdate, n
WITH yourtable
AS (SELECT DATE '2014-03-01' yourdate, 10 bbls, 100 rate FROM DUAL
UNION ALL
SELECT DATE '2014-03-02', 20, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-04', 50, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-05', 30, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-06', 20, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-07', 50, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-08', 40, 100 FROM DUAL)
WITH incentives3
AS (SELECT DATE '2014-03-01' date1, 10 bbls, 100 rate FROM DUAL
UNION ALL
SELECT DATE '2014-03-02', 20, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-04', 50, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-05', 30, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-06', 20, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-07', 50, 100 FROM DUAL
UNION ALL
SELECT DATE '2014-03-08', 40, 100 FROM DUAL)
SELECT date1, CASE WHEN n = 1 THEN bbls ELSE special END bbls, rate
FROM (SELECT *
FROM incentives3
MODEL
DIMENSION BY(ROW_NUMBER() OVER (ORDER BY date1) rn)
MEASURES(date1, bbls, rate, bbls running_total, TO_NUMBER(NULL) special)
RULES
(running_total [rn > 1] =
CASE
WHEN (running_total[CV() - 2] + bbls[CV() - 1]) > 100
THEN
bbls[CV()]
WHEN (running_total[CV() - 1] + bbls[CV()]) > 100
THEN
(running_total[CV() - 1] + bbls[CV()]) - 100
ELSE
(running_total[CV() - 1] + bbls[CV()])
END,
special [rn > 1] =
CASE
WHEN (running_total[CV() - 1] + bbls[CV()]) > 100
THEN
(running_total[CV() - 1] + bbls[CV()]) - 100
ELSE
NULL
END)),
(SELECT 1 n FROM DUAL
UNION ALL
SELECT 2 FROM DUAL)
WHERE n = 1 OR (n = 2 AND special IS NOT NULL)
ORDER BY date1, n
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.