Member_2_6478753
asked on
tsql select count with interval time each 30 second .
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 ?
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
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.
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.
ASKER
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..
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..
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)
SELECT COUNT(*) AS Total, CONVERT(char(14),DateField
FROM Table
GROUP BY CONVERT(char(14),DateField
(I have a minute function which I remembered won't work with normal T-SQL)
ASKER
i think ur function doesn't work
>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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT COUNT(*) AS Total,CONVERT(char(8),Date
FROM Table
GROUP BY CONVERT(char(8),DateField,