Hey there!
Im trying to make a dashboard, based on three questions.
The first one gives me a list of customer id’s that are relevant.
The second one uses one of those customer id’s to get a list of accounts on that customer
The third uses one of the account id’s to get the data i want to report.
I want to be able to enter a customer id, and have it use all the accounts that match to get all the matching results in the third.
I also want to be able to loop trough all the customers, get all the accounts, and make a complete report(third question) of all of it.
Is such a thing possible?
Copy pasting the sql so you can see the current questions
SELECT i_customer FROM Customers c
WHERE EXISTS(SELECT a.i_account FROM Accounts a JOIN Custom_Field_Values cfv ON a.i_account = cfv.i_account
JOIN Custom_Fields cf ON cfv.i_custom_field = cf.i_custom_field AND cf.name = ‘AccessType’
WHERE a.i_customer = c.i_customer AND cfv.value = 3)
Second question
SELECT IFNULL(cfv.value, 1) AS access_type_id, a.id, a.i_account, a.i_customer, a.i_product, p.name AS product_name
FROM Accounts a JOIN Products p ON a.i_product = p.i_product LEFT
JOIN Custom_Field_Values cfv ON a.i_account = cfv.i_account JOIN Custom_Fields cf ON cf.i_custom_field = cfv.i_custom_field AND cf.name = ‘AccessType’
WHERE a.i_customer ={{variable_i_customer }} AND a.i_master_account IS NULL
Last question
SELECT SUM(count) AS count, SUM(duration_sec) AS duration_sec, SUM(amount_ex_tax) AS amount_ex_tax, SUM(cost) AS cost, unit, item_type, ‘’ AS description
FROM billing.Invoice_details WHERE i_account = {{i_account}} AND belongs_to_month = {{month}} AND item_type =‘A’ AND unit != ‘MB’ GROUP BY unit
UNION ALL
SELECT SUM(count) AS count, SUM(duration_sec) AS duration_sec, SUM(amount_ex_tax) AS amount_ex_tax, SUM(cost) AS cost, unit, item_type, description
FROM billing.Invoice_details WHERE i_account = {{i_account}} AND belongs_to_month ={{month}} AND item_type = ‘A’ AND unit = ‘MB’ GROUP BY unit, description
UNION ALL
SELECT SUM(count) AS count, SUM(duration_sec) AS duration_sec, SUM(amount_ex_tax) AS amount_ex_tax, SUM(cost) AS cost, unit, item_type, description
FROM billing.Invoice_details WHERE i_account = {{i_account}} AND belongs_to_month = {{month}} AND item_type IN (‘AS’, ‘AC’) GROUP BY unit, description, item_type
And advice is greatly apriciated.