Converting SQL Server Query to Teradata

General
Enthusiast

Converting SQL Server Query to Teradata

It's basically Date Consolidation & getting minimum price from the overlapping dates 

DROP TABLE tmp_mtrl_price

CREATE TABLE tmp_mtrl_price

(

  Frcst_Altr_Id varchar(100),

  Item varchar(100),

  Geo_Cd varchar(10),

  Start DATETIME,

  [End] DATETIME,

  Price DECIMAL(13,2)

)

INSERT INTO tmp_mtrl_price

SELECT '00E0836','921236','PROC', CAST('18-Apr-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 17 UNION ALL

SELECT '00E0836','921236','IJKK', CAST('18-Apr-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 17 UNION ALL

SELECT '00E0836','921236','EMEA', CAST('09-May-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 12 UNION ALL

SELECT '00E0836','921236','ASMO', CAST('17-May-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 15 UNION ALL

SELECT '00E0836','921236','APAC', CAST('17-May-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 16 UNION ALL

SELECT '00E0836','921238','PROC', CAST('18-Apr-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 17 UNION ALL

SELECT '00E0836','921238','IJKK', CAST('20-Apr-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 16 UNION ALL

SELECT '00E0836','921238','EMEA', CAST('09-May-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 12 UNION ALL

SELECT '00E0836','921238','ASMO', CAST('17-May-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 10 UNION ALL

SELECT '00E0837','921232','PROC', CAST('13-July-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 340 UNION ALL

SELECT '00E0837','921232','IJKK', CAST('14-Sep-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 350 UNION ALL

SELECT '00E0836','801462','PROC', CAST('09-Feb-2003' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 205 UNION ALL

SELECT '00E0836','801462','IJKK', CAST('18-Apr-2004' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 175 UNION ALL

SELECT '00E0836','801462','EMEA', CAST('09-Feb-2006' AS DATETIME), CAST('10-Mar-2010' AS DATETIME), 150 UNION ALL

SELECT '00E0836','921237','PROC', CAST('18-Apr-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 17 UNION ALL

SELECT '00E0836','921237','IJKK', CAST('18-Apr-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 17 UNION ALL

SELECT '00E0836','921237','EMEA', CAST('19-May-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 17 UNION ALL

SELECT '00E0836','921237','ASMO', CAST('17-May-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 15 UNION ALL

SELECT '00E0836','921237','APAC', CAST('17-May-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 16 UNION ALL

SELECT '00E0836','921240','PROC', CAST('18-Apr-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 17.5 UNION ALL

SELECT '00E0836','921240','IJKK', CAST('18-Apr-2014' AS DATETIME), CAST('31-May-2015' AS DATETIME), 14.5 UNION ALL

SELECT '00E0836','921240','EMEA', CAST('9-May-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 12.5  UNION ALL

SELECT '00E0836','921242','PROC', CAST('18-Apr-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 17.5 UNION ALL

SELECT '00E0836','921242','IJKK', CAST('20-Apr-2014' AS DATETIME), CAST('30-Apr-2015' AS DATETIME), 14.5 UNION ALL

SELECT '00E0836','921242','EMEA', CAST('9-May-2014' AS DATETIME), CAST('31-Dec-9999' AS DATETIME), 12.5  

;With EndDates AS (

    SELECT [End],Item,price FROM tmp_mtrl_price

    UNION ALL

    SELECT DATEADD(day,-1,Start),Item,price FROm tmp_mtrl_price

) ,

Periods AS (

    SELECT Item,price,MIN(Start) AS Start,

                (SELECT MIN([End]) FROM EndDates e

                 WHERE e.Item = t.Item and

                 e.[End] >= MIN(Start)) AS [End]

    FROM tmp_mtrl_price t

    GROUP BY price,Item

    UNION all

    SELECT p.Item,p.price,DATEADD(day,1,p.[End]),e.[End]

    FROM Periods p

            INNER JOIN EndDates e

             ON 

p.Item = e.Item and

p.price = e.price and

                p.[End] < e.[End]

    WHERE

        NOT EXISTS (SELECT 1 FROM EndDates e2 WHERE

                e2.Item = p.Item and 

e2.price = p.price and 

                e2.[End] > p.[End] and

                e2.[End] < e.[End])

)

SELECT DISTINCT p.Item,p.price,p.start FROM (

SELECT Item,MIN(Price) price,Min(Start) start FROM Periods 

GROUP BY Item,Start )p

INNER JOIN tmp_mtrl_price t

ON p.Item = t.Item AND p.Price = t.Price AND p.start = t.Start 

o/p :

Item         price                     start_date

801462 150.00 2006-02-09 00:00:00.000

801462 175.00 2004-04-18 00:00:00.000

801462 205.00 2003-02-09 00:00:00.000

921232 340.00 2014-07-13 00:00:00.000

921236 12.00 2014-05-09 00:00:00.000

921236 17.00 2014-04-18 00:00:00.000

921237 15.00 2014-05-17 00:00:00.000

921237 17.00 2014-04-18 00:00:00.000

921238 10.00 2014-05-17 00:00:00.000

921238 12.00 2014-05-09 00:00:00.000

921238 16.00 2014-04-20 00:00:00.000

921238 17.00 2014-04-18 00:00:00.000

921240 12.50 2014-05-09 00:00:00.000

921240 14.50 2014-04-18 00:00:00.000

921242 12.50 2014-05-09 00:00:00.000

921242 14.50 2014-04-20 00:00:00.000

921242 17.50 2014-04-18 00:00:00.000

Please help ... it's using recursive CTE to generate the periods ..... can we achieve this as a single view