I am pretty new to SQL, so forgive me if this is a noob question. I am building a stored procedure that queries from a table that has data stored by month. Here is a simplified example of what the table looks like:
+------------+------------+------------+
| date | value_A | value_B |
+------------+------------+------------+
| 2014-01-01 | 100 | 200 |
| 2014-02-01 | 200 | 400 |
| 2014-03-01 | 300 | 600 |
| 2014-04-01 | 400 | 800 |
| 2014-05-01 | 500 | 1000 |
| 2014-06-01 | 600 | 1200 |
| 2014-07-01 | 700 | 1400 |
| ... | ... | ... |
+------------+------------+------------+
In the procedure, I need to calculate: (Value_A / Value_B). However, Value_B needs be lagged one month behind. So in the example above, the calculation for February 2014 would be: (Value_A of Feb 2014 / Value_B or Jan 2014) or (200 / 200). The calculation for March 2014 would then be: (Value_A of Mar 2014 / Value_B of Feb 2014) or (300 / 400), and so on and so forth. Hope that makes sense.
It wouldn't be too difficult to do if I was only querying a specific month since I could use a CASE statement in the procedure. But since I need to retrieve a year's worth of data grouped by month, I am a bit stumped.
Without the one month lag of Value_B, I have the SQL statement set up like:
SELECT ex.`date `, ex.value_A / ex.value_B AS calc
FROM example_table ex
WHERE ex.`date` BETWEEN '2014-03-01' AND '2015-02-01'
GROUP BY ex.`date`
I'm not sure where to go from here. I've experimented with different joins and unions but haven't had any luck. Do I need to query Value_A and Value_B separately with their own date range, then merge them together somehow for the calculation? Or am I making this much harder than it really is?
Thanks for your help.
解决方案SELECT t.date, t.value_A/t2.value_B AS calc
FROM table t JOIN table t2 ON t.date = DATE_ADD(t2.date, INTERVAL 1 MONTH)