July 24, 2006 at 1:49 pm
Hi,
I have a summary table which store all kinds of business measurements (there's 86 measure_name, I list only 2 for example usage) and values. Example:
Location2 Measure_Name Location1 Measure_Value
S00152 TY_Sales Chicago 2111.05
S00152 PY_Sales Chicago 2200.65
D1005 TY_Sales Chicago 12240.05
D1005 PY_Sales Chicago 10079.87
D2188 TY_Sales Chicago 11555.21
D2188 PY_Sales Chicago 10800.44
Chicago TY_Sales All 132240.05
Chicago PY_Sales All 132079.87
We need to calculate Comp Sales and the formula is "(TY_Sales-PY_Sales)/PY_Sales"
The result set should be
Location2 Location1 Comp_Sales
S00152 Chicago -0.041
D1005 Chicago 0.214
D2188 Chicago 0.070
Chicago All 0.001
How to do that?
July 24, 2006 at 2:19 pm
Table design discussions set aside, this should get you waht you want:
select
s1
.Location2,
s1
.Location1,
(s1.Measure_Value - s2.Measure_Value)/s2.Measure_Value as Comp_Sales
from
YourTable s1
INNER
JOIN
YourTable s2
ON
s1
.Location1 = s2.Location1
AND
s1
.Location2 = s2.Location2
WHERE
s1
.Measure_Name = 'TY_Sales'
AND
s2
.Measure_Name = 'PY_Sales'
July 24, 2006 at 2:26 pm
-- Prepare test data
declare @test table (Location2 varchar(7), Measure_Name varchar(8), Location1 varchar(7), Measure_Value smallmoney)
insert @test
select 'S00152', 'TY_Sales', 'Chicago', 2111.05 union all
select 'S00152', 'PY_Sales', 'Chicago', 2200.65 union all
select 'D1005', 'TY_Sales', 'Chicago', 12240.05 union all
select 'D1005', 'PY_Sales', 'Chicago', 10079.87 union all
select 'D2188', 'TY_Sales', 'Chicago', 11555.21 union all
select 'D2188', 'PY_Sales', 'Chicago', 10800.44 union all
select 'Chicago', 'TY_Sales', 'All', 132240.05 union all
select 'Chicago', 'PY_Sales', 'All', 132079.87
-- Do the work
SELECT z.Location2,
z.Location1,
CASE WHEN z.PY = 0 THEN NULL ELSE (z.TY - z.PY) / z.PY END Perc
FROM (
SELECT Location1,
Location2,
SUM(CASE WHEN Measure_Name = 'TY_Sales' THEN Measure_Value ELSE 0 END) TY,
SUM(CASE WHEN Measure_Name = 'PY_Sales' THEN Measure_Value ELSE 0 END) PY
FROM @test-2
GROUP BY Location1,
Location2
) z
N 56°04'39.16"
E 12°55'05.25"
July 24, 2006 at 4:05 pm
Thank you so much. Both queries work perfectly.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply