0

We are migrating the data from SQL Server to Teradata and I am unable to replicate the same code from SQL Server to Teradata.

Description: I want to generate report between the dates received from the Rstart and Rend, which is basically 6 month duration (1. jan to jun & 2. jul to dec ). For this, I need to store the dates in two variables (Rstart,Rend) and inorder to fetch records between those two dates

SQL Server code

DECLARE @REnd Date;
DECLARE @RStart Date;

SET @REnd = (SELECT MAX(CalendarDate) AS @REnd 
             FROM Table1 
             WHERE CalendarDate = MonthEndDate
               AND Monthofyear IN ('June', 'December')
               AND CalendarDate < CURRENT_DATE());

SET @RStart = (SELECT MAX(CalendarDate) AS @RStart
               FROM Table1 
               WHERE CalendarDate = (CalendarDate - Extract(Day From CalendarDate) + 1)
                 AND Monthofyear IN ('January', 'July')
                 AND CalendarDate < @REnd);

SELECT * 
FROM Table2 
WHERE ReviewDate BETWEEN @REnd AND @RStart;

This is what I have tried in Teradata:

Create Procedure Demo()

Begin
Declare REnd Date;
Declare RStart Date;

Set REnd = (select max(CalendarDate) as REnd 
            from Table1 
            where CalendarDate = MonthEndDate
            and  Monthofyear in ('June', 'December')
            and CalendarDate < Current_date()
           );

Set RStart = (select max(CalendarDate) as RStart
            from Table1 
            where CalendarDate = (CalendarDate - Extract(Day From CalendarDate)+1)
            and  Monthofyear in ('January', 'July')
            and CalendarDate < REnd
           );

Call dbc.sysexecsql(('select * from table 2 where reviewdate between' ||REnd|| 'and' ||RStart|| ');');
End;
Sufiyan Ahmed Shaikh
  • 1
  • 1
  • If you're failing to migrate code from one dialect to another, you will find it worth while to explain what the code you are attempting to translate does. Otherwise you are limiting yourself to only those that are fluent in *both* dialects; which can be quite a small part of the communities of the 2, dialects. If you explain the problem, then those that are fluent in Teradata, but not T-SQL, can also contribute. – Thom A Feb 16 '22 at 12:53
  • i want to generate report between the dates received from the Rstart and Rend, which is basically 6 month duration (1. jan to jun & 2. jul to dec ). for this i need to stored the dates in two local variable and then run a select statement to fetch the record from the table. – Sufiyan Ahmed Shaikh Feb 16 '22 at 12:58
  • `Extract(Day From CalendarDate)` isn't valid T-SQL. – Panagiotis Kanavos Feb 16 '22 at 13:15
  • Explain what problem you are having. Does your code generate an **error**? Does it not produce the correct resultset? What values do your local variables contain? No one can read your mind or see your screen. In short, "I am unable to replicate ..." is not a meaningful statement. – SMor Feb 16 '22 at 13:42
  • Why do you even use dynamic sql in the TeraData version? While it doesn't seem to be a problem to use the variables in the other queries? – LukStorms Feb 16 '22 at 14:35
  • HI Luke I have tried doing it with out dynamic sql but still getting error and while using it it gives error like ';' is missing and does not execute. – Sufiyan Ahmed Shaikh Feb 16 '22 at 14:45
  • An SP is way to much for a simple query like this (and the syntax for returning a result set is different in Teradata, based on Standard SQL). The logic is confusing, but it's just a strange way to get the current half-year? – dnoeth Feb 16 '22 at 14:54

1 Answers1

0

Assuming Table1 is a calendar table and you just want to return the previous half-year:

SELECT * 
FROM Table2              -- first day of Jan/Jul
WHERE ReviewDate BETWEEN Trunc(Add_Months(Current_Date,  -6 -(Extract(MONTH From Current_Date) -1) MOD 6), 'mon')
                      -- end of Jun/Dec
                  AND Last_Day(Add_Months(Current_Date, -1 -(Extract(MONTH From Current_Date) -1) MOD 6))
dnoeth
  • 59,503
  • 4
  • 39
  • 56