Reputation: 293
I have a number of records for equipment contracts and need to display the total available days by year for each piece of equipment and contract.
For example, the table may contain multiple contracts spanning different periods for the same piece of equipment and I need to show how many days the equipment was available for each year.
The table structure looks like;
CREATE TABLE [dbo].[tblContracts]
(
[ID] [int] NOT NULL IDENTITY(1,1) ,
[EquipmentId] [int] NOT NULL,
[ContractTypeId] [int] NOT NULL,
[FromDt] [datetime] NOT NULL,
[SpudDt] [datetime] NULL,
[ToDt] [datetime] NOT NULL
)
The result I need would look like;
EquipmentId 2012 2013 2014 2015
1 120 365 180 30
I have a function that, given the Equipment ID and Year, will return the total number of days the equipment was available for the specified year however, except for creating some dynamic SQL, I'm not sure how to return the data in the format required.
Does anyone have any suggestions?
Thanks in advance.
Upvotes: 0
Views: 76
Reputation: 369
Dear Ray Brack this one will work, I got correct answer with your table structure. Note that you should subtract 366 in case of Leap Year.
SELECT T.EquipmentId,
365 - T.[2013] AS [2013],
365 - T.[2014] AS [2014],
365 - T.[2015] AS [2015]
FROM (SELECT EquipmentId,
[2013],
[2014],
[2015]
FROM (SELECT EquipmentId,
Datepart(Year, ToDt) AS Yr,
Datediff(Day, FromDt, ToDt) AS Diff
FROM tblContracts
) X PIVOT
( Sum(Diff)
FOR Yr IN([2013],
[2014],
[2015])
)P
)T
Result
--------------------------------------------
EquipmentId | 2013 | 2014 | 2015
--------------------------------------------
1 | 351 | 337 | 337
Upvotes: 0
Reputation: 293
I ended up having to use dynamic SQL to resolve the issue though it does seem to be a pity there is no pivot like function that perform this relatively simple requirement.
Upvotes: 0
Reputation: 1271241
Your question is a bit vague. For instance, what does available mean? What is the function?
This following may do what you need:
select EquipmentId,
sum(MyFunc(EquipmentId, 2012)) as [2012],
sum(MyFunc(EquipmentId, 2013)) as [2013],
sum(MyFunc(EquipmentId, 2014)) as [2014],
sum(MyFunc(EquipmentId, 2015)) as [2015]
from tblContracts c
group by EquipmentId;
Upvotes: 2