CODEWITHSUNDEEP
sql-servert-sqlpivot
Ray Brack
Ray Brack

Reputation: 293

Create Pivot from Column Values

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

Answers (3)

Anoop
Anoop

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

Ray Brack
Ray Brack

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

Gordon Linoff
Gordon Linoff

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

Related Questions