Most Recent Database Data

jeffjose33

Hi, 

I'm querying a database for workflow data (accounting data) that can be updated as often as monthly if the specific account/location is still active. The database stores all of the data (aka stores the same account data for May, June, July etc) and I want to be able to to take the most recent data point for each account/location, whether it was this month or 3 years ago. I currently have a sort and sample combination to get me this data, but I am wondering if there is a better way to do this?

If I want to make this an analytic app in the future so that users can check account information, would it be better to use a workflow constant for a date and use that as a reference for the most recent data? I ask bc my workplace has a workflow from a Big 4 consultant and it utilizes workflow constants, I'm just not sure if that is the best way to move forward.

Any guidance is appreciated!!

fmvizcaino

Hey @jeffjose33 ,

I've seeing a few posts talking about Julian date to standard date in sql, but not sure if that would work since I don't have a lot of SQL experience.

What I would do, since you are basically filtering the most recent date, I would do the sort and the sample in-db and use the data stream out tool to mix that with your local formula tool. 

Something like this:

One problem you may have there is related to the sample tool in-db, since it doesn't have the option to group by, so you would need to develop that directly in the input in-db tool query or doing something like this to get only the most recent date and then use a inner join.

Best,

Fernando Vizcaino