BDNA Data Platform 5.4 July 2017 (SQL Server) CAT_SW_REL_LIFECYCLE_CALC - Software release lifecylce(calculated)
|
View Definition:
CREATE VIEW CAT_SW_REL_LIFECYCLE_CALC AS
SELECT CAT_SW_REL_LIFECYCLE.CAT_SW_REL_LIFECYCLE_ID,CAT_SW_REL_LIFECYCLE.CAT_SW_RELEASE_ID,
GENERAL_AVAILABILITY as GA,GA_EXCEPTION,GA_RANGE_START,GA_RANGE_END,
COALESCE(END_OF_LIFE,OBSOLETE) as EOL,END_OF_LIFE_EXCEPTION,END_OF_LIFE_RANGE_START,END_OF_LIFE_RANGE_END,END_OF_LIFE_SUPPORT_LEVEL,
OBSOLETE,OBSOLETE_EXCEPTION,OBSOLETE_RANGE_START,OBSOLETE_RANGE_END,OBSOLETE_SUPPORT_LEVEL,
COALESCE(END_OF_LIFE,OBSOLETE) as CALC_EOL,
COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG) as DISCONDESUPP,
CAT_SW_VERSION.VERSION_ORDER,
ORDER1.MAX_VERSION_ORDER,
ORDER1.MAX_VERSION_ORDER-CAT_SW_VERSION.VERSION_ORDER as CALC_DIFF_ORDER,
CASE
WHEN COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG)='yes' THEN 'Technopedia flagged this as discontinued/de-supported'
WHEN DATEDIFF(day,GETDATE(),COALESCE(END_OF_LIFE,OBSOLETE)) > 0 THEN 'Technopedia EOL date not met yet'
ELSE 'Technopedia EOL Date'
END as REASON_IS_SUPPORTED,
CASE
WHEN COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG)='yes' THEN 'no'
WHEN DATEDIFF(day,GETDATE(),COALESCE(END_OF_LIFE,OBSOLETE)) > 0 THEN 'yes'
ELSE 'no' END as IS_SUPPORTED,
CAT_SW_REL_LIFECYCLE.CREATE_DATE,
CAT_SW_REL_LIFECYCLE.LAST_MODIFIED_DATE
FROM CAT_SW_REL_LIFECYCLE
INNER JOIN CAT_SW_RELEASE ON CAT_SW_RELEASE.CAT_SW_RELEASE_ID=CAT_SW_REL_LIFECYCLE.CAT_SW_RELEASE_ID
INNER JOIN CAT_SW_VERSION ON CAT_SW_VERSION.CAT_SW_VERSION_ID=CAT_SW_RELEASE.CAT_SW_VERSION_ID
INNER JOIN (
SELECT CAT_SW_PRODUCT_ID, MAX(VERSION_ORDER) as MAX_VERSION_ORDER
FROM CAT_SW_VERSION GROUP BY CAT_SW_PRODUCT_ID
)ORDER1 ON ORDER1.CAT_SW_PRODUCT_ID=CAT_SW_VERSION.CAT_SW_PRODUCT_ID
WHERE (CAT_SW_REL_LIFECYCLE.END_OF_LIFE IS NOT NULL OR CAT_SW_REL_LIFECYCLE.OBSOLETE IS NOT NULL)
UNION ALL
SELECT CAT_SW_REL_LIFECYCLE.CAT_SW_REL_LIFECYCLE_ID,CAT_SW_REL_LIFECYCLE.CAT_SW_RELEASE_ID,GENERAL_AVAILABILITY AS GA,GA_EXCEPTION,GA_RANGE_START,GA_RANGE_END,COALESCE(END_OF_LIFE,OBSOLETE) AS EOL,
END_OF_LIFE_EXCEPTION,END_OF_LIFE_RANGE_START,END_OF_LIFE_RANGE_END,END_OF_LIFE_SUPPORT_LEVEL,OBSOLETE,OBSOLETE_EXCEPTION,OBSOLETE_RANGE_START,
OBSOLETE_RANGE_END,OBSOLETE_SUPPORT_LEVEL,
CASE WHEN 47 = 9999 THEN NULL
WHEN 47 != 9999 THEN DATEADD(month,47,GENERAL_AVAILABILITY)
END as CALC_EOL,
COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG) as DISCONDESUPP,
CAT_SW_VERSION.VERSION_ORDER,
ORDER1.MAX_VERSION_ORDER,
ORDER1.MAX_VERSION_ORDER-CAT_SW_VERSION.VERSION_ORDER as CALC_DIFF_ORDER,
CASE
WHEN COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG)='yes' THEN 'Technopedia flagged this as discontinued/de-supported'
WHEN ( 47 != 9999 AND DATEDIFF(day,GETDATE(),DATEADD(month,47,GENERAL_AVAILABILITY)) > 0
Possibly Referenced Tables/Views: