Solved

SQL query for records added in the last hour?

Posted on 2007-03-24
11
1,938 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 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 69

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 69

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

809 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