Hourly Breakdown

[timferris]

Hi there,

I'm trying to write some SQL that will run through a table I have, adding up the values in one column, for each hour they were logged.

For example, 8-9am 3 calls logged
9-10am 6 calls logged

 

[rajeevnandanmishra]

Hi timferris,
I don't know the exact usage, but may be this is what you are looking for.
Suppose your table is having this structure:
CREATE TABLE myTable (TimeHour INT, Nf_Calls INT)
Then create this procedure
CREATE PROCEDURE updCallLog AS
DECLARE @pTimeHour INT
SELECT @pTimeHour=CONVERT(INT,CONVERT(char(2),getdate(),108))
IF EXISTS (SELECT * FROM myTable WHERE TimeHour=@pTimeHour)
UPDATE myTable
SET Nf_Calls = Nf_Calls + 1
WHERE TimeHour=@pTimeHour
ELSE
INSERT myTable VALUES (@pTimeHour, 1)

Now whenever you want to update your table , just issue the command UpdCallLog


You may have to make changes in the procedure depending on your actual requirement. If further you face any problem, just keep posted.

 

[tlbroadbent]

Use a simple query to return the results you want.

Select
datepart(hh,LogTime) As LogHour,
sum(CallCount) As TotCount
From table
Group By datepart(hh,LogTime)

You could insert the result into another table, display, or process it however you need. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[timferris]

Hey thanks for that, Terry yours is the one that I am going to go with. Thanks also rajeevnandanmishra

Regards

Tim