I have a slightly generic query. I have a table with actual sales and budget per week for the whole year, and another table which holds calender information, which I link my first table to work out which figures belong to which company weeks. What I want to do is an equation along the lines of:
My problem is that both actuals and budgets are in the same table so how can I work out the actual sales last week and take it away from this week's budget in one sql string? I've got the following code just to work out sales from last week.
SELECT sum(ACTUAL_SALES_VALUE) FROM MEASURES_DATA ON B.Retail_Outlet_Number = A.RETAIL_OUTLET_NUMBER Inner Join VWI0CAL_SMALL_CALENDAR C ON A.ssw_TIME = C.year_week_number GROUP_BY Calendar_Date, A.SSW_TIME, A.MEASURE_ID, B.Trading_Status_Ind HAVING ((AND ((A.MEASURE_ID)='ATSV')) AND (Calendar_Date = (date-7))
the "MEASURES_DATA" table holds sales by day, hence I use the calender table to work out week sales, if that makes sense. Now I just need to do something similar to work out this week's budget, and take one away from the other, but not sure how to go about it.