0

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)??

2
Contributors
1
Reply
3
Views
5 Years
Discussion Span
Last Post by faroukmuhammad
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.