Need sql to list all recent package promotions

Products

• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

I need to create a query on Harvest SCM tables that allow me to extract all the promotions made in test testing and production of the different projects I manage

Environment

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

Resolution

If your database is Oracle, try this:

SELECT HARPKGHISTORY.ENVIRONMENTNAME,
HARPKGHISTORY.STATENAME,
HARPACKAGE.PACKAGENAME,
HARPKGHISTORY.ACTION,
TO_CHAR(HARPKGHISTORY.EXECDTIME, 'mm-dd-yyyy hh:mi:ss') EXECDTIME,
HARALLUSERS.USERNAME
FROM HARPKGHISTORY
INNER JOIN HARPACKAGE
ON HARPKGHISTORY.PACKAGEOBJID = HARPACKAGE.PACKAGEOBJID
INNER JOIN HARALLUSERS
ON HARPKGHISTORY.USROBJID = HARALLUSERS.USROBJID
WHERE ACTION = 'Promote'
AND ENVIRONMENTNAME IN ('SampleProject', 'TestConcurrentMerge') /* <--- List the names of your projects here */
AND STATENAME IN ('Development', 'Test') /* <--- List the names of your states here */
AND EXECDTIME BETWEEN TO_DATE('04-05-2017', 'mm-dd-yyyy') /* <--- List the beginning date of your date range here */
AND TO_DATE('04-20-2017', 'mm-dd-yyyy') /* <--- List the ending date of your date range here */

If your database is SQL Server, try this:
SELECT HARPKGHISTORY.ENVIRONMENTNAME,
HARPKGHISTORY.STATENAME,
HARPACKAGE.PACKAGENAME,
HARPKGHISTORY.ACTION,
HARPKGHISTORY.EXECDTIME,
HARALLUSERS.USERNAME
FROM HARPKGHISTORY
INNER JOIN HARPACKAGE
ON HARPKGHISTORY.PACKAGEOBJID = HARPACKAGE.PACKAGEOBJID
INNER JOIN HARALLUSERS
ON HARPKGHISTORY.USROBJID = HARALLUSERS.USROBJID
WHERE ACTION = 'Promote'
AND ENVIRONMENTNAME IN ('SampleProject', 'TestConcurrentMerge') /* <--- List projects here */
AND STATENAME IN ('Development', 'Test') /* <--- List states here */
AND EXECDTIME BETWEEN '04-05-2017' /* <--- List beginning of date range here */
AND '04-22-2017' /* <--- List ending of date range here */

Depending on your settings in Windows and in Oracle or SQL Server you may have to adjust the formats of your dates accordingly.