Solved

# SQL Sum transactions by hour.

Posted on 2009-04-23
637 Views
We have a table (billingTransactions)

Important fields are:

idCharge int
TimeStamp dateTime
Staff nvarchar
Amount float

What I need is to be able to pass in a date and get back sales by hour and a second report of sales by hour by staff.

Thanks.
0
Question by:pazzaTim

LVL 60

Assisted Solution

1.

declare @x datetime
set @x = '4/1/2009'

select datepart(hh, timestamp), count(*)
from billingTransactions
where timestamp >= @x
and timestamp < dateadd(d, 1, @x)
group by datepart(hh, timestamp)
go
2.
declare @x datetime
set @x = '4/1/2009'

select datepart(hh, timestamp), staff, count(*)
from billingTransactions
where timestamp >= @x
and timestamp < dateadd(d, 1, @x)
group by datepart(hh, timestamp), staff
0

LVL 75

Assisted Solution

Select      DATEPART(hour, [TimeStamp]) [Hour],
SUM(Amount) Sales
From      billingTransactions
Where      [TimeStamp] >= @Date And [TimeStamp] < @Date + 1
Group By
DATEPART(hour, [TimeStamp])

Select      Staff,
DATEPART(hour, [TimeStamp]) [Hour],
SUM(Amount) Sales
From      billingTransactions
Where      [TimeStamp] >= @Date And [TimeStamp] < @Date + 1
Group By
Staff, DATEPART(hour, [TimeStamp])
0

LVL 75

Expert Comment

And please, please change the float data type for amount to something more appropriate such as money, smallmoney or numeric.
0

LVL 59

Accepted Solution

``````SELECT DATEPART(hour, TimeStamp) AS Hour

, SUM(Amount) AS Sales

FROM billingTransactions

WHERE DATEADD(day, 0, DATEDIFF(day, 0, TimeStamp)) = '04/23/2009'

GROUP BY DATEPART(hour, TimeStamp)

SELECT DATEPART(hour, TimeStamp) AS Hour

, Staff

, SUM(Amount) AS Sales

FROM billingTransactions

WHERE DATEADD(day, 0, DATEDIFF(day, 0, TimeStamp)) = '04/23/2009'

GROUP BY DATEPART(hour, TimeStamp), Staff
``````
0

LVL 60

Expert Comment

if you also want the sum, along with the count:

1.

declare @x datetime
set @x = '4/1/2009'

select datepart(hh, timestamp), count(*) as prodcount, sum(amount) as salesamount
from billingTransactions
where timestamp >= @x
and timestamp < dateadd(d, 1, @x)
group by datepart(hh, timestamp)
go
2.
declare @x datetime
set @x = '4/1/2009'

select datepart(hh, timestamp), staff, count(*) as prodcount, sum(amount) as salesamount
from billingTransactions
where timestamp >= @x
and timestamp < dateadd(d, 1, @x)
group by datepart(hh, timestamp), staff
0

Author Closing Comment

First two were not complete.  Third did the job!   Fastest response on all three ever!  Less than 5 minutes.
0

LVL 60

Expert Comment

actually, my first one was complete...you really didn't specify the sales that you wanted...I gave you count as the number of sales instead of the amount sold...
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
Creating and Managing Databases with phpMyAdmin in cPanel.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of demâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦

#### 730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!