- I'm trying to write a query that needs to increment the dates by -1 week for -12 weeks.
- I have the SQL that works (see code below), however if I do UNION ALL (I know not good idea) it takes too long to run.
- So I'm trying to write a PL/SQL that loops through and increments the dates. Below is my example with the SQL that works independently but not in this loop.
- Below is my attempt, but this isn't quite right as it generates an error. The DBMS_OUT.PUT_LINE works great except it's just an example and not what I need. Also it needs to start with current_date-0 instead of current_date-7
- I hope someone can point me in the right direction
Example Weeks:
Example expected output (should be 12 weeks):
DECLARE
l_week_end DATE := NEXT_DAY(TRUNC(TO_DATE(current_date), 'IW'), 'SUNDAY');
l_interval NUMBER := 7; -- Days interval
l_num_intervals NUMBER := 12; -- How many intervals to loop through
BEGIN
FOR i IN 1 .. l_num_intervals LOOP
-- DBMS_OUTPUT.PUT_LINE('Date minus ' || (i * l_interval) || ' days: ' || TO_CHAR(l_week_end - (i * l_interval), 'YYYY-MM-DD'));
SELECT DISTINCT
'WIP' as metric_status,
to_char(l_week_end - (i*l_interval), 'MM/DD/YYYY') as week_end,
count(distinct parent_id) as count_status
FROM jobpiece_metric_view v
WHERE v.max_metric_status = '1'
AND v.metric_status not in ('C', 'R', 'N')
AND started_date <= current_date-(i*l_interval)
AND completed_date is null or completed_date >= current_date-(i*l_interval);
END LOOP;
END;