February 24, 2011 at 7:23 am
Hiya guys,
I have a table like below:
SELECT
[Province]
,[Date]
,[Region]
,[Store]
,[Budget]
,[Actual Revenue]
,[Revenue Last year]
FROM [TotalRevenue]
That table contains rows like:
Province A, '2011-02-24', Region A, Store 1, 1000, 2000, 500
Province A, '2011-02-24', Region A, Store 2, 2000, 1000, 750
Province A, '2011-02-24', Region A, Store 3, 500 , 1500, 1000
Province A, '2011-02-24', Region B, Store 3, 3000, 2000, 3000
Province A, '2011-02-24', Region B, Store 4, 1500, 2500, 4000
Province A, '2011-02-24', Region C, Store 5, 500 , 4000, 2000
Province A, '2011-02-24', Region C, Store 6, 1000, 6000, 1400
..... and so on for more dates, provinces, regions...
These rows are used for reports in Excel. To select the data for these reports, the values in Budget, Actual Revenue and Revenue Last Year are summed. In addition two columns ([Deviation on Budget], and [Deviation on Last Year]) are added by dividing the values in Budget, Actual Revenue and Revenue Last year.
In the reports, the region with the highest positive value for [Deviation on Budget] must appear first, ordering stores with the highest value for [Deviation on Budget] first and after that listing stores in the same region second highest positive value for [Deviation on Budget] and so on.
Furthermore, to make things more complicated, subtotals for only the regions need to be added to the result.
This was the query I prepared but did not work.
SELECT
Region,
Store,
ISNULL(SUM([Budget]),0) AS [Budget], ISNULL(SUM([Actual Revenue]) ,0) AS [Actual Revenue],
ISNULL(SUM([Revenue Last Year]),0) AS [Revenue Last Year],
CASE WHEN ISNULL(SUM([Actual Revenue]),0) = 0 THEN -1
WHEN ISNULL(SUM([Budget]),0) <> 0 THEN (ISNULL(SUM([Actual Revenue]),0)-SUM([Budget]))/(SUM([Budget]))
ELSE 1 END as [Afwijking budget],
CASE WHEN ISNULL(SUM([Actual Revenue]),0) = 0 THEN -1
WHEN ISNULL(SUM([Actual Revenue]),0) = 0 THEN 0
WHEN ISNULL(SUM([Revenue Last Year]),0) = 0 OR ISNULL(SUM([Revenue Last Year]),0) = 0 THEN 1
ELSE (SUM([Actual Revenue])-SUM([Revenue Last Year]))/SUM([Revenue Last Year]) END as [Afwijking vorig jaar]
FROM [Revenue]
INNER JOIN LWD ON [Revenue].Date = LWD.Date
WHERE (Province = 'Province A')
GROUP BY Region, Store WITH ROLLUP
ORDER BY
Regio,
(CASE WHEN ISNULL(SUM([Actual Revenue]),0) = 0 THEN -1
WHEN ISNULL(SUM([Budget]),0) <> 0 THEN (ISNULL(SUM([Actual Revenue]),0)-SUM([Budget]))/(SUM([Budget]))
ELSE 1 END
) DESC
I would like to have results as follows:
Region C Store 5 500 4000 2000 7.00 3.00
Region C Store 6 1000 6000 1400 5.00 0.33
Subtotal for region C...
Region A Store 3 500 1500 1000 2.00 0.50
Region A Store 1 1000 2000 500 1.00 -0.33
Region A Store 2 2000 1000 750 -0.50 -0.37
Subtotal for region A...
Region B Store 4 1500 2500 4000 0.66 1.00
Region B Store 3 3000 2000 3000 -0.33 3.28
Subtotal for region B...
Somehow I'm not able to get the data as above. I've been pulling my hair out because it must be really simple but I'm missing out on the possible solution.
Someone that can point me to the right direction? Maybe use some kind of intelligent ROW_NUMBER() construct?
Many thanks!
February 24, 2011 at 10:15 am
Please post the DDL for your table and the DML for your data.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply