access max.date from sql table

user095063

Hi. I have two input datasets in SQL Magic Transformation. One table called daily the other one estimated . Both tables have the same columns. I need to fetch the data from estimated table but only for the date that is later than max(`Date`) in table dail

Data looks like this. Table daily

 Date          Name    Revenue
2021-08-01     A        10
2021-08-01     B        20
2021-08-02     A        4
2021-08-02     B        5

Table estimated

 Date          Name    Revenue
2021-08-02     A        4
2021-08-02     B        4
2021-08-03     A        30
2021-08-03     B        35

The desired output is:

 Date          Name    Revenue
2021-08-03     A        30
2021-08-03     B        35

I would normally create a temporarily table where I would store the max Date like `WITH TABLE as (select max.Date from daily` but this functionality is not supported in domo.

I tried union both tables and create a new column last_date where for estimated table portion I assigned everything to NULL and daily portion fetched the max.Date but it didn't return any data.

Thanks for help in advance.

MarkSnodgrass

This should do it:

SELECT 'date', 'Name', 'Revenue'

FROM 'estimated'

WHERE 'date' > (SELECT MAX('date') FROM 'daily')

user095063

Phew! Works, thank you very much! :)

MarkSnodgrass

This should do it:

SELECT 'date', 'Name', 'Revenue'

FROM 'estimated'

WHERE 'date' > (SELECT MAX('date') FROM 'daily')

user095063

Phew! Works, thank you very much! :)

jaeW_at_Onyx

@user095063

if you can't write SQL, i strongly recommend you learn your way around Magic 2.0. The more SQL you write, the fewer people at your org can support you as your queries become more complex.

If it were me, i would

1) Trans_Table = union all my data together with a column for 'Activity Type'

2) Date_DimByType = calculate the Max Date and Min Date Group By Activity Type.

3) JOIN tables together by Activity Type.

use Analyzer to construct my actuals vs. expected using

sum(CASE When Activity type ... ).

This construction avoids filtering data in ETL, forces you to think in dimensional modeling terms, and introduces recycle-able data models that can be extended. https://www.youtube.com/watch?v=PVbOeLSae9o&t=252s