0

I need to have this query run 12 times (previous 12 months) and append the results to a table. I am not very good with looping, looking for input. I am just not sure where to put my counter variables or any other looping statements. I think I may need two variables to loop because of the Previous Month First Day and Last day variables.

SET @PM_FD = last_day(curdate() - interval 2 month) + interval 1 day;
SET @PM_LD = last_day(curdate() - interval 1 month);

insert into sandbox.metrics_history

SELECT
'CHI' as Company
,count(*) as Result
,'SSRM10' as Metric_ID
,'PONoReqLine' as Metric_Name
, MONTHNAME(@PM_FD) as Month, year(@PM_FD) as Year
   
FROM
    poline pol
        INNER JOIN
    purchorder po ON pol.company = po.company
        AND pol.po_number = po.po_number
        AND pol.po_release = po.po_release
        AND pol.po_code = po.po_code
        LEFT JOIN
    polinesrc src ON pol.company = src.company
        AND pol.po_number = src.po_number
        AND pol.po_release = src.po_release
        AND pol.line_nbr = src.line_nbr
        AND pol.po_code = src.po_code
        LEFT JOIN
    buyer byr ON pol.buyer_code = byr.buyer_code
WHERE
    pol.buyer_code != 'POC'
        AND src.company IS NULL
        AND po.po_date >= @PM_FD
        AND po.po_date <= @PM_LD
ORDER BY pol.company , pol.po_number , pol.line_nbr
Jill
  • 1
  • 2
    But why would you loop? Just insert for the whole year once. – JNevill Jan 24 '22 at 16:30
  • https://stackoverflow.com/questions/2157282/generate-days-from-date-range has a bunch of options for generating a list of dates, then you'd join on that. Or you can loop in a stored procedure: https://stackoverflow.com/questions/41888407/insert-every-date-of-the-year-mysql – Schwern Jan 24 '22 at 17:55

0 Answers0