[AccessD] OT: Creating a MySQL View

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




More information about the AccessD mailing list