access max.date from sql table

user095063
user095063 Member
edited March 2023 in SQL DataFlows

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.

Tagged:

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    This should do it:

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

    FROM 'estimated'

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • user095063
    user095063 Member
    Answer ✓

    Phew! Works, thank you very much! :)

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    This should do it:

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

    FROM 'estimated'

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • user095063
    user095063 Member
    Answer ✓

    Phew! Works, thank you very much! :)

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories