Solved

tsql select count with interval time each 30 second .

Posted on 2013-01-14
8
640 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

813 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

17 Experts available now in Live!

Get 1:1 Help Now