IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'ServiceManager_Report_ServiceManagement_SP_GetCMCollectionGroupData')
BEGIN
DROP PROCEDURE dbo.ServiceManager_Report_ServiceManagement_SP_GetCMCollectionGroupData
END
GO
IF(@StartDate) is NULL SET @StartDate = '1900/01/01'
IF(@EndDate) is NULL SET @EndDate = '2100/01/01'
SET @EndDate = DATEADD(DAY, 1, @EndDate)
DECLARE @StartDateKey int
DECLARE @EndDateKey int
SET @StartDateKey = CONVERT(nvarchar(8), @StartDate, 112)
SET @EndDateKey = CONVERT(nvarchar(8), @EndDate, 112)
DECLARE @WattPerComputer INT = 150
DECLARE @CostOfPower FLOAT = 0.15
INSERT INTO @groupByComputers
SELECT configItems.GroupById ,
configItems.GroupByDimKey ,
configItems.GroupByBME ,
configItems.GroupByEntityDimKey ,
configItems.GroupByName ,
compDim.ComputerDimKey,
compDim.EntityDimKey,
compDim.BaseManagedEntityId,
configItems.CreatedDate,
configItems.DeletedDate
FROM @groupByConfigItems configItems
INNER JOIN ComputerDimvw compDim ON configItems.ConfigItemEntityDimKey = compDim.EntityDimKey
IF ( @ServiceDimKeys = '' AND @GroupDimKeys = '' AND @CollectionDimKeys = '' AND @GroupByForReport = 'AggregatedTime' )
BEGIN
INSERT INTO @groupByComputers
SELECT DISTINCT NULL, NULL, NULL, NULL, NULL, CompDim.ComputerDimKey, entityDim.EntityDimKey, CompDim.BaseManagedEntityId, '1/1/1900', NULL
FROM dbo.ComputerDimvw CompDim
INNER JOIN dbo.EntityDimvw entityDim ON entityDim.BaseManagedEntityId = CompDim.BaseManagedEntityId
WHERE CompDim.IsDeleted = 0
END
IF @GroupByForReport != 'AggregatedTime'
BEGIN
DECLARE @summary AS TABLE(ReportSeries NVARCHAR(256),
PhysicalComputers INT,
VirtualComputers INT,
SoftwareUpdateCompliance FLOAT,
SecurityBulletinCompliance FLOAT,
TotalConsumption FLOAT,
CostOfPower FLOAT,
UnusedConsumption FLOAT)
--Virtual Computers--
insert into @summary
SELECT comp.GroupByName AS ReportSeries,
NULL AS PhysicalComputers,
COUNT(DISTINCT dimComp.ComputerDimKey) AS VirtualComputers,
NULL AS SoftwareUpdateCompliance,
NULL AS SecurityBulletingCompliance,
NULL AS TotalConsumption,
NULL AS CostOfPower,
NULL AS UnusedConsumption
FROM dbo.ComputerDimvw dimComp
INNER JOIN @groupByComputers comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN dbo.EntityDimvw dimEnt ON comp.ComputerEntityDimKey = dimEnt.EntityDimKey
WHERE dimComp.IsVirtualMachine = 1
AND (dimEnt.IsDeleted = 0 AND dimEnt.TimeAdded < @EndDate) OR (dimEnt.IsDeleted = 1 AND dimEnt.LastModified < @EndDate)
GROUP BY comp.GroupByName
--Software Update Compliance--
insert into @summary
SELECT GroupByName AS ReportSeries,
NULL AS PhysicalComputers,
NULL AS VirtualComputers,
SUM(CAST(IsInstalled AS INT)) * 100.0 / ISNULL(NULLIF(SUM(CAST(IsInstalled AS INT) + CAST(IsMissing AS INT)), 0), 1) AS SoftwareUpdateCompliance,
NULL AS SecurityBulletingCompliance,
NULL AS TotalConsumption,
NULL AS CostOfPower,
NULL AS UnusedConsumption
FROM dbo.ComputerDimvw dimComp
LEFT JOIN dbo.ComputerHasSoftwareUpdateInstalledFactvw factCompSoftwareUpdate ON dimComp.ComputerDimKey = factCompSoftwareUpdate.ComputerDimKey
INNER JOIN @groupByComputers comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN dbo.EntityDimvw dimEnt ON comp.ComputerEntityDimKey = dimEnt.EntityDimKey
WHERE (dimEnt.IsDeleted = 0 AND dimEnt.TimeAdded < @EndDate) OR (dimEnt.IsDeleted = 1 AND dimEnt.LastModified < @EndDate)
GROUP BY GroupByName
--Security Bulletin Compliance--
insert into @summary
SELECT GroupByName AS ReportSeries,
NULL AS PhysicalComputers,
NULL AS VirtualComputers,
NULL AS SoftwareUpdateCompliance,
SUM(CAST(IsInstalled AS INT)) * 100.0 / ISNULL(NULLIF(SUM(CAST(IsInstalled AS INT) + CAST(IsMissing AS INT)), 0), 1) AS SecurityBulletingCompliance,
NULL AS TotalConsumption,
NULL AS CostOfPower,
NULL AS UnusedConsumption
FROM dbo.ComputerDimvw dimComp
LEFT JOIN dbo.ComputerHasSoftwareUpdateInstalledFactvw factCompSoftwareUpdate ON dimComp.ComputerDimKey = factCompSoftwareUpdate.ComputerDimKey
INNER JOIN dbo.SoftwareUpdateDim dimSoftUpdt ON dimSoftUpdt.SoftwareUpdateDimKey = factCompSoftwareUpdate.SoftwareUpdateDimKey
INNER JOIN @groupByComputers comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN dbo.EntityDimvw dimEnt ON comp.ComputerEntityDimKey = dimEnt.EntityDimKey
WHERE
(dimSoftUpdt.BulletinID IS NOT NULL AND dimSoftUpdt.BulletinID <> '') AND
((dimEnt.IsDeleted = 0 AND dimEnt.TimeAdded < @EndDate) OR (dimEnt.IsDeleted = 1 AND dimEnt.LastModified < @EndDate))
GROUP BY GroupByName
-- Total Consumption
-- Cost Of Power
--Unused Consumption--
-- Is needed in KWH. For this we need to get the total wattage of all computers that are on.
IF EXISTS (SELECT 'x' FROM sys.tables WHERE name = '#ComputerOn')
BEGIN
DROP TABLE #ComputerOn
END
IF EXISTS (SELECT 'x' FROM sys.tables WHERE name = '#ComputerActive')
BEGIN
DROP TABLE #ComputerActive
END
SELECT comp.GroupByName AS GroupName,
SUM(Hour0 + Hour1 + Hour2 + Hour3 + Hour4 + Hour5 + Hour6 + Hour7 + Hour8 + Hour9 +
Hour10 + Hour11 + Hour12 + Hour13 + Hour14 + Hour15 + Hour16 + Hour17 + Hour18 + Hour19 +
Hour20 + Hour21 + Hour22 + Hour23) AS [Minutes],
COUNT(dimComp.ComputerDimKey) AS Computers
INTO dbo.#ComputerOn
FROM dbo.PowerActivityDayFactvw factPwr
INNER JOIN dbo.ComputerDimvw dimComp ON factPwr.ComputerDimKey = dimComp.ComputerDimKey
INNER JOIN @groupByComputers comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN PowerActivityRecordEventType outrigger ON factPwr.PowerActivityRecordEventTypeId = outrigger.PowerActivityRecordEventTypeId
WHERE factPwr.CreatedDate >= @StartDate AND factPwr.CreatedDate < @EndDate
AND outrigger.ID = 'System.Power.ActivityRecordEnum.ComputerOn'
GROUP BY comp.GroupByName
SELECT comp.GroupByName AS GroupName,
SUM(Hour0 + Hour1 + Hour2 + Hour3 + Hour4 + Hour5 + Hour6 + Hour7 + Hour8 + Hour9 +
Hour10 + Hour11 + Hour12 + Hour13 + Hour14 + Hour15 + Hour16 + Hour17 + Hour18 + Hour19 +
Hour20 + Hour21 + Hour22 + Hour23) AS [Minutes],
COUNT(dimComp.ComputerDimKey) AS Computers
INTO dbo.#ComputerActive
FROM dbo.PowerActivityDayFactvw factPwr
INNER JOIN dbo.ComputerDimvw dimComp ON factPwr.ComputerDimKey = dimComp.ComputerDimKey
INNER JOIN @groupByComputers comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN PowerActivityRecordEventType outrigger ON factPwr.PowerActivityRecordEventTypeId = outrigger.PowerActivityRecordEventTypeId
WHERE factPwr.CreatedDate >= @StartDate AND factPwr.CreatedDate < @EndDate
AND outrigger.ID = 'System.Power.ActivityRecordEnum.ComputerActive'
GROUP BY comp.GroupByName
INSERT INTO @summary
SELECT table1.GroupName AS ReportSeries,
NULL AS PhysicalComputers,
NULL AS VirtualComputers,
NULL AS SoftwareUpdateCompliance,
NULL AS SecurityBulletinCompliance,
(table1.[Minutes] * @WattPerComputer)/(60 * 1000) AS TotalConsumption,
(table1.[Minutes] * @WattPerComputer)/(60 * 1000) * @CostOfPower AS CostOfPower,
CASE WHEN table1.[Minutes] > 0 THEN (100. * table2.[Minutes])/table1.[Minutes] ELSE 0 END AS UnusedConsumption
FROM dbo.#ComputerOn table1
INNER JOIN dbo.#ComputerActive table2 ON table1.GroupName = table2.GroupName
SELECT
ReportSeries,
NULL AS Incidents,
NULL AS IncidentsResolvedWithinTarget,
NULL AS AvgTimeToResolution,
NULL AS Alerts,
NULL AS AlertsToIncidentsRatio,
NULL As PhysicalComputers,
SUM(VirtualComputers) As VirtualComputers,
SUM(SoftwareUpdateCompliance) As SoftwareUpdateCompliance,
SUM(SecurityBulletinCompliance) As SecurityBulletinCompliance,
NULL AS DCMIncidents,
SUM(TotalConsumption) As TotalConsumption,
SUM(CostOfPower) As CostOfPower,
SUM(UnusedConsumption) As UnusedConsumption
FROM @summary
GROUP BY ReportSeries
END
ELSE IF @TimeAggregation IS NOT NULL
BEGIN
DECLARE @datesummary AS TABLE(ReportSeries DATETIME,
PhysicalComputers INT,
VirtualComputers INT,
SoftwareUpdateCompliance FLOAT,
SecurityBulletinCompliance FLOAT,
TotalConsumption FLOAT,
CostOfPower FLOAT,
UnusedConsumption FLOAT)
--Virtual Computers--
insert into @datesummary
SELECT ReportSeries =
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END,
NULL AS PhysicalComputers,
COUNT(DISTINCT dimComp.ComputerDimKey) AS VirtualComputers,
NULL AS SoftwareUpdateCompliance,
NULL AS SecurityBulletinCompliance,
NULL AS TotalConsumption,
NULL AS CostOfPower,
NULL AS UnusedConsumption
FROM dbo.ComputerDimvw dimComp
INNER JOIN @groupByComputers comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN dbo.EntityDimvw dimEnt ON comp.ComputerEntityDimKey = dimEnt.EntityDimKey
INNER JOIN @selectdatedim selectdatedim on selectdatedim.CalendarDate = DATEADD(dd, 0, DATEDIFF(dd, 0, dimEnt.TimeAdded))
WHERE dimComp.IsVirtualMachine = 1
AND (dimEnt.IsDeleted = 0 AND dimEnt.TimeAdded < @EndDate) OR (dimEnt.IsDeleted = 1 AND dimEnt.LastModified < @EndDate)
GROUP BY
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END
--Software Update Compliance--
insert into @datesummary
SELECT ReportSeries =
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END,
NULL AS PhysicalComputers,
NULL AS VirtualComputers,
SUM(CAST(IsInstalled AS INT)) * 100.0 / ISNULL(NULLIF(SUM(CAST(IsInstalled AS INT) + CAST(IsMissing AS INT)), 0), 1) AS SecurityBulletinCompliance,
NULL AS SecurityBulletinCompliance,
NULL AS TotalConsumption,
NULL AS CostOfPower,
NULL AS UnusedConsumption
FROM dbo.ComputerDimvw dimComp
LEFT JOIN dbo.ComputerHasSoftwareUpdateInstalledFactvw factCompSoftwareUpdate ON dimComp.ComputerDimKey = factCompSoftwareUpdate.ComputerDimKey
INNER JOIN @groupByComputers comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN dbo.EntityDimvw dimEnt ON comp.ComputerEntityDimKey = dimEnt.EntityDimKey
INNER JOIN @selectdatedim selectdatedim on selectdatedim.DateKey = factCompSoftwareUpdate.DateKey
WHERE (dimEnt.IsDeleted = 0 AND dimEnt.TimeAdded < @EndDate) OR (dimEnt.IsDeleted = 1 AND dimEnt.LastModified < @EndDate)
GROUP BY
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END
--Security Bulletin Compliance--
insert into @datesummary
SELECT ReportSeries =
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END,
NULL AS PhysicalComputers,
NULL AS VirtualComputers,
NULL AS SoftwareUpdateCompliance,
SUM(CAST(IsInstalled AS INT)) * 100.0 / ISNULL(NULLIF(SUM(CAST(IsInstalled AS INT) + CAST(IsMissing AS INT)), 0), 1) AS SecurityBulletinCompliance,
NULL AS TotalConsumption,
NULL AS CostOfPower,
NULL AS UnusedConsumption
FROM dbo.ComputerDimvw dimComp
LEFT JOIN dbo.ComputerHasSoftwareUpdateInstalledFactvw factCompSoftwareUpdate ON dimComp.ComputerDimKey = factCompSoftwareUpdate.ComputerDimKey
INNER JOIN dbo.SoftwareUpdateDim dimSoftUpdt ON dimSoftUpdt.SoftwareUpdateDimKey = factCompSoftwareUpdate.SoftwareUpdateDimKey
INNER JOIN @groupByComputers comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN dbo.EntityDimvw dimEnt ON comp.ComputerEntityDimKey = dimEnt.EntityDimKey
INNER JOIN @selectdatedim selectdatedim on selectdatedim.DateKey = factCompSoftwareUpdate.DateKey
WHERE
(dimSoftUpdt.BulletinID IS NOT NULL AND dimSoftUpdt.BulletinID <> '' ) AND
((dimEnt.IsDeleted = 0 AND dimEnt.TimeAdded < @EndDate) OR (dimEnt.IsDeleted = 1 AND dimEnt.LastModified < @EndDate))
GROUP BY
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END
-- Total Consumption
-- Cost Of Power
--Unused Consumption--
-- Is needed in KWH. For this we need to get the total wattage of all computers that are on.
IF EXISTS (SELECT 'x' FROM sys.tables WHERE name = '#ComputerOn')
BEGIN
DROP TABLE #ComputerOnDate
END
IF EXISTS (SELECT 'x' FROM sys.tables WHERE name = '#ComputerActive')
BEGIN
DROP TABLE #ComputerActiveDate
END
SELECT [DATE] =
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END,
SUM(Hour0 + Hour1 + Hour2 + Hour3 + Hour4 + Hour5 + Hour6 + Hour7 + Hour8 + Hour9 +
Hour10 + Hour11 + Hour12 + Hour13 + Hour14 + Hour15 + Hour16 + Hour17 + Hour18 + Hour19 +
Hour20 + Hour21 + Hour22 + Hour23) AS [Minutes],
COUNT(dimComp.ComputerDimKey) AS Computers
INTO #ComputerOnDate
FROM dbo.PowerActivityDayFactvw factPwr
INNER JOIN dbo.ComputerDimvw dimComp ON factPwr.ComputerDimKey = dimComp.ComputerDimKey
INNER JOIN @groupByComputers comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN PowerActivityRecordEventType outrigger ON factPwr.PowerActivityRecordEventTypeId = outrigger.PowerActivityRecordEventTypeId
INNER JOIN @selectdatedim selectdatedim ON factPwr.DateKey = selectdatedim.DateKey
WHERE factPwr.CreatedDate >= @StartDate AND factPwr.CreatedDate < @EndDate
AND outrigger.ID = 'System.Power.ActivityRecordEnum.ComputerOn'
GROUP BY
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END
SELECT [DATE] =
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END,
SUM(Hour0 + Hour1 + Hour2 + Hour3 + Hour4 + Hour5 + Hour6 + Hour7 + Hour8 + Hour9 +
Hour10 + Hour11 + Hour12 + Hour13 + Hour14 + Hour15 + Hour16 + Hour17 + Hour18 + Hour19 +
Hour20 + Hour21 + Hour22 + Hour23) AS [Minutes],
COUNT(dimComp.ComputerDimKey) AS Computers
INTO #ComputerActiveDate
FROM dbo.PowerActivityDayFactvw factPwr
INNER JOIN dbo.ComputerDimvw dimComp ON factPwr.ComputerDimKey = dimComp.ComputerDimKey
INNER JOIN @groupByComputers comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN PowerActivityRecordEventType outrigger ON factPwr.PowerActivityRecordEventTypeId = outrigger.PowerActivityRecordEventTypeId
INNER JOIN @selectdatedim selectdatedim ON factPwr.DateKey = selectdatedim.DateKey
WHERE factPwr.CreatedDate >= @StartDate AND factPwr.CreatedDate < @EndDate
AND outrigger.ID = 'System.Power.ActivityRecordEnum.ComputerActive'
GROUP BY
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END
INSERT INTO @datesummary
SELECT table1.[DATE] AS ReportSeries,
NULL AS PhysicalComputers,
NULL AS VirtualComputers,
NULL AS SoftwareUpdateCompliance,
NULL AS SecurityBulletinCompliance,
(table1.[Minutes] * @WattPerComputer)/(60 * 1000) AS TotalConsumption,
(table1.[Minutes] * @WattPerComputer)/(60 * 1000) * @CostOfPower AS CostOfPower,
CASE WHEN table1.[Minutes] > 0 THEN (100. * table2.[Minutes])/table1.[Minutes] ELSE 0 END AS UnusedConsumption
FROM #ComputerOnDate table1
INNER JOIN #ComputerActiveDate table2 ON table1.[DATE] = table2.[DATE]
SELECT
ReportSeries,
NULL AS Incidents,
NULL AS IncidentsResolvedWithinTarget,
NULL AS AvgTimeToResolution,
NULL AS Alerts,
NULL AS AlertsToIncidentsRatio,
NULL AS PhysicalComputers,
SUM(VirtualComputers) As VirtualComputers,
SUM(SoftwareUpdateCompliance) As SoftwareUpdateCompliance,
SUM(SecurityBulletinCompliance) As SecurityBulletinCompliance,
NULL AS DCMIncidents,
SUM(TotalConsumption) As TotalConsumption,
SUM(CostOfPower) As CostOfPower,
SUM(UnusedConsumption) As UnusedConsumption
FROM @datesummary
GROUP BY ReportSeries
END
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON [dbo].[ServiceManager_Report_ServiceManagement_SP_GetCMCollectionGroupData] TO reportuser
GO
Home