CODEWITHSUNDEEP
sqlsql-servert-sql
ChrisCurrie
ChrisCurrie

Reputation: 1619

SQL statement to get a total for a given date range

I am trying to get the number of bookings and their total value by date for every day within a given date range.

My table looks like:

BookingId (int)
BookingFare (decimal)
BookingDateTime (datetime)

I can convert BookingDateTime to a date only by using:

SELECT CONVERT(varchar(8), BookingDateTime, 112) as BookingDateOnly
FROM   [TaxiBookingOnline].[dbo].[Bookings]

What I'm after is something like this:

Date         Bookings   Value
2013-07-10   10         256.24
2013-07-11   12         321.44
2013-07-12   14         311.53

I get the feeling I should be aliasing the table, joining it to itself and then using 'GROUP BY' but I am failing to get this to work.

Any help would be much appreciated.

Thanks.

Upvotes: 0

Views: 353

Answers (4)

ayb
ayb

Reputation: 23

SELECT
    CONVERT(DATE,BookingDateTime) BookingDate,
    COUNT(BookingID) Bookings,
    SUM(BookingFare) BookingFare
FROM TaxiBookingOnline.dbo.Bookings

Upvotes: 0

Sonam
Sonam

Reputation: 3466

Following is the code. Replace Date1 and Date2 with the date range values:

SELECT 
CONVERT(varchar(8), BookingDateTime, 112) as BookingDateOnly, BookingID   Bookings,Sum(BookingFare)Value  
FROM 
[TaxiBookingOnline].[dbo].[Bookings]
WHERE BookingDateTime Between 'Date1' and 'Date2'
GROUP BY
BookingDateTime,BookingID

Upvotes: 0

Brad Christie
Brad Christie

Reputation: 101604

SELECT   CONVERT(VARCHAR(8), BookingsDateTime, 112) AS [Date],
         COUNT(*)  AS [Bookings],
         SUM(BookingsFare AS [Value]
FROM     MyTable
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, BookingDateTime))

Group by SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, dateColumn)) which will effectively get the date portion of the datetime, then you can use count or sum as necessary on the grouped values.

EDIT: If you're using SQL Server >= 2008, you can cast to date (like @AlexK has done) otherwise you have to hack around it using DATEADD.

Upvotes: 1

Alex K.
Alex K.

Reputation: 175766

How about 

select
   cast(BookingDateTime as date) [Date],
   count(*) [Bookings],
   sum(BookingFare) [Value]
from t
group by cast(BookingDateTime as date)

Upvotes: 2

Related Questions