I have been having the hardest time converting this T-SQL to TeraData SQL. Can someone assist me with this. I would really appriecate it. I am not understanding the convertion of dates with teradata. I am super confused.
A fiscal Month example: Starts: 2020-22-01 - Ends: 2020-21-01 This query delivers a data result within the fiscal month of with they started there position. Example: 8/21/2019
,CASE WHEN DAY(POSITION_START_DT) BETWEEN 22 AND 31
THEN CAST(
CONCAT(
CAST(YEAR(POSITION_START_DT)AS VARCHAR(4)),
CASE WHEN LEN(MONTH(POSITION_START_DT)) = 1 THEN '-0' ELSE '-'END,
CAST(MONTH(POSITION_START_DT)AS VARCHAR(2)),
'-21')
AS date)
WHEN DAY(POSITION_START_DT) BETWEEN 1 AND 6
THEN CAST(
CONCAT(
CAST(YEAR(DATEADD(MONTH,-1,POSITION_START_DT))AS VARCHAR(4)),
CASE WHEN LEN(MONTH(DATEADD(MONTH,-1,POSITION_START_DT))) = 1 THEN '-0' ELSE '-'END,
CAST(MONTH(DATEADD(MONTH,-1,POSITION_START_DT))AS VARCHAR(2)),
'-21')
AS date)
ELSE CAST(
CONCAT(
CAST(YEAR(DATEADD(MONTH,0,POSITION_START_DT))AS VARCHAR(4)),
CASE WHEN LEN(MONTH(DATEADD(MONTH,0,POSITION_START_DT))) = 1 THEN '-0' ELSE '-'END,
CAST(MONTH(DATEADD(MONTH,0,POSITION_START_DT))AS VARCHAR(2)),
'-21')
AS date) END AS FISCAL_START_MONTH_INSALES
Fiscal Tenure Bucket uses "Fiscal_Start_Month_insales field and groups the employee in a bucket. Buckets: - 0_3_MONTHS - 4_6_MONTHS - 7_12_MONTHS - 13_24_MONTHS - 25_PLUS_MONTHS - ELSE Unknown
,CASE WHEN DATEDIFF(Day,FiscalStartMonthInSales,CURRENT_DATE)/30 >= 25 THEN '25_PLUS_MONTHS'
WHEN DATEDIFF(Day,FiscalStartMonthInSales,CURRENT_DATE)/30 BETWEEN 13 AND 24 THEN '13_24_MONTHS'
WHEN DATEDIFF(Day,FiscalStartMonthInSales,CURRENT_DATE)/30 BETWEEN 7 AND 12 THEN '7_12_MONTHS'
WHEN DATEDIFF(Day,FiscalStartMonthInSales,CURRENT_DATE)/30 BETWEEN 4 AND 6 THEN '4_6_MONTHS'
WHEN DATEDIFF(Day,FiscalStartMonthInSales,CURRENT_DATE)/30 BETWEEN 0 AND 3 THEN '0_3_MONTHS'
ELSE 'Unknown' END as PositionTenureBucket
Cheers~ Arron
Simplifying Fred's first part to:
Cast(To_Char(CASE WHEN Extract(DAY From POSITION_START_DT) BETWEEN 1 AND 6
THEN Add_Months(POSITION_START_DT,-1)
ELSE POSITION_START_DT
END, 'yyyy-mm') || '-21' AS DATE)
And simplifying the logic to subtract 6 days, get the 1st of the month and add 20 days:
Trunc(POSITION_START_DT - 6, 'mon') + 20