Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers. It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.
(SELECT DV.ForecastYear, @Metric1,
( Sum(case when MetricName=@Metric1 then MetricValue end)
+ Sum(case when MetricName=@Metric3 then MetricValue end)
+ Sum(case when MetricName=@Metric4 then MetricValue end)
- Sum(case when MetricName=@Metric2 then MetricValue end)
+ Sum(case when MetricName=@Metric5 then MetricValue end)
- Sum(case when MetricName=@Metric6 then MetricValue end)
) / Sum(case when MetricName=@Metric7 then MetricValue end) CalcResults, @spid
FROM Metrics M2
INNER JOIN DataVersion DV
ON M2.DataVersionRowID = DV.DataVersionRowID
WHERE DV.DataVersionID = @DVID
AND MetricName in (@Metric1, @Metric2, @Metric3, @Metric4, @Metric5, @Metric6, @Metric7)
Group by DV.ForecastYear)
SELECT ForecastYear,
Metric1,
(sum_1 + sum_2 + sum_3 - sum_4 + sum_5 - sum_6) / sum_7,
spid
FROM ( SELECT DV.ForecastYear,
@Metric1 Metric1,
SUM(CASE
WHEN MetricName = @Metric1 THEN MetricValue
END) sum_1,
SUM(CASE
WHEN MetricName = @Metric3 THEN MetricValue
END) sum_2,
SUM(CASE
WHEN MetricName = @Metric4 THEN MetricValue
END) sum_3,
SUM(CASE
WHEN MetricName = @Metric2 THEN MetricValue
END) sum_4,
SUM(CASE
WHEN MetricName = @Metric5 THEN MetricValue
END) sum_5,
SUM(CASE
WHEN MetricName = @Metric6 THEN MetricValue
END) sum_6,
SUM(CASE
WHEN MetricName = @Metric7 THEN MetricValue
END) sum_7,
@spid spid
FROM Metrics M2
INNER JOIN DataVersion DV
ON M2.DataVersionRowID = DV.DataVersionRowID
WHERE DV.DataVersionID = @DVID
AND MetricName IN (@Metric1,@Metric2,@Metric3,@Metric4,
@Metric5,@Metric6,@Metric7)
GROUP BY DV.ForecastYear) t1
if object_id('tempdb..#mydata_source') is not null drop table #mydata_source
create table #mydata_source (Forecastyear int, MetricName varchar(20), metricValue decimal (18,6))
insert #mydata_source values (2009,'M2',222)
insert #mydata_source values (2009,'M5',555)
insert #mydata_source values (2009,'M6',666)
insert #mydata_source values (2009,'M7',777)
insert #mydata_source values (2010,'M1',111.111)
insert #mydata_source values (2010,'M2',222.222)
insert #mydata_source values (2010,'M3',333.333)
insert #mydata_source values (2010,'M4',444.444)
insert #mydata_source values (2010,'M5',555.555)
insert #mydata_source values (2010,'M7',777.777)
--insert #mydata_source values (2010,'M6',666.66)
insert #mydata_source values (2010,'M6',NULL)
declare @metric1 varchar(20)
declare @metric2 varchar(20)
declare @metric3 varchar(20)
declare @metric4 varchar(20)
declare @metric5 varchar(20)
declare @metric6 varchar(20)
declare @metric7 varchar(20)
set @metric1 = 'M1'
set @metric2 = 'M2'
set @metric3 = 'M3'
set @metric4 = 'M4'
set @metric5 = 'M5'
set @metric6 = 'M6'
set @metric7 = 'M7'
select forecastyear,
( Sum(case when MetricName=@Metric1 then MetricValue end)
+ Sum(case when MetricName=@Metric3 then MetricValue end)
+ Sum(case when MetricName=@Metric4 then MetricValue end)
- Sum(case when MetricName=@Metric2 then MetricValue end)
+ Sum(case when MetricName=@Metric5 then MetricValue end)
- Sum(case when MetricName=@Metric6 then MetricValue end)
) as old_way,
( Sum(case when MetricName=@Metric1 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric2 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric3 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric4 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric5 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric6 then MetricValue else 0.0 end)
) as new_way,
( 0
- Sum(case when MetricName=@Metric6 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric2 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric1 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric3 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric4 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric5 then MetricValue else 0.0 end)
) as new_way_jumbled
from #mydata_source
group by forecastyear
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.