• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 652
  • Last Modified:

SQL Sum transactions by hour.

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
pazzaTim
Asked:
pazzaTim
3 Solutions
 
chapmandewCommented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
And please, please change the float data type for amount to something more appropriate such as money, smallmoney or numeric.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Kevin CrossChief Technology OfficerCommented:

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

Open in new window

0
 
chapmandewCommented:
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
 
pazzaTimAuthor Commented:
First two were not complete.  Third did the job!   Fastest response on all three ever!  Less than 5 minutes.
0
 
chapmandewCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now