How To Fetch Records Using Vertical Columns In Amazon Redshift

I need to fetch records between the date range given in two columns. My Table structure is like: CREATE TABLE shared ( id integer NOT NULL, product v

Solution 1:

Your dates have not the MySQL format, so they have to be converted,to be compared

With the MIN and MAX and your 'n/a, it is better to use NULL in the inner Select and in the outer assign the Value n/a if the value is NULL, ot or else you get n/qa even if there is data.

SELECT*FROM
    (SELECT 
        parent_id,
            product,
            MIN(CASEWHEN key_code ='start_date'THEN key_value ELSE'n/a'END) AS start_date,
            MIN(CASEWHEN key_code ='end_date'THEN key_value ELSE'n/a'END) AS end_date,
            MAX(CASEWHEN key_code ='type'THEN key_value ELSE'n/a'END) AS type,
            MAX(CASEWHEN key_code ='plan'THEN key_value ELSE'n/a'END) AS plan
    FROM
        shared
    GROUPBY parent_id , product
    ORDERBY parent_id) comp
WHERE
    TO_DATE(start_date, 'DD/MM/YYYY') >='2011-01-12'AND TO_DATE(start_date, 'DD/MM/YYYY') <='2011-07-17'AND product ='a'
;

Thsi would give youz

 parent_id  product  start_date  end_date   type        plan
 1          a        1/7/201115/7/2011  Promotion   new

So STR:TO:DATE doesn't exist in AWS but it EXISTS a TO_Date Instead of IF you maust do A CASe when like in your query