Adding Year to Date Component to a SQL Query in Teradata
In this article, we will explore how to add a year-to-date (YTD) component to an existing SQL query written for Teradata. The process involves modifying the query to include calculations that take into account the current date and the desired year.
Understanding Teradata’s Date Handling
Before diving into the solution, it’s essential to understand how Teradata handles dates. In Teradata, dates are stored internally as integers, with the year represented as 0 for the year 1900 and subsequent years increasing by 1 each time. This means that the date format is not in the traditional YYYY-MM-DD format but rather in YYYYMMDD.
For example, January 16th, 2020 would be represented as 20200416 (2020-04-16).
Modifying the Query to Include YTD
To add a year-to-date component to our query, we need to divide the current date by 10000 to get the year. Here’s an example of how this can be done:
SELECT
COST__DESC,
ST.AD_SRV_MTN AS MONTH_OF_AD,
COUNT(DISTINCT CM.CM_NBR) AS CMS,
MEM_MO AS MBR_MTH,
CMS/MBR_MTH*1000 AS CMS_PER_1000,
SUM(CASE WHEN CURRENT_DATE / 10000 = <YourDateField>/10000 THEN <YourKPI> ELSE 0 END) AS YourKPI_YTD
FROM XTR.FT_CM AS CM
JOIN XTR.FT_ST AS ST ON ST.CM_NBR = CM.CM_NBR
JOIN XTR.DIM_MED_CST AS MC ON ST.CST_CK = MCC.CST_CK
JOIN XTR.DIM_AF AS AFF ON ST.PRO_CK = AFF.AFF_CK
JOIN XTR.DIM_ADJDCTN_STAT AS A_S ON ST.ADJDCTN_STAT_CK = A_S.ADJDCTN_STAT_CK
JOIN XTR.DIM_ADJ_OT AS OT ON ST.ADJ_CK = OT.ADJ_CK
LEFT JOIN
(SELECT
CALENDAR_YEAR_MONTH as YEAR_MO,
SUM(MBR.COUNT_NBR) as MEM_MO
FROM XTR.FT_MBR_MONTHS MBR
INNER JOIN DIM_MBR_C ON MBR.DB_MBR_CK = DIM_MBR_C.DB_MBR_CK
AND MBR.DATE_CK BETWEEN DIM_MBR_C.DB_eff_date_ck
AND DIM_MBR_C.DB_END_DATE_CK
INNER JOIN DIM_DATE DT ON ELI_DATE_CK = DT.DATE_CK
WHERE MBR.F_C_CK = 500058321 AND YEAR_MO >= 201701
GROUP BY 1) MM ON ST.AD_SRV_MTN = MM.YEAR_MO
WHERE ST.F_C_CK = 500058321 AND ST.ST_START_DATE_CK >= 20200101
AND ST.AD_SRV_MTN > 201912 AND MC.MED_DESC IN ('Er', 'IP')
AND ST.AD_SRV_MTN < ((EXTRACT (YEAR FROM CURRENT_DATE) *100) +
EXTRACT (MONTH FROM CURRENT_DATE))
GROUP BY 1,2,4
ORDER BY 1,2
In the modified query above, YourDateField represents the date field you want to use for your YTD calculation, and YourKPI represents the KPI you want to calculate.
Counting with a CASE Statement
Another way to implement counting in Teradata is by using a CASE statement. Here’s an example of how this can be done:
SELECT
COST__DESC,
ST.AD_SRV_MTN AS MONTH_OF_AD,
COUNT(DISTINCT CM.CM_NBR) AS CMS,
MEM_MO AS MBR_MTH,
CMS/MBR_MTH*1000 AS CMS_PER_1000,
SUM(CASE WHEN CURRENT_DATE / 10000 = <YourDateField>/10000 THEN 1 ELSE 0 END) AS COUNT_YTD
FROM XTR.FT_CM AS CM
JOIN XTR.FT_ST AS ST ON ST.CM_NBR = CM.CM_NBR
JOIN XTR.DIM_MED_CST AS MC ON ST.CST_CK = MCC.CST_CK
JOIN XTR.DIM_AF AS AFF ON ST.PRO_CK = AFF.AFF_CK
JOIN XTR.DIM_ADJDCTN_STAT AS A_S ON ST.ADJDCTN_STAT_CK = A_S.ADJDCTN_STAT_CK
JOIN XTR.DIM_ADJ_OT AS OT ON ST.ADJ_CK = OT.ADJ_CK
LEFT JOIN
(SELECT
CALENDAR_YEAR_MONTH as YEAR_MO,
SUM(MBR.COUNT_NBR) as MEM_MO
FROM XTR.FT_MBR_MONTHS MBR
INNER JOIN DIM_MBR_C ON MBR.DB_MBR_CK = DIM_MBR_C.DB_MBR_CK
AND MBR.DATE_CK BETWEEN DIM_MBR_C.DB_eff_date_ck
AND DIM_MBR_C.DB_END_DATE_CK
INNER JOIN DIM_DATE DT ON ELI_DATE_CK = DT.DATE_CK
WHERE MBR.F_C_CK = 500058321 AND YEAR_MO >= 201701
GROUP BY 1) MM ON ST.AD_SRV_MTN = MM.YEAR_MO
WHERE ST.F_C_CK = 500058321 AND ST.ST_START_DATE_CK >= 20200101
AND ST.AD_SRV_MTN > 201912 AND MC.MED_DESC IN ('Er', 'IP')
AND ST.AD_srv_MN_T < ((EXTRACT (YEAR FROM CURRENT_DATE) *100) +
EXTRACT (MONTH FROM CURRENT_DATE))
GROUP BY 1,2,4
ORDER BY 1,2
In the modified query above, COUNT_YTD represents the number of records for your YTD calculation.
Conclusion
Adding a year-to-date component to an existing SQL query in Teradata requires modifications to calculate the current date and apply it to your desired KPI. By dividing the current date by 10000, you can get the year and use this value to perform calculations that take into account the current date.
Last modified on 2023-11-23