• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

mySQL - Dynamic Query writing help

Scarpozzi

Scarpozzi

Lifer
I've got a reporting tool that allows me to write SQL queries. It has an option to set dynamic variables by writing sql code to dynamically grab dates, etc.. One example is, it could grab today's date, convert to a string, then use substring functions to create a date code to use in the primary query.

Can you do anything like this in mySQL? I'm new to writing reports outside of the reporting tool. What I'm struggling with is that I have a database with date codes that splits the year in half. Basically first half and second half of the year. For 2016, it'd be like 20161 and 20167, 20171 and 20177... What I need to do is be able to write queries that look at a date code and use the previous date code on a separate table to look up prior history. (that's they dynamic part)
 
Of course you can do it in sql. That is what the reporting tool does anyway.

Put up sample data of tables involved and desired result if you want help with query.
 
Last edited:
Speed was the real issue. I wrote a few queries that hit the recursive side of the dates and they never completed before my mySQL connection timed out (and yes, I adjusted the timeouts on the client)... I'm dealing with a couple of tables with around 8M rows each, so I'm going to run this manually and stitch the results together. I think each semi-annual result is around 30k rows....I should be able to load 10 years worth in 30 minutes.
 
Scarpozzi said:
Speed was the real issue. I wrote a few queries that hit the recursive side of the dates and they never completed before my mySQL connection timed out (and yes, I adjusted the timeouts on the client)... I'm dealing with a couple of tables with around 8M rows each, so I'm going to run this manually and stitch the results together. I think each semi-annual result is around 30k rows....I should be able to load 10 years worth in 30 minutes.
Click to expand...


Err stored proc should do it. And you can always adjust timeout.

Like I said, post sample data and desired output if you want sql help. There are wrong ways of querying.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top