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
Solved

tsql select count with interval time each 30 second .

Posted on 2013-01-14
8
641 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 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 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