Creating a graph using 2 tables on different date structure

Archived Topics
1

I have 2 tables,

First table is a weekly FORECAST that shows part number, quantity, and date, the date represents Mondays.

Second table is a list of daily SALES, it shows the part number, quantity and date, the date represents any of the seven days a week the product is sold.

I would like to add up the quantity sold from the sales table grouped by week and part number and then create a graph that has a pen for quantity of forecast and quantity of sales by dates on a selected part number.

The problem I run into is that when I run the query on the sales table the grouping on the week causes the date to become an integer or week number, which is not an acceptable timestamp for the graph.

Any suggestions?

2

The easiest thing (if you’re using MySQL) is to group by YEARWEEK(date) on the sales table, and then just select YEARWEEK(date) from the forecast table, and use a numeric axis on the classic chart instead of a date axis, but if you really want a date axis, I’m sure we can come up with a snazzy SQL query that gets it for you…