Need SQL query to list all packages promoted to "Prod" in the last 30 days

Article Id: 15989
Status: Published
Updated On: 14-02-2018 07:51
Legacy Id: TEC1185645
Products:
CA Harvest Software Change Manager - OpenMake Meister
Issue/Introduction:



What would be the best way to query for all packages promoted to the "Prod" state in the last 30 days?

Environment:

Release: SCMNCR99000-12.5-Harvest-Software Change Manager-Named User-Competitive Replacem
Component:

Resolution:

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