Scripting

I need to create a report on patch compliance and am new to SQL. I would like to limit this report to patches created from 21 days old to 60 days old which is where I am having the main problem. For this report I want to know what patches in THIS MONTHS patching run have failed. I don't want to deal with every failed patch throughout history, I have another report for that. Here is what I have so far:

SELECT

M.NAME AS ComputerName,

OS_NAME,

M.USER_LOGGED AS USER_LOGGED,

P.PUBLISHER,

P.TITLE AS DISPLAY_NAME,

P.SEVERITY,

P.CREATION_DATE,

MS.DETECT_STATUS

FROM

PATCH_MACHINE_STATUS MS

JOIN KBSYS.PATCH P ON P.ID = MS.PATCH_ID

JOIN MACHINE M ON M.ID = MS.MACHINE_ID

JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)

JOIN LABEL L ON (ML.LABEL_ID = L.ID)

WHERE

MS.DETECT_STATUS != 'PATCHED' AND

P.IS_SUPERCEDED = '0' AND

P.SEVERITY != 'low' AND

P.Creation_date BETWEEN 20210320 and 20210520 AND

L.NAME = 'Server Label Name'

ORDER BY M.NAME

This succeeds in limiting the report to the specified date range. The problem is I don't want to manually change those dates on 5 reports every month. After teaching myself some MySQL I came up with this replacement line for highlighted:

P.Creation_date BETWEEN (CURRENT_DATE(), INTERVAL 21 DAY) AND (CURRENT_DATE(), INTERVAL 60 DAY)

But this generates a error because it does not like INTERVAL in a where statement. If I was writing this in PowerShell I would just create do it like this :

Top of the script:

$TodaysDate = (Get-Date).ToString("yyyyMMdd")

$A = ($TodaysDate - 21)

$B = ($TodaysDate - 60)

and then in the Where statement :

P.Creation_date BETWEEN $A AND $B

This should really be a canned report, or at a minimum much easier to generate.