hope i worded this well enough... :D
trouble working out a query
Need week 27 totals to be multiplyed by week 26 revenue not week 28 revenue as the revenue would have been generated by the same values as used in week 26 to save having to re-enter revenue data if it hasn't changed;
SELECT year(daily_totals.Date) As 'Year', (WEEK(Date,7) - WEEK('2012-04-02',7)+2) As 'Week_Number', Concat(Date_Format(DATE(Date + INTERVAL (2 - DAYOFWEEK(Date)) DAY), '%W %D %M %Y'), ' - ',Date_Format(DATE(Date + INTERVAL (7 - DAYOFWEEK(Date)) DAY), '%W %D %M %Y')) as 'Week', concat(sum((daily_totals.`Filtered to Refurb`) * weeklyrevenue.`Filtered to Refurb`),' (',sum(daily_totals.`Filtered to Refurb`), ' Units)' ) As 'Filtered to Refurb', concat(sum(daily_totals.Anovo) * (weeklyrevenue.Anovo),' (',sum(daily_totals.Anovo), ' Units)') As 'Anovo' From daily_totals, weeklyrevenue WHERE daily_totals.Financial_Year = weeklyrevenue.Financial_Year AND daily_totals.Week_Number = weeklyrevenue.Week_Number GROUP BY Year(daily_totals.Date), week
could i use equalto/less than max(date)??