mySQL - Dynamic Query writing help

[Scarpozzi]

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)

 

[sdifox]

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.

 

[Scarpozzi]

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.

 

[sdifox]

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.

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.