Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

tsql select count with interval time each 30 second .

Posted on 2013-01-14
8
Medium Priority
?
668 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 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