Tenure Calculation with TeraData SQL

Question

List item

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

Sample data, desired results, and an explanation of the intended logic would improve your chances of getting some help. — Eric Brandt, Feb 24 '20 at 19:11
It looks like you edited the question, but there is still no sample data and desired results. It would be very helpful since it's not clear from the code alone what the exact calculation is. That tsql is very complex and between the parsing, casting, and math.. I'm lost. — JNevill, Feb 24 '20 at 19:37
Nor is it clear what your problem actually is. Try and simplify if you can. — Andrew, Feb 24 '20 at 19:40
Here's my best guess: *"I want to get the number of months between the employee's start date and today where months are delineated by our fiscal period which starts on the 21st of each month and ends on the 20th of the next month"*. I believe something like: `SELECT COUNT(PERIOD(DATE '2018-01-01', CURRENT_DATE) P_INTERSECT fiscpers.fiscal_period) FROM (SELECT PERIOD(calendar_date, calendar_date + INTERVAL '1' MONTH) as fiscal_period FROM Sys_Calendar."CALENDAR" WHERE day_of_month = 21) AS fiscpers` would get you in the ballpark if that's the case. (Where `2018-01-01` is the employee start) — JNevill, Feb 24 '20 at 19:45

Fred · Answer 1 · 2020-02-25T16:44:56.410

Just converting functions (and combining the two cases that seem to give identical results), it seems the first part could be

CASE WHEN EXTRACT(DAY FROM POSITION_START_DT) BETWEEN 1 AND 6
     THEN CAST(CAST(CAST(ADD_MONTHS(POSITION_START_DT,-1) AS FORMAT 'YYYY-MM') AS VARCHAR(7))||'-21' AS DATE FORMAT 'YYYY-MM-DD')
     ELSE CAST(CAST(CAST(POSITION_START_DT AS FORMAT 'YYYY-MM') AS VARCHAR(7))||'-21' AS DATE FORMAT 'YYYY-MM-DD')
     END AS FISCAL_START_MONTH_INSALES

And perhaps the second part is just

 ,CASE WHEN (CURRENT_DATE-FiscalStartMonthInSales)/30  >= 25 THEN '25_PLUS_MONTHS'
       WHEN (CURRENT_DATE-FiscalStartMonthInSales)/30   BETWEEN 13 AND 24 THEN '13_24_MONTHS'
       WHEN (CURRENT_DATE-FiscalStartMonthInSales)/30   BETWEEN 7 AND 12 THEN '7_12_MONTHS'
       WHEN (CURRENT_DATE-FiscalStartMonthInSales)/30   BETWEEN 4 AND 6 THEN '4_6_MONTHS'
       WHEN (CURRENT_DATE-FiscalStartMonthInSales)/30   BETWEEN 0 AND 3 THEN '0_3_MONTHS'
       ELSE 'Unknown' END as PositionTenureBucket

I am getting a Syntax error: expected something between ')' and '"||"' for the top syntax conversion. Any ideas? — CodeOfArms, Feb 24 '20 at 21:50
Misplace parentheses. I'll edit the answer just in principle. But dnoeth's is simpler. — Fred, Feb 25 '20 at 16:43

score 0 · Accepted Answer · answered Feb 24 '20 at 21:46

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

Tenure Calculation with TeraData SQL

2 Answers2