Grouping COUNT By HOUR

Apr 1, 2008

Hi, I'm hoping you guys can help me with a query.

I am trying to write a SELECT statement to return the number of calls received by a Call Centre, grouped by the hour that they are received (i.e to reflect peaks and troughs throughout the 24 hr day). So, my SELECT statement is as follows:


SELECT DATEPART(HOUR, RecvdTime) AS [Hour of Day], COUNT(CallID) AS [Total Calls]

FROM Table1

GROUP BY DATEPART(HOUR, RecvdTime)

ORDER BY DATEPART(HOUR, RecvdTime)

However, the problem of course with this statement is that for Hours where there are no Calls (i.e a COUNT of zero) there is no result returned at all. What I am trying to acheive is for the query to return a result for all hours of the day, and to display a 0 for a particular hour if appropriate.

Can anybody guide me as to an approach for this?

Many thanks
Matt

View 13 Replies


ADVERTISEMENT

Count Of Transaction Per Hour For A Day

Sep 7, 2015

I am new in SQL Server and creating some SQL script that should give total transaction count per hour for a day. I need simply hour and count result. My table contains DateCreated column and unique ID column.

View 5 Replies View Related

Grouping Count Question

Jun 5, 2008

Hello,

I have a query that in the end is to return totals by racial groups. In addition to grouping and counting on race I want to group based on percents. For example I want totals for all the racial groups for <=7%, >7% - <=9%, and >9%. The percents are stored in the hga1cpercent field which is also referenced when the race count is got but it isn't grouping on the hga1cpercent because I am not sure how to set up a grouping that requires columns be assigned to different groups. Is that possible? The percents are actually stored as numbers so 7 not 7% if that matters. Any help would be greatly appreciated! :)

With Diabetes(person_id) as
(
select distinct person_id from
(select count(*) as countperson,a.person_id,a.date_of_birth,
(2008-year(a.date_of_birth)) as age
from person a
join diabetes_ b on a.person_id = b.person_id
join patient_encounter c on a.person_id = b.person_id
where year(c.create_timestamp) = '2008'
group by a.person_id,a.date_of_birth)tmp
where tmp.age >=18 and tmp.countperson >=2
)
select count(*), b.race,a.hga1cpercent,max(a.create_timestamp)
from Diabetes tmp
join diabetes_flwsheet_ a on tmp.person_id = a.person_id
join person b on tmp.person_id = b.person_id
group by b.race,a.hga1cpercent

View 2 Replies View Related

SQL Server 2012 :: Query To Count How Many Sessions Are Active And Remain Active Per Hour

Jan 22, 2015

I have a table with the following columns employeeSessionID, OpDate, OpHour, sessionStartTime, sessionCloseTime. I need to see how many users remain active per hour. I can calculate how many logged in per hour, but I am stumped on how to count how many are active per hour. I have a single table that stores login data. I have created a query that pulls out the only the data needed from the table into a temp table using this query. Also note it is possible that the sessionCloseTime is null if the device has not been logged out this would need to be counted a active.

TABLE NAME #empSessionLog Contains the time stamp data OpDate, sessionStartTime and sessionCloseTime.
OpDatesessionStartTimesessionCloseTime
2015-01-202015-01-20 14:32:59.1302015-01-20 14:33:14.6299166
2015-01-202015-01-20 06:58:33.7302015-01-20 15:27:16.9133442
2015-01-202015-01-20 09:56:22.8402015-01-20 17:56:29.7555853
2015-01-202015-01-20 05:59:18.6132015-01-20 14:05:19.0426707

[code]....

can see how many sessions logged in per hour with the following statement:

SELECT
opDate,
FORMAT(DATEPART(HOUR, sessionStartTime), '00') AS opHour,
Count(*) AS Total
FROM #empSessionLog
Group BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00')
Order BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') ASCResults:
opDateopHourTotal
2015-01-20041

[code]....

Where I am stuck is how do I count the sessions that remain active per hour until the session is closed with the sessionCloseTime.

View 5 Replies View Related

Counting Popularity, Hour By Hour

Nov 14, 2001

I have a table that is recording hits to a website. Everytime someone views a page, the datetime of the hit is recorded in a field called hit_date_time. I would like to be able to come up with a query that will show how many hits occured on a given day or given days, broken down by hour.

The resulting table for two days would look something like:
Time Hits
1/1/01 12:00 1
1/1/01 1:00 23
1/1/01 2:00 54
1/2/01 1:00 15
1/2/01 2:00 14

I can't seem to figure out how to write the query so that I can take into consideration the date and hour of the event so that I can count it.

Thanks,

Eron

View 2 Replies View Related

Query Or Grouping Problem (some Kind Of Parallel Grouping?)

Nov 26, 2007

I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.

I'm trying to get information like this in to a report:

WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc

I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?

There are 4 tables:

Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions

The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:

WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc


Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR

It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:

SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]

View 1 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

DATE AND HOUR

May 4, 2006

Hello people,
I'm migrating an application from asp to asp.net and access to ms sql 2005. On the old db, there's a table with a column to store date and a column to store time. Ms sql only works with date and time together, right? I created a query to get the time from time column and update the date column inserting the time. I did something like this:
UPDATE    S_ACC_MONEYSET              Date_Oper = CONVERT(varchar, Date_Oper, 101) + ' ' + CONVERT(varchar, Time_Oper, 108)
Does anyone knows a better way to do that? Any other comments?
Thanks!

View 1 Replies View Related

24 Hour Environment

Sep 29, 1999

I have about 23 SQL servers running 6.5 SP3 or SP5a in a 24 hour environment. Most of the activity takes place between 6am and 11pm, with few transaction after 11pm. What is best to do with the main DB's transaction log, have the truncate at checkpoint option checked OR back up the transaction log a couple times a week? The Database is backed up every 6-8 hours.

Thanks in advance for your opinions/help.

LN

View 3 Replies View Related

Log Is Going To Backup Itself Every One Hour ,WHY?

Jul 23, 2004

Hi all,
We have many messages in SQL server logs that say 'Log backed up:Database:COM,creation date(time):2003/03/26(19:41:58),first'
why is this message appeared every one hour?

View 3 Replies View Related

Hour From A Date

Oct 28, 2006

How to get hour from a date

Msg_Time = 10/28/2006 12:20:00 AM

here how to get hour, minuts

see for month we are using month(Msg_Time),
for year we are using year(Msg_Time) same way how to fetch the hour and minutes

all are appreciate

View 1 Replies View Related

Transactions Per Hour

Jul 31, 2007

Hi All,
I have several servers each with an instance of SQL Server with a number of databases in each instance. We are a law firm dealing mostly with documents (Modifying and creating new). Is there an easy way to determin how many transactions the transaction log handles per hour? Not all tables have a create date or modify date. Is there some way to monitor the transaction logs them selves?
Thanks,
Roy

View 1 Replies View Related

Max Orders Placed By Hour

May 22, 2007

I have a pretty straight forward fact table that contains order information like this:



DateTimeStamp Price OrderID



I have a measure that does a simple row count for orders placed. How can I get the Max Orders Placed grouped by Hour? Something similar to this:



0:00 15

1:00 25

2:00 50

.....

23:00 75



I tried to do a max function like max([time].[hour], [measures].[Order Row Count]) but ended up with only one value. I know I'm doing it wrong.



I appreciate any help on this.

View 1 Replies View Related

Increment Hour By 1

Apr 27, 2007

Hey guys,

I am doing a project that has start time and end time. i was given only the start time of cases, but not the end time.

for example:



start time

01:30:00

10:00:00

09:24:00

11:25:00

.

.

.

.

It was like, thousands of start time. I need to create an end time (smalldatetime) which is one hour passed the start time. can anyone tell me how to do it in sql query? thanks.



Jul

View 1 Replies View Related

Add One Hour To A Datetime-value

Dec 29, 2007

Hi

I'm trying to create a query, where I add one hour to a value (I marked it below with "+ 1 HOUR"). I can't find any informations, how I can do this.




Code Block
"SELECT field1, enddate, (enddate < GetDate() FROM table WHERE (enddate + 1 HOUR) < GetDate()"





Does anybody can help me with this problem?

Kind regards
Roland

View 3 Replies View Related

Group By Hour, Month Etc

Apr 7, 2007

Hi All,
I have an orders table which has a filed called OrderTime which is the exact time when the order was received ? I need to write the following queries
1. Get number of orders per year
2. Get number of orders per month (obviously if a month (for example, november) is in a different year, its to be in a different row)
3. Get number of orders per hour 
Note: If there are no orders in a year etc, it should return a row with 0.
 

View 5 Replies View Related

Get Hour In HH24 Format

Aug 30, 2001

How do I get the hour in HH24 format from a datetime value?

Many Thanks.

View 3 Replies View Related

Counting Items Per Hour

Nov 2, 2005

I am trying to get a count of how many times a badge is entered in each hour it appears. In other words, I would like to find out how many times badge 3333 comes up in each hour of the day, the same with badge 4532.

The task at hand is to find out productivity for items processed per hour per badge number. I know I will need to take the hour out of timedate using datepart, but the results do not look right. Would anyone have any ideas on this? Thanks!




the table I have looks like this:

IDBadgeOrdNumberTimeDate
6112133333781267941/10/05 9:19
6112084532781275591/10/05 9:21
6111973333781265611/10/05 9:29
6111903333781261671/10/05 9:30
6111774532781253021/10/05 9:38
6111693333781257811/10/05 9:40

View 4 Replies View Related

Extract And Compare Hour

Jan 22, 2007

Hi all:

As I can extract the hour values and minute of a field of type datetime to compare it with the values of a field of type smalldatetime of another table

Thanks.:confused:

View 1 Replies View Related

Rond Time Down To Hour

Aug 5, 2007

I want to round times down to the previous hour,
For example a call time of 7/31/07 11:51:13.532 would become 7/31/07 11:00:00.000.

View 14 Replies View Related

Forcing Every Hour Of The Day To Show?

Dec 3, 2013

I have the following Case statement:

CASE
WHEN CAST(wo.start_date AS TIME) BETWEEN '00:00:00' AND '00:59:59' THEN 0
WHEN CAST(wo.start_date AS TIME) BETWEEN '01:00:00' AND '01:59:59' THEN 1
WHEN CAST(wo.start_date AS TIME) BETWEEN '02:00:00' AND '02:59:59' THEN 2
WHEN CAST(wo.start_date AS TIME) BETWEEN '03:00:00' AND '03:59:59' THEN 3
WHEN CAST(wo.start_date AS TIME) BETWEEN '04:00:00' AND '04:59:59' THEN 4

[code]....

The purpose is to take a row and set it to the hour of the day that it occurred in. This works fine, however I would like to force it to display every hour 0-23 regardless of whether or not it has a corresponding row.

So, if no row exists for 0, display 0 with null values for the rest of the columns.

View 2 Replies View Related

Get Date With Specific Hour

Jul 8, 2014

from getdate(), how can i get today's date with specific time :

For example : (i just need specific 11oclock today)
2014-07-08 11:00:00.000

This do the trick :
select DATEADD( HH, 11, CAST(CAST(DATEADD(DAY,-1,GETDATE()) AS INT) AS DATETIME))

But at second half of the day it changed from 2014-07-08 11:00:00.000 to the next day, i dont know why who controls the past controls the future, who controls the present controls the past. ¯(º_o)/¯ ~~~

View 1 Replies View Related

Query Report By HOUR

Sep 11, 2014

I have created a report in visual studio 2013 that talks with my SQL server through data connections. I want to be able to query my report BY HOUR. Meaning, I want the report to gather values at 14:30, 22:30, and 06:30. Here is my nonworking code so far.

AND DATEPART(hh,[datetime]) = 0630 AND DATEPART(hh,[datetime]) = 1430 AND DATEPART(hh [datetime]) = 2230

View 13 Replies View Related

SQL Hour Scheduling Program?

Nov 24, 2005

Does anyone know where to purchase a good (or great) SQL hour scheduler program, and pulls up information from a SQL server and puts this information into a schedule (hourly schedule). I'm looking for something that will show the schedule that people work, the information off the SQL server needed is the "agent" name. If anyone could help me, that would be greatly appreciated.

Thanks.

View 1 Replies View Related

6 Hour Stored Procedure

Feb 15, 2007

One of our programmers has a stored procedure that joins two views into a new table.

One of the views that it is grabbing is fairly complicated pulling from several tables, and can take quite awhile to run on its own. This stored procedure has been running on several databases for several months just fine.

After switching to sql server 2005 it started taking almost 6 hours in one of the databases.

As of now, it runs fine in one of the dbs in a few minutes and produces 50,000 records. The db that it takes almost 6 hours to complete produces only 17,000 records. The programmer has restricted the fields and gets it to run in under a minute - but it doesn't seem to matter which fields, just how many.

After 3 or 4 of the records from the complex view, it goes from running in 1 min, to almost 6 hours again - no in between. It is the same code as the database where it runs fine and produces more records.

Any thoughts would be greatly appreciated.

Dave

View 7 Replies View Related

Backup The Database Every Hour

Nov 28, 2007

Hi ,

I want to backup the database per hour but not fully only the change that has been made from last one hour.Every hour I want the update data base is it possible.

Abhi

View 3 Replies View Related

Calculating Salary Per Hour?

Jan 26, 2008

is there a way to create a SELECT clause which counts the accumulate hours from tw columns in same row (entering hour and leaving hour) and then calculating the total price according to a parameter?

Shimi

View 2 Replies View Related

Query Every Entry Not In Each Hour

Jul 17, 2007

Hello,I have been having a tough time writing the follow requirement for aquery.On a table that the primary key is a tagId and an hourly timestamp, Iwould like to find out for every hour which tags did not get enteredinto the database. Essentially I am looking for patterns of entriesthat are not making it into tableB.Examples of the tables:TableA TableBTagID and TagName TagId TimestampPK PK1 PK2approx 6000 rows approx 6000 rows per hourI am thinking that I will need to do something like:Select tableB1.time, count(*) from tableB1 group by tableB1.timehaving tableB1.time >= XXXX and tableB1.time <= XXXX and tableB1.tagIdnot in (select tagId from tableA where not exists (selecttableA.tagId, distinct.tableB2.time from tableB2)I have been trying to create an effecient query handle this but havenot had any luck. Any assistance would be more then appreciated.Thanks,Andy

View 1 Replies View Related

Add An Hour To A Timestamp Column

Mar 27, 2008

I have a table with a [Timestamp] field which is a datetime data type. What I need to do is add one hour to the timestamp.
Each entry in the [Timestamp] field looks like this: 2008-03-09 16:44:06.313

What is the best way to do this?

(Should I be using UPDATE [Tablename] or Alter Table [Tablename])


View 4 Replies View Related

How To Subtract 1 Hour From GetDate() Function

Sep 15, 1999

Can any one tell me how to subtracts and add hours to the current date and time?
In my case I have to store the Vancouver date and time in Toronto.
Thanks,
T.Lingam

View 1 Replies View Related

Gurus Help!! How To Sort By Hour And By Day Of Time?

Jan 21, 2005

I need to query data from a table which has order_date which is datetime field. How to pull orders by hour by day per month for the last 4 months. Any idea how to write the query?

Sample data:

order_dt order_no
2004-02-04 18:18:19.800 100
2004-02-04 18:18:22.677 101
2004-02-05 06:30:23.300 102
2004-02-05 06:31:39.533 103
2004-02-05 06:32:45.377 104

View 6 Replies View Related

Strip Off Hour, Minute, Second From A Datetime?

Jan 18, 2005

I would like something I can do inline eg:

select convert(blahdatatype,a.datefield) as smallerdatefield
from
a

where a.datefield is a datetime. If a contains rows like:

datefield
---------------------
01/20/2005 22:17:23
08/23/2001 03:04:15
...

Then the SQL above returns:

smallerdatefield
---------------------
01/20/2005 00:00:00
08/23/2001 00:00:00
...

Is there any non-obnoxious way (eg: without have to result to using datepart a million times) to do this? For instance, Oracle provides a function called Trunc which does it, but I cannot find an SQL Server equivalent. Anyone? TIA!!!

View 9 Replies View Related

How To Get Date And Hour In Column Of Table

Nov 25, 2011

I have a Date column in my table.I want to insert the Date and hour in that Column.

Here is the sample data 2011/11/25 11 AM

The Text in Bold i.e 11 Am is how the hour part i want.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved