Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4144
  • Last Modified:

Get Hourly count for a day from a SQL Table

Hi,
     I have a table of transactions data for a store. It has a field td_datetimstamp which stores the time at which the transaction takes place. And td_record_type stores the count of the customer. Now, I want to generate a report which has the customer count on an hourly basis. How do i write a query to return the count on an hourly basis when a date is passed as a parameter?

I want the Sql Stored Procedure. Thanks.
0
WebRules
Asked:
WebRules
  • 12
  • 6
  • 4
  • +4
1 Solution
 
dwkorCommented:
You can use something like that in SP. @ADate is parameter
select DATEPART(hour,td_datetimstamp) as [hour], COUNT(*)
from dbo.MyTable
where td_datetimstamp between CONVERT(datetime,FLOOR(convert(float,@ADate))) and CONVERT(datetime,FLOOR(convert(float,@ADate)) + 1)
group DATEPART(hour,td_datetimstamp)

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
WebRules,

You can use DatePart() to pull the hour of the day from the timestamp column and group by that.

i.e.,
SELECT DatePart(hh, td_datetimstamp) AS HourOfDay
     , SUM(td_record_type) AS TotalCustomerCount -- Or: COUNT(td_record_type)
FROM your_tx_table
WHERE td_datetimstamp >= @somedate AND td_datetimestamp < (@somdate+1)
GROUP BY DatePart(hh, td_datetimstamp)

Hope that helps!
0
 
Kevin CrossChief Technology OfficerCommented:
I wrote an Article on a function that I have used in these kinds of reports that may come in useful:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_669-TimeSerial-Function-for-Microsoft-SQL-Server.html
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Patrick MatthewsCommented:
WebRules,

Will you need zeroes for the hours in which there are no transactions?  Also, using between for a date criterion is risky: if you happen to have a transaction for the next day but logged exactly at midnight, the query above will count it.

Assuming you have an Hours table that lists the hours you always want results for, even if those results are zero...


SELECT @Date AS TheDate, h.[Hour], COUNT(t.td_datetimstamp) AS Customers
FROM tblHours h LEFT JOIN
    SomeTable t ON h.[Hour] = DATEPART(hour, t.td_datetimstamp)
GROUP BY h.[Hour]

Open in new window


Patrick
0
 
Patrick MatthewsCommented:
Sorry, missed the criteria...

SELECT @Date AS TheDate, h.[Hour], COUNT(t.td_datetimstamp) AS Customers
FROM tblHours h LEFT JOIN
    SomeTable t ON h.[Hour] = DATEPART(hour, t.td_datetimstamp)
WHERE t.td_datetimstamp >= CONVERT(datetime, CONVERT(varchar, @Date, 102)) AND
    t.td_datetimstamp < DATEADD(day, 1, CONVERT(datetime, CONVERT(varchar, @Date, 102)))
GROUP BY h.[Hour]

Open in new window

0
 
WebRulesAuthor Commented:
@dwkor: it is throwing syntax error at datepart. i am not able to figure out.
0
 
Kevin CrossChief Technology OfficerCommented:
select DATEPART(hour,td_datetimstamp) as [hour], COUNT(*)
from dbo.MyTable
where td_datetimstamp between CONVERT(datetime,FLOOR(convert(float,@ADate))) and CONVERT(datetime,FLOOR(convert(float,@ADate)) + 1)
group DATEPART(hour,td_datetimstamp)

The highlighted should be: group by DATEPART(hour,td_datetimstamp)
0
 
WebRulesAuthor Commented:
yeah fixed that. i got the query working but irrespective of the hour, it is returning 0 for hour. why is it like that?
0
 
Kevin CrossChief Technology OfficerCommented:
Is td_datetimestamp field truly a DATETIME data type or is it varchar?
0
 
WebRulesAuthor Commented:
sorry my bad. i fixed it. the time was 12:00 am, so got '0'.
Now I have a question about SSRS.
I created a rdl file, and binding hour and count fields. But instead of hour i want to display something like this...
for hour '1', i want it to be 1:00 am -2:00 am, for hour '2' i want that column in the report to be 2:00 a, - 3:00 am. How can i display like that?
0
 
SharathData EngineerCommented:
try this.
select STUFF(RIGHT(CONVERT(varchar,td_datetimstamp,0),7),3,3,' ') + ' - ' + 
       STUFF(RIGHT(CONVERT(varchar,DATEADD(hour,1,td_datetimstamp),0),7),3,3,' ')

Open in new window

an example
declare @date datetime
set @date = '01/01/2011 18:5:34'
select STUFF(RIGHT(CONVERT(varchar,@date,0),7),3,3,' ') + ' - ' + STUFF(RIGHT(CONVERT(varchar,DATEADD(hour,1,@date),0),7),3,3,' ')
--  6 PM -  7 PM

Open in new window

0
 
WebRulesAuthor Commented:
cant we set as an expression in rdl file's column?
0
 
WebRulesAuthor Commented:
I am getting this error:
Incorrect syntax near ') + '-' +  STUFF(RIGHT(CONVERT(varchar,DATEADD(hour,1,td_datetimstamp),0),7),3,3,'.
0
 
SharathData EngineerCommented:
I am not sure if this expression can be placed in an rdl file.
It seems like you missed the last parenthesis when you execute my query.
0
 
WebRulesAuthor Commented:
i am getting the error in the first line, not the second one. just before the '+' symbol.
0
 
SharathData EngineerCommented:
Did you try executing my example query?
0
 
WebRulesAuthor Commented:
yeah that worked perfect. actually i am using dynamic sql. so does the syntax change for that?
0
 
SharathData EngineerCommented:
For dynamic sql, you should include a single for quote for every single quote. post your dynamic sql.
0
 
WebRulesAuthor Commented:
yeah i did that.

The code is below.
And the error i get is:

Incorrect syntax near ') + ' - ' +
       STUFF(RIGHT(CONVERT(varchar,DATEADD(hour,1,td_datetimstamp),0),7),3,3,'')

N'select STUFF(RIGHT(CONVERT(varchar,td_datetimestamp,0),7),3,3,' ') + '' - '' + 
       STUFF(RIGHT(CONVERT(varchar,DATEADD(hour,1,td_datetimestamp),0),7),3,3,' ') as hour, COUNT(*) as count
from '+@tablename+'
where td_datetimestamp between CONVERT(datetime,FLOOR(convert(float,@date))) and CONVERT(datetime,FLOOR(convert(float,@date)) + 1)
AND td_record_type=''7''
group by DATEPART(hour,td_datetimestamp)'

Open in new window

0
 
SharathData EngineerCommented:
Check this.
 N'select STUFF(RIGHT(CONVERT(varchar,td_datetimestamp,0),7),3,3,'' '') + '' - '' + 
       STUFF(RIGHT(CONVERT(varchar,DATEADD(hour,1,td_datetimestamp),0),7),3,3,'' '') as hour, COUNT(*) as count
from '+@tablename+'
where td_datetimestamp between CONVERT(datetime,FLOOR(convert(float,@date))) and CONVERT(datetime,FLOOR(convert(float,@date)) + 1)
AND td_record_type=''7''
group by DATEPART(hour,td_datetimestamp)'

Open in new window

0
 
WebRulesAuthor Commented:
i am getting an error now. syntax is fixed. what does this error mean?

Msg 8120, Level 16, State 1, Line 3
Column 'sp.td_datetimestamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 4
Column 'sp.td_datetimestamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
SharathData EngineerCommented:
try this.
N'select STUFF(RIGHT(CONVERT(varchar,td_datetimestamp,0),7),3,3,'' '') + '' - '' + 
       STUFF(RIGHT(CONVERT(varchar,DATEADD(hour,1,td_datetimestamp),0),7),3,3,'' '') as hour, COUNT(*) as count
from '+@tablename+'
where td_datetimestamp between CONVERT(datetime,FLOOR(convert(float,@date))) and CONVERT(datetime,FLOOR(convert(float,@date)) + 1)
AND td_record_type=''7''
group by STUFF(RIGHT(CONVERT(varchar,td_datetimestamp,0),7),3,3,'' '') + '' - '' + 
       STUFF(RIGHT(CONVERT(varchar,DATEADD(hour,1,td_datetimestamp),0),7),3,3,'' '')'

Open in new window

0
 
Nico BontenbalCommented:
Can't you use this expression in your query
dateadd(hour,datepart(hour,td_datetimestamp),0)

Open in new window

to get the hour of the timestamp back as a date. Then use the format property of the textbox to show only the time in the desired format?
0
 
WebRulesAuthor Commented:
hey it works perfect. thanks sharath.
0
 
WebRulesAuthor Commented:
but when i bind it to the report, it doesnt show the time range but displaying 'false' there..why like that?
0
 
WebRulesAuthor Commented:
never mind..it works..thanks!
0
 
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 12
  • 6
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now