Calculate relative totals with absolute values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculate relative totals with absolute values

Author

Topic

tderoo
Starting Member

4 Posts

Posted - 2015-02-28 : 06:22:05

Have a bit of an SQL challenge in that I need to create a well performing view in SQL2008R2 which calculates hours remaining at any given point in time. This is not a simple sum but has some absolute values included. Any bright ideas from the community on how to do the following in a neat way?

---------------------------------------------------------------------------------------------------
-- Basic simplified structure of maintenance and usage tables
---------------------------------------------------------------------------------------------------
CREATE TABLE #Maintenance (Id int NOT NULL
IDENTITY(1, 1)
CONSTRAINT PK_Maintenance PRIMARY KEY,
MaintenanceDate smalldatetime NOT NULL, -- When maintenance hours are reset
HoursAvailable numeric(5, 1) NULL -- Available hours until next maintenance
)
CREATE TABLE #Usage (Id int NOT NULL
IDENTITY(1, 1)
CONSTRAINT PK_Usage PRIMARY KEY,
UsageDate smalldatetime NOT NULL, -- When hours are used
HoursUsed numeric(5, 1) NULL -- Number of hours used
)
---------------------------------------------------------------------------------------------------
-- Example data for maintenance events and hours usage
---------------------------------------------------------------------------------------------------
INSERT INTO #Maintenance
(MaintenanceDate, HoursAvailable)
VALUES ('10-Jan-2015 10:00', 10.1),
('1-Feb-2015 00:00', 12.2)

INSERT INTO #Usage
(UsageDate, HoursUsed)
VALUES ('1-Jan-2015 15:00', 3.5),
('11-Jan-2015 7:30', 1.2),
('11-Jan-2015 11:10', 6.0),
('15-Jan-2015 00:00', 3.0),
('02-Feb-2015 13:00', 2.2)

---------------------------------------------------------------------------------------------------
-- Required output view so that at any given point in time I can determine hours remaining
---------------------------------------------------------------------------------------------------
-- AtDate HoursAvailable
-- '01-Jan-2015 15:00' NULL -- before first maintenance hence unknown
-- '10-Jan-2015 10:00' 10.1 -- maintenance hours reset
-- '11-Jan-2015 07:30' 8.9 -- subtract 1.2
-- '11-Jan-2015 11:10' 2.9 -- subtract 6.0
-- '15-Jan-2015 00:00' -0.1 -- subtract 3.0
-- '01-Feb-2015 00:00' 12.2 -- maintenance hours reset
-- '02-Feb-2015 13:00' 10.0 -- subtract 2.2

Theo de Roo
Mission Aviation Fellowship

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-02-28 : 07:39:17

This should work

-- SwePeso
WITH cteData
AS (
	SELECT	AtDate,
		ResetValue,
		HoursUsed,
		COUNT(ResetValue) OVER (ORDER BY AtDate) AS theGroup
	FROM	(
			SELECT	MaintenanceDate AS AtDate,
				HoursAvailable AS ResetValue,
				NULL AS HoursUsed
			FROM	#Maintenance

				UNION ALL

			SELECT	UsageDate AS AtDate,
				NULL AS ResetValue,
				HoursUsed
			FROM	#Usage
		) AS d
)
SELECT		AtDate,
		MAX(ResetValue) OVER (PARTITION BY theGroup ORDER BY AtDate) - SUM(ISNULL(HoursUsed, 0)) OVER (PARTITION BY theGroup ORDER BY AtDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS HoursAvailable
FROM		cteData
ORDER BY	AtDate;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

tderoo
Starting Member

4 Posts

Posted - 2015-02-28 : 10:21:22

Hi SwePeso,

thanks for the reply. Unfortunately I cannot run this since "UNBOUNDED PRECEDING" and "CURRENT" are SQL 2012 window function which and not available on SQL 2008R2 :-( Any quick changes which could make it run in SQL 2008 R2?

Theo

Theo de Roo
Mission Aviation Fellowship

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-02-28 : 15:00:10

[code]WITH cteData(AtDate, HoursAvailable)
AS (
SELECT MaintenanceDate,
HoursAvailable
FROM #Maintenance

UNION ALL

SELECT u.UsageDate AS AtDate,
x.HoursAvailable - y.Yak
FROM #Usage AS u
OUTER APPLY (
SELECT TOP(1) m.MaintenanceDate,
m.HoursAvailable
FROM #Maintenance AS m
WHERE m.MaintenanceDate <= u.UsageDate
ORDER BY m.MaintenanceDate DESC
) AS x
OUTER APPLY (
SELECT SUM(q.HoursUsed) AS Yak
FROM #Usage AS q
WHERE q.UsageDate >= x.MaintenanceDate
AND q.UsageDate <= u.UsageDate
) AS y
)
SELECT AtDate,
HoursAvailable
FROM cteData
ORDER BY AtDate;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

tderoo
Starting Member

4 Posts

Posted - 2015-03-01 : 06:19:42

Thanks a lot Peter, that works brilliantly! And it is a nice study query for me :-) Had expected to need more nested sub queries in this solution. So I learned some new tricks again, thanks!

Theo de Roo
Mission Aviation Fellowship

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources