hope i worded this well enough... :D
trouble working out a query
Table: weeklyrevenue
Week Number||value
25||20
26||40
28||60
Table daily_totals
Week Number||quantity
26||10
27||10
28||10
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;
Week Number||revenue_generated
26||400
27||400
28||800
Current query:
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)??
