?
Solved

tsql select count with interval time each 30 second .

Posted on 2013-01-14
8
Medium Priority
?
658 Views
Last Modified: 2013-01-14
i want to count the number of message from table each 30 second interval  to put in the graph..  which query should give me in this selection ?
0
Comment
Question by:afifosh
[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
  • 2
  • 2
8 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38774392
Try something like:
SELECT COUNT(*) AS Total,CONVERT(char(8),DateField,112)+' '+CONVERT(char(3),DateField,108)+CASE WHEN MINUTE(DateField)<30 THEN '00' ELSE '30' END TimeInterval
FROM Table
GROUP BY CONVERT(char(8),DateField,112)+' '+CONVERT(char(3),DateField,108)+CASE WHEN MINUTE(DateField)<30 THEN '00' ELSE '30' END
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38774405
in short:
select CONVERT(varchar(16), date_field, 120)
  , case when DATEPART(field, date_field) < 30 then 0 else 1 end
  , COUNT(*)
from yourtable
group by CONVERT(varchar(16), date_field, 120)
  , case when DATEPART(field, date_field) < 30 then 0 else 1 end 

Open in new window

0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38774425
angelIII, I could be wrong, but I don't think that would work. That is grouping by yyyy-MM-dd HH:mm, meaning any distinct minute is grouped into a separate category. You would need to create a date with either 0 or 30 minutes. Yours would seem to count all that have :01, :02, etc...

I could be wrong. You're one of the people I respect most regarding SQL around here, but it does seem as if your results aren't quite what the OP requested.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:afifosh
ID: 38774434
the two solution not work something is missing
i have something like message , datetimein

each 30 seconds i make a selection using ajaxtime set timeout 30 second
than i want to count record in each .. 30 second..
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38774440
I suppose mine would be simpler with 120 as well:
SELECT COUNT(*) AS Total, CONVERT(char(14),DateField,120)+CASE WHEN DATEPART(minute,DateField)<30 THEN '00' ELSE '30' END AS TimeInterval
FROM Table
GROUP BY CONVERT(char(14),DateField,120)+CASE WHEN DATEPART(minute,DateField)<30 THEN '00' ELSE '30' END

(I have a minute function which I remembered won't work with normal T-SQL)
0
 
LVL 1

Author Comment

by:afifosh
ID: 38774467
i think ur function doesn't work
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38774482
>the two solution not work
can you please clarify "does not work" ?
can you show sample data, requested output, and what you get?

question: the field datetimein  is it datetime data type?
0
 
LVL 18

Accepted Solution

by:
Cluskitt earned 2000 total points
ID: 38774488
Ok, what exactly are you trying to achieve. In your original question, it seems that you were looking for counts for date intervals of 30 seconds. Now it seems that you just want to know how many there were in the last 30 seconds. That is a different thing.

The first one was the one we were answering. For the second, all you need to do is:
SELECT COUNT(*) AS Total
FROM Table
WHERE DATEDIFF(second,DateField,GETDATE())<=30
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 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