G
Guest
Hi All,
I have a table with the following attributes:
Customer_Code, Product_Code, Category, Month, Value
Where category has the domain BudgetTurnover and ActualTurnover
and Month has the domain naturally Jan to Dec
The budget figures are in for the year.
I get the monthly actual figures at the end of each month.
What I want to do is get a table which looks like this:
Customer_Code Product_Code Month Value(Budget) Value(Actual) Difference
I thought to do 2 queries, one which give me a (BUDGET QUERY TABLE) :
Customer_Code Product_Code Month Value(Budget)
and another which gives me a (ACTUAL QUERY TABLE)
Customer_Code Product_Code Month Value(Actual)
I then thought to join the 2 query tables to get this:
Customer_Code Product_Code Value(Budget) Value(Actual) Difference
but I need to include:
1. all the matching rows in both tables (ie where Cust_Code, Product_Code
and Month match)
2. all rows in the budget table and have zero where there is no match in the
actual table where we did not get the budgeted sale and
3. all rows in the actual table and have zero where there is no match in the
budget table in case we got orders from new customer not in the budget
I am sure this is a no brainer but I just cannot get it out.
Can someone help me out say with the appropriate SQL perhaps?
Thanks
Bon
I have a table with the following attributes:
Customer_Code, Product_Code, Category, Month, Value
Where category has the domain BudgetTurnover and ActualTurnover
and Month has the domain naturally Jan to Dec
The budget figures are in for the year.
I get the monthly actual figures at the end of each month.
What I want to do is get a table which looks like this:
Customer_Code Product_Code Month Value(Budget) Value(Actual) Difference
I thought to do 2 queries, one which give me a (BUDGET QUERY TABLE) :
Customer_Code Product_Code Month Value(Budget)
and another which gives me a (ACTUAL QUERY TABLE)
Customer_Code Product_Code Month Value(Actual)
I then thought to join the 2 query tables to get this:
Customer_Code Product_Code Value(Budget) Value(Actual) Difference
but I need to include:
1. all the matching rows in both tables (ie where Cust_Code, Product_Code
and Month match)
2. all rows in the budget table and have zero where there is no match in the
actual table where we did not get the budgeted sale and
3. all rows in the actual table and have zero where there is no match in the
budget table in case we got orders from new customer not in the budget
I am sure this is a no brainer but I just cannot get it out.
Can someone help me out say with the appropriate SQL perhaps?
Thanks
Bon