CODEWITHSUNDEEP
t-sqlsum
wafw1971
wafw1971

Reputation: 361

Sum using a Datediff

I should know this but I cannot remember, how do I add another column to show AmountDays* PitchValues.Value = results.

SELECT DATEDIFF(day, Bookings.ArrivalDate, Bookings.DepartureDate) AS AmountDays, 
   PitchValues.Value
FROM   Bookings 
INNER JOIN PitchValues 
   ON Bookings.PitchType_Skey = PitchValues.PitchType_Skey

Results so far

AmountDays PitchValues.Value   BookingValue
    15           £15                
    7            £9
    8            £25

Upvotes: 0

Views: 63

Answers (1)

SQLGuru
SQLGuru

Reputation: 1099

SELECT        
  DATEDIFF(day, Bookings.ArrivalDate, Bookings.DepartureDate) AS AmountDays, 
  PitchValues.Value,
  (DATEDIFF(day, Bookings.ArrivalDate, Bookings.DepartureDate)) * PitchValues.Value AS YourCol
FROM            
  Bookings INNER JOIN
  PitchValues ON Bookings.PitchType_Skey = PitchValues.PitchType_Skey

alternatively, and only for making it look pretty:

SELECT
  P0.AmountDays,
  P0.Value,
  (P0.AmountDays * P0.Value) as YourCol
FROM
  (
    SELECT        
      DATEDIFF(day, Bookings.ArrivalDate, Bookings.DepartureDate) AS AmountDays, 
      PitchValues.Value
    FROM            
      Bookings INNER JOIN
      PitchValues ON Bookings.PitchType_Skey = PitchValues.PitchType_Skey
  ) P0

you also might want to perform ISNULL(,0) around your columns in the event there is a null, the entire value will become null. Like so:

SELECT
  P0.AmountDays,
  P0.Value,
  (P0.AmountDays * P0.Value) as YourCol
FROM
  (
    SELECT        
      ISNULL(DATEDIFF(day, Bookings.ArrivalDate, Bookings.DepartureDate), 0) AS AmountDays, 
      ISNULL(PitchValues.Value, 0) as Value
    FROM            
      Bookings INNER JOIN
      PitchValues ON Bookings.PitchType_Skey = PitchValues.PitchType_Skey
  ) P0

Upvotes: 1

Related Questions