CODEWITHSUNDEEP
sqlt-sql
wafw1971
wafw1971

Reputation: 361

Totalling a multiple Datediff columns

I hope you can help, I need to total the TotalCapacity column to give me a years total based of the Where Statement. I will also need to see different years next to each other. Thanks

SELECT
    Capacity.StartDate,
    Capacity.EndDate,
    DATEDIFF(d, Capacity.StartDate, Capacity.EndDate) + 1 AS DaysOpen,
    Capacity.Capacity,
    (DATEDIFF(d, Capacity.StartDate, Capacity.EndDate) + 1) * Capacity.Capacity AS TotalCapacity,
FROM
    Capacity
WHERE 
    Capacity.StartDate >= '01 Jan 2010' 
    AND Capacity.EndDate <= '31 Dec 2010'

Upvotes: 1

Views: 52

Answers (1)

Sergio
Sergio

Reputation: 6948

Guess you need some kind of this:

SELECT SUM(TotalCapacity)
    FROM
    (
        SELECT
          Capacity.StartDate
          ,Capacity.EndDate
          ,DATEDIFF(d, Capacity.StartDate, Capacity.EndDate) + 1 AS DaysOpen
          ,Capacity.Capacity
          ,(DATEDIFF(d, Capacity.StartDate, Capacity.EndDate) + 1) * Capacity.Capacity AS TotalCapacity
        FROM
          Capacity
          WHERE Capacity.StartDate >= '01 Jan 2010' AND Capacity.EndDate <= '31 Dec 2010'
    ) t

Upvotes: 1

Related Questions