ETL SQL Statement

Reply
Highlighted
Yellow Belt

ETL SQL Statement

Hi,

 

I'm not familiar with SQL and wondering if someone can help with a SQL statement in an ETL that would solve the below(hope it makes sense)...

 

COLUMN1(txt)COLUMN2(date)COLUMN3(date)
datapoint1oct 18 2020

if 'column1' = 'datapoint1'

 then 'column2'  minus 1 mth from the datafield in Col2'

datapoint2oct 18 2020

if 'column1' = 'datapoint2'

 then 'column2'  minus 2 mths from the datafield in Col2'

 

result...

COLUMN1(txt)COLUMN2(date)COLUMN3(date)
datapoint1oct 18 2020Sept 2020
datapoint2oct 18 2020August 2020
Highlighted
Black Belt

If you are using MySQL, you could do this:

SELECT Column1, Column2,
CASE WHEN column1 = 'datapoint1' THEN DATE_SUB(column2, INTERVAL 1 MONTH)
WHEN column1 = 'datapoint2' THEN DATE_SUB(column2, INTERVAL 2 MONTH)
END as Column3
FROM MyTable

You can also do this in MagicETL, which might be worth doing if you are not comfortable with SQL. You would use the Filter tile to split your data into two groups: datapoint1 and datapoint2. You would then use the Date Operations tile on both of those and use the subtract from date function in the tile and subtract 1 month for datapoint1 and 2 months for datapoint2. You would then use the Append tile to bring the datasets back together. 

Hope this helps. 




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.