Solved

SQL query for records added in the last hour?

Posted on 2007-03-24
11
1,937 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

776 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