What would be the best way to query for all packages promoted to the "Prod" state in the last 30 days?
If your database is Oracle:
SELECT HARPACKAGE.PACKAGENAME,
HARPKGHISTORY.ENVIRONMENTNAME,
HARPKGHISTORY.STATENAME,
HARPKGHISTORY.EXECDTIME
FROM HARPACKAGE
INNER JOIN HARPKGHISTORY ON HARPACKAGE.PACKAGEOBJID = HARPKGHISTORY.PACKAGEOBJID
WHERE HARPKGHISTORY.STATENAME = 'Prod'
AND HARPKGHISTORY.ACTION = 'Promote'
AND HARPKGHISTORY.EXECDTIME > SYSDATE - 30
If your database is SQL Server:
SELECT HARPACKAGE.PACKAGENAME,
HARPKGHISTORY.ENVIRONMENTNAME,
HARPKGHISTORY.STATENAME,
HARPKGHISTORY.EXECDTIME
FROM HARPACKAGE
INNER JOIN HARPKGHISTORY ON HARPACKAGE.PACKAGEOBJID = HARPKGHISTORY.PACKAGEOBJID
WHERE HARPKGHISTORY.STATENAME = 'Prod'
AND HARPKGHISTORY.ACTION = 'Promote'
AND HARPKGHISTORY.EXECDTIME > CURRENT_TIMESTAMP - 30