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 ,

If you are downloading a large dataset from your accounting system, I would suggest you using in-database tools since it would use your data source performance to run everything and only download the data you actually need.

Related to your date question, the idea of using a workflow constant is if you have a lot of formulas and places using that same date, then you can change all at the same time by changing the workflow constant.

Best,

Fernando Vizcaino

[jeffjose33]

Thanks Fernando. I am having trouble recreating a formula that utilizes date, which is why I wasn't using the in-database tools. 

I'm trying to recreate this formula with the Formula In-DB tool, but I'm not sure exactly how to do it. 

It takes date info from the database that looks like this: 

And transforms it into this format: 

Do you know how I can do this?

[fmvizcaino · Accepted solution]

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