Display max date of whole dataset as new column using In-DB

[akasubi]

I'm connected to a table in SQL server that has dates as one of the columns. I would like to create a new column that displays the max date of the whole dataset. Therefore, the data for this column will be the same for all rows.

In SQL Server you'd do this by writing a nested query as a column in the select statement.
e.g. select id, (select max(date) from table) from table

How can I do this in Alteryx using the in-db tools. Due to the way my workflow works, I can't just write is in the connect in-db tool.

Thanks

afk

[JosephSerpis]

Hi @akasubi you could use the Summarize In-DB Tool and use the max function to create a column.

[Ladarthure · Accepted solution]

Hi, 

what I would do is to have one summarize with the max date, then create a "fake join field" with value "a" and join it with the main datastream in whihc you also create a field with "a" value (both with formula tools)

[akasubi]

Hi @JosephSerpis 

If i use the summarize tool and do max(date), I'm only left with one column. How would I add this to the original dataset?

[JosephSerpis]

Hi @akasubi as mentioned by @Ladarthure create a field you can then join back onto your data. So that you have maxdate essentially appended to all rows of your data.

[Ladarthure]

@akasubi here is a screen shot of what you could do!

[akasubi]

Thanks @Ladarthure 

This has done the job!

[ThomasT]

@Ladarthure long time since this post. Maybe you can help me on a similar issue. 

I have a database table with a range of dates [date_1], out of those dates I want to find the max_date (easy with summarize) --> [date_1_max]. Now next steps is to only download rows from another database table with another date range ie [date_2] where the dates are 'newer'.

With your solution displayed, is the easiest way IN-DB to append the max date as a new column (fake field) and then use an IN-DB filter to check if [date_1_max] > [date_2]?

Thanks, 

Thomas

[Ladarthure]

Hi @ThomasT,

it depends on your databases, but there could be multiple schemas around this.

Here are 2 "blueprint" to do it.

The first one is using the full in db route where you only use a join key you make for this case (here 1), and then filter
The second one is using dynamic input in db.