Solved

SQL query for records added in the last hour?

Posted on 2007-03-24
11
1,944 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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 70

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 70

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

615 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