Solved

tsql select count with interval time each 30 second .

Posted on 2013-01-14
8
633 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
  • 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 142

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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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 142

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now