CODEWITHSUNDEEP
sqlsql-servert-sql
wafw1971
wafw1971

Reputation: 361

Create a New Calculated Column

I know this is going to be easy but I cannot remember how to do it, I have query see below where I want to add a new column called Total Capacity, This is my datediff result * my Capcity. But for the life of me I cannot figure it out. Can you help?

SELECT
  Capacity.StartDate
  ,Capacity.EndDate, DATEDIFF(d,Capacity.StartDate
  ,Capacity.EndDate)+1 AS DaysOpen
  ,Capacity.Capacity
  ,Capacity.Site_Skey
FROM
  Capacity

the result

  StartDate   EndDate       DaysOpen   Capacity TotalCapacity
  01/05/2010  30/09/2010    153        46       7038

Upvotes: 0

Views: 2205

Answers (4)

marc_s
marc_s

Reputation: 754268

You can easily add those two columns to your table - as computed, persisted columns. Then you'll have them available for easy selection - they're new column in your table, that you can select like the other columns - and you can even put an index on them, if you need to!

ALTER TABLE [dbo].[Capacity]
    ADD DaysOpen AS DATEDIFF(d, StartDate, EndDate) PERSISTED

ALTER TABLE [dbo].[Capacity]
    ADD TotalCapacity AS DATEDIFF(d, StartDate, EndDate) * [Capacity] PERSISTED

Now you can issue:

SELECT
    StartDate, EndDate,
    DaysOpen,
    Capacity,
    TotalCapacity
FROM
    Capacity

Update: if you want to use the DATEDIFF+1 approach - use these column specifications:

ALTER TABLE [dbo].[Capacity]
    ADD DaysOpen AS (DATEDIFF(d, StartDate, EndDate) + 1) PERSISTED

ALTER TABLE [dbo].[Capacity]
    ADD TotalCapacity AS (DATEDIFF(d, StartDate, EndDate) + 1) * [Capacity] PERSISTED

Upvotes: 5

SP007
SP007

Reputation: 1921

SELECT
  Capacity.StartDate
  ,Capacity.EndDate
  ,DATEDIFF(d,Capacity.StartDate
  ,Capacity.EndDate)+1 AS DaysOpen
  ,Capacity.Capacity
  ,Capacity.Site_Skey
  ,(DATEDIFF(d,Capacity.StartDate
  ,Capacity.EndDate)+1 ) * Capacity.Capacity
FROM
  Capacity

Upvotes: 1

xlecoustillier
xlecoustillier

Reputation: 16351

Something like this ?

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

Upvotes: 2

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

SELECT
  Capacity.StartDate
  ,Capacity.EndDate
  ,DATEDIFF(d,Capacity.StartDate ,Capacity.EndDate)+1 AS DaysOpen
  ,Capacity.Capacity
  ,(DATEDIFF(d,Capacity.StartDate ,Capacity.EndDate)+1) * Capacity.Capcity as TotalCapacity
  ,Capacity.Site_Skey
FROM
  Capacity

Upvotes: 2

Related Questions