Darren D
darren at activebilling.com.au
Wed Mar 4 16:55:09 CST 2009
Hi All
A colleague is trying to create a 'view' in MySQL but is having issues because
of the 'embedded' sub query in one of the joins
He asked if 'my list' might be able to look at it (I brag about this list all
the time :-))
I realise this is OT so if any MySQL gurus want to have a look at this - Please
send any replies straight back to me if that's OK - thanks
Here 'tis - Thanks in advance team
Darren
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE VIEW v_weighting AS
SELECT du.account_no, du.exchange_id, ex.name AS Exchange,
SUM(du.num_completed_calls) AS Exchange_Calls, Tot.Account_calls,
d.measure_ref, d.value, d.comparative_value AS Exchange_Percent, da.value AS
Aus_Value, da.comparative_value AS Aus_Percent,
(d.comparative_value - da.comparative_value)/ da.comparative_value AS
Exchange_Trend, SUM(du.num_completed_calls)/Tot.Account_Calls AS
Exchange_Call_Weighting,
((d.comparative_value - da.comparative_value)/ da.comparative_value) *
SUM(du.num_completed_calls)/Tot.Account_Calls AS Weighted_Exchange_Trend
FROM daily_usage_exchange du
JOIN exchanges ex
ON du.exchange_id = ex.id
JOIN demographics d
ON ex.name = d.exchange_name
JOIN demographics_views dv
ON ( d.measure_ref = dv.measure_ref )
JOIN v_Aus_demographics da
ON d.measure_ref = da.measure_ref
JOIN (SELECT account_no, sum(u.num_completed_calls) as Account_Calls
FROM daily_usage_exchange u
WHERE u.`start_date` >= ADDDATE(CURDATE(), -30)
GROUP BY account_no) Tot
ON Tot.account_no = du.account_no
WHERE ( du.`start_date` >= ADDDATE(CURDATE(), -30)
AND dv.data_type = 'Population'
AND dv. demographic_view = 'DETAIL'
)
GROUP BY account_no, exchange_id, d.measure_ref