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

Honestly I don't understand your question...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.