Building queries

For a year end report, some data needs to gathered from some tables and calculations made.

First run, get the data that needs to be summarised (This query returned 929 rows):

SELECT `truckdumps`.`Route` , `truckdumps`.`item` , sum( `truckdumps`.`qty` )
FROM `truckdumps`
WHERE `truckdumps`.`Date` >= '2017-10-29'
AND `truckdumps`.`Date` <= '2018-11-03'
GROUP BY `Route` , `item`

Because of the way costing is done for this internally, we have to group our data by week with the week ending on Saturdays (For this period, this query returned 5284 rows)

SELECT `truckdumps`.`Route`, `truckdumps`.`item`, sum(`truckdumps`.`qty`), DATE_ADD(`truckdumps`.`Date`, INTERVAL (7 - DAYOFWEEK(`truckdumps`.`Date`)) day) as WeekEndDay 
FROM `truckdumps` 
WHERE`truckdumps`.`Date` >= '2017-10-29' and `truckdumps`.`Date` <= '2018-11-03'
GROUP by `Route`, `item`, WeekEndDay

Now, to attach the pricing. (Which also returns 5284 rows)

SELECT `truckdumps`.`Route` , `truckdumps`.`item` , sum( `truckdumps`.`qty` ) , DATE_ADD( `truckdumps`.`Date` , INTERVAL( 7 - DAYOFWEEK( `truckdumps`.`Date` ) )
DAY ) AS WeekEndDay, `pricing`.`price`
FROM `truckdumps`
LEFT JOIN `pricing` ON ( DATE_ADD( `truckdumps`.`Date` , INTERVAL( 7 - DAYOFWEEK( `truckdumps`.`Date` ) )
DAY ) = `pricing`.`END`
AND `truckdumps`.`item` = `pricing`.`PRODNO` )
WHERE `Date` >= '2017-10-29'
AND `Date` <= '2018-11-03'
GROUP BY `Route` , `item` , WeekEndDay, `price`

Next show some totals(again, 5284 rows) :

SELECT `truckdumps`.`Route` , `truckdumps`.`item` , sum( `truckdumps`.`qty` ) , `pricing`.`price` , DATE_ADD( `truckdumps`.`Date` , INTERVAL( 7 - DAYOFWEEK( `truckdumps`.`Date` ) )
DAY ) AS WeekEndDay, sum( `truckdumps`.`qty` * `pricing`.`price` ) as value
FROM `truckdumps`
LEFT JOIN `pricing` ON ( DATE_ADD( `truckdumps`.`Date` , INTERVAL( 7 - DAYOFWEEK( `truckdumps`.`Date` ) )
DAY ) = `pricing`.`END`
AND `truckdumps`.`item` = `pricing`.`PRODNO` )
WHERE `Date` >= '2017-10-29'
AND `Date` <= '2018-11-03'
GROUP BY `Route` , `item` , WeekEndDay, `price`

Totals by Route (Returned 18 rows):

SELECT `truckdumps`.`Route` , sum( `truckdumps`.`qty` * `pricing`.`price` ) AS value
FROM `truckdumps`
LEFT JOIN `pricing` ON ( DATE_ADD( `truckdumps`.`Date` , INTERVAL( 7 - DAYOFWEEK( `truckdumps`.`Date` ) )
DAY ) = `pricing`.`END`
AND `truckdumps`.`item` = `pricing`.`PRODNO` )
WHERE `Date` >= '2017-10-29'
AND `Date` <= '2018-11-03'
GROUP BY `Route`

And one final Grand Total:

SELECT sum( `truckdumps`.`qty` * `pricing`.`price` ) AS value
FROM `truckdumps`
LEFT JOIN `pricing` ON ( DATE_ADD( `truckdumps`.`Date` , INTERVAL( 7 - DAYOFWEEK( `truckdumps`.`Date` ) )
DAY ) = `pricing`.`END`
AND `truckdumps`.`item` = `pricing`.`PRODNO` )
WHERE `Date` >= '2017-10-29'
AND `Date` <= '2018-11-03'