Solved

SQL query for records added in the last hour?

Posted on 2007-03-24
11
1,940 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

756 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