Solved

SQL query for records added in the last hour?

Posted on 2007-03-24
11
1,936 Views
Last Modified: 2008-01-09
I have a call logger, and there's a date and another time field.  The time field is formatted like 01/01/1900 15:36:23.

How can I just count all of the calls received in the last 60 minutes?

Thanks

Lapchien
0
Comment
Question by:Lapchien
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 18785958
SELECT COUNT(*) FROM myTable WHERE datefield >= dateadd(minute,-60,getdate())
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18785960
assuming the field is indeed date/time data type (and not varchar):
select count(*) from yourtable where yourfield > dateadd(hour, -1, getdate())
0
 

Author Comment

by:Lapchien
ID: 18785968
That results in 0

To clarify, the time field is type datetime.  There is a different field in the db for the date, called date.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18785974
datetime datatype includes date and time, therefore the time should always include the date.
0
 

Author Comment

by:Lapchien
ID: 18785977
So do I have to concat the date and time fields?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 29

Expert Comment

by:Nightman
ID: 18785983
You should not have to - datetime datatype includes both.

What do you store in the time field? Please can you post an example of the data.
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 18785991
The logger seems to write date as date-only, setting time to 0:0:0 (this is often seen).
In this case:
select count(*) from yourtable where date+convert(float, time) >= dateadd(hour, -1, getdate())
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18785992
select count(*)
  from yourtable
 where convert(datetime,convert(char(8),Date,112) + ' '+convert(char(8),Time,108))
                between  dateadd(nn,-60,getdate())      and getdate()
0
 

Author Comment

by:Lapchien
ID: 18785995
LogId            Date                  Time                                 Number                    Extension
234            24/03/2007     01/01/1900 19:20:23        123456789                     2271
235            24/03/2007     01/01/1900 19:21:01         987654321                    2334


0
 

Author Comment

by:Lapchien
ID: 18786016
Ok, so I have:

SELECT CallLogger.CallLoggerId,
      CallLogger.[Time],
COUNT (CallLogger.CallLoggerId) AS Calls
FROM
calllogger
where
date+convert(float, time) >= dateadd(hour, -1, getdate())
GROUP BY CallLogger.CallLoggerId, CallLogger.[Time]
ORDER BY CallLogger.CallLoggerId ASC

Interestingly, the firt line looks real odd - the rest is fine though...  why is the first line dodgy?

1900-01-01 23:53:16.000      1      237936
1900-01-01 18:33:14.000      1      243325
1900-01-01 18:32:40.000      1      243326
1900-01-01 18:33:23.000      1      243327
1900-01-01 18:32:13.000      1      243328
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 18786220
You should sort for Time, too:

ORDER BY CallLogger.CallLoggerId, CallLogger.[Time]

GROUP BY does not necessarily sort for the group values
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now