CODEWITHSUNDEEP
sqlt-sql
wafw1971
wafw1971

Reputation: 361

Grand Total from DATEDIFF

Another easy one that I haven't got a clue on how to solve and was wondering if you could help. I need to total up 1.4 million rows of data, each row has a amount of PitchNights, Row 1 = 2, Row 2 = 26, Row 14000 = 3 etc, so if i only had these 3 rows the total would be 31. What I need to is have a grand total of all Pitch nights between 2 set dates. I tried the group by function but that didn't work.

SELECT
  Bookings.BookingNumber
  ,Bookings.ArrivalDate
  ,Bookings.DepartureDate, DATEDIFF(d,Bookings.ArrivalDate
  ,Bookings.DepartureDate) AS 'PitchNights'
FROM
  Bookings
WHERE Bookings.ArrivalDate >=@ArrivalDate AND Bookings.DepartureDate <=@DepartureDate

Thanks

Wayne

Upvotes: 0

Views: 55

Answers (1)

John Woo
John Woo

Reputation: 263693

SELECT  SUM(DATEDIFF(d,Bookings.ArrivalDate,Bookings.DepartureDate)) AS 'PitchNights'
FROM    Bookings
WHERE   Bookings.ArrivalDate >=@ArrivalDate AND 
        Bookings.DepartureDate <=@DepartureDate

Upvotes: 3

Related Questions