Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Sum transactions by hour.

Posted on 2009-04-23
7
Medium Priority
?
644 Views
Last Modified: 2012-05-06
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
Comment
Question by:pazzaTim
7 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 500 total points
ID: 24218135
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

by:Anthony Perkins
Anthony Perkins earned 500 total points
ID: 24218161
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

by:Anthony Perkins
ID: 24218167
And please, please change the float data type for amount to something more appropriate such as money, smallmoney or numeric.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 24218174

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
 
LVL 60

Expert Comment

by:chapmandew
ID: 24218192
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

by:pazzaTim
ID: 31573922
First two were not complete.  Third did the job!   Fastest response on all three ever!  Less than 5 minutes.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24218215
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Video by: Steve
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

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

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

Join & Ask a Question