Grouping a query in 30 seconds

Hi,

How can I make a query and group the registries in a interval of 30 seconds...like

for each line I have a datetime field that have all the day, and I need it to return just like

TIME Contador_type1 Contador_type2 Total

01-01-2006 00:00:30.000 2 5 7

01-01-2006 00:01:00.000 3 7 10

It's just an example...but that's the result that I need and my table is

data_hora -- datetime field

tipo - 1 or 2 -- count

nrtelefone - that's is the number dialed.

Thanks

Answer this question

Grouping a query in 30 seconds

tanitoUy
It's almost that...but I need the role day time from 01/03/2006 00:00:00.000 to 01/03/2006 23:59:59.997 for example
ader10

Hum...ok..I'll check again...but do you Know webchart I'm using this query to build a smoothlinechart, but it's getting an strange format..

Do you think that I need to pass the whole interval, or you think that the values that is missing the graph will automatically put 0

Thanks anyway, it worked with Select Floor(Convert(Float, data_hora) * 24 * 60 *2) As TEMPO, count(*) AS TOTAL From pabx WHERE cod_cliente = 221 AND data_hora BETWEEN '20060201' AND '20060203' Group By Floor(Convert(Float, data_hora) * 24 * 60 *2) ORDER BY Floor(Convert(Float, data_hora) * 24 * 60 *2)
harrybosh

Hi,

I tried that and didn't worked, I need to count how many rows are, in a period of 30 seconds.

Thanks for helping almost there...
Karthikeyan.B

As I the code above didn't work can you give me an example

Thanks
skills0
I am not sure what you are after if my last SQL statement does not produce the result you are looking for. The statement I gave you will group all records together that fall withing the 30 intervals. If it does not then I did something wrong.
madog5353
Does this help

Select Floor(Convert(Float, data_hora) * 24 * 60 *2) As TimeChunk, count(*)

From myTable

Group By Floor(Convert(Float, data_hora) * 24 * 60 *2);
Dan_Icon

OK, this works, but it does not group by an interval of 30 seconds, it's just put together all registries that has the same time....

Did you get the idea or not Like I did something(based on your idea) like a while the increase the time from 01/01/2006 00:00:00.000 to 02/01/2006 23:59:30.000 just using dateadd(ss,30,@date) but the problem is....to fill the total field I need to do a select that counts and it takes a long time to complete...

I'm asking if there is a faster way...
Daisycutter
Hi there and welcome to the groups,

see if that one helps:

As you didn’t specified your logic in your request (if it should be rounded up or down if its perhaps 00:15 (00:00 vs. 00:30)), you possible have to tweak the case branch.

DROP TABLE SomeTable

GO

CREATE TABLE SomeTable

(

SomeColumn datetime,

SomeValue int

)

INSERT INTO SomeTable

VALUES(GETDATE(),1)

INSERT INTO SomeTable

VALUES(DATEADD(mi,30,GETDATE()),1)

SELECT SUm(SomeValue),

DATEADD(mi,Minutes,DATEADD(hh,hours,Date))

FROM

(

SELECT

CONVERT(VARCHAR(10),SOmeColumn,112) AS Date ,

SomeValue,

DATEPART(hh,SomeColumn) Hours,

(CASE WHEN DATEPART(mi,SomeColumn) <30 THEN 30 ELSE 0 END) Minutes

From SomeTable

) SUbQUery

GROUP BY DATEADD(mi,Minutes,DATEADD(hh,hours,Date))

Drop Table SomeTable

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---
Sinan Ussakli - MSFT
Use a similar solution to that suggested by sussemeyer above, but use integer division of 30 (rounds result to nearest whole number) with the seconds, as this would be more efficient, and would do away with the CASE statement, which is computationally costly.

HTH For more SQL tips, check out my blog below:
Shash58691
How about this then:

Select Convert(date, Floor(Convert(Float, data_hora) * 24 * 60 *2)/2880.0) As TimeChunk, count(*)

From myTable

Group By Convert(date, Floor(Convert(Float, data_hora) * 24 * 60 *2)/2880.0);
LoisW

Here it is..with the columns

COD_CLIENTE,DATA_HORA,NRTELEFONE,RAMAL,TEMPO_SEGUNDOS,TIPO,TEMPO_ATENDIMENTO,JAPROCESSADO VALOR,VALOR_CONC,VALOR_TARIFA,VALOR_TARIFA_CONC,CLASSIFICA,LOCALIDADE,VALOR_TEMPO,NUMERO_E1, BLOQUEADO,TABELA_TELEFONICA
149,2006-03-01 09:48:26.000,0800784403,6935,1.0,2,0,NULL,NULL,NULL,NULL,NULL,ESP,0800 NACIONAL,.5000,01132816900,1,1
149,2006-03-01 09:44:16.000,01144145754,6922,324.0,2,0,NULL,.6600,.7536,.1100,.1256,CONUR,ATIBAIA - SP,6.0000,01132816900,1,1
149,2006-03-01 09:49:25.000,01137417505,6935,108.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 09:53:59.000,01159258359,6941,103.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 09:57:58.000,01332995733,6922,228.0,2,0,NULL,1.0440,1.9800,.2900,.5500,DDD,SANTOS - SP,3.6000,01132816900,1,1
149,2006-03-01 10:01:20.000,01184262728,6923,76.0,2,0,NULL,.5500,.6710,.5000,.6100,VC1,AREA 011 - CELULAR - SP,1.1000,01132816900,1,1
149,2006-03-01 09:55:32.000,01121874200,6932,457.0,2,0,NULL,.6800,.9600,.0850,.1200,LOCAL,SAO PAULO - SP,8.0000,01132816900,1,1
149,2006-03-01 10:02:24.000,05534125161,6937,104.0,2,0,NULL,.6720,.8800,.4200,.5500,DDD,SANTA MARIA - RS,1.6000,01132816900,1,1
149,2006-03-01 10:05:05.000,01121617500,6935,67.0,2,0,NULL,.0850,.1200,.0850,.1200,LOCAL,SAO PAULO - SP,1.0000,01132816900,1,1
149,2006-03-01 10:04:41.000,01934627164,6937,136.0,2,0,NULL,.8820,1.1550,.4200,.5500,DDD,AMERICANA - SP,2.1000,01132816900,1,1
149,2006-03-01 10:11:05.000,01934582333,6936,46.0,2,0,NULL,.4200,.5500,.4200,.5500,DDD,SANTA BARBARA D OESTE - SP,1.0000,01132816900,1,1
149,2006-03-01 10:12:14.000,01121617525,6935,9.0,2,0,NULL,.0000,.0000,.0850,.1200,LOCAL,SAO PAULO - SP,.0000,01132816900,1,1
149,2006-03-01 10:08:37.000,01161035061,6913,255.0,2,0,NULL,.4250,.6000,.0850,.1200,LOCAL,SAO PAULO - SP,5.0000,01132816900,1,1
149,2006-03-01 10:11:35.000,01434546110,6941,263.0,2,0,NULL,1.7640,2.3100,.4200,.5500,DDD,BAURU - SP,4.2000,01132816900,1,1
149,2006-03-01 10:18:37.000,01934063393,6936,87.0,2,0,NULL,.5460,.7150,.4200,.5500,DDD,AMERICANA - SP,1.3000,01132816900,1,1
149,2006-03-01 10:26:12.000,01121617525,6935,11.0,2,0,NULL,.0000,.0000,.0850,.1200,LOCAL,SAO PAULO - SP,.0000,01132816900,1,1
149,2006-03-01 10:25:25.000,01132536576,6920,78.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:23:20.000,01162222734,6904,244.0,2,0,NULL,.3400,.4800,.0850,.1200,LOCAL,SAO PAULO - SP,4.0000,01132816900,1,1
149,2006-03-01 10:29:40.000,01991638344,6924,87.0,2,0,NULL,1.4820,1.5600,1.1400,1.2000,VC2,AREA 019 - CELULAR - SP,1.3000,01132816900,1,1
149,2006-03-01 10:33:54.000,011102,6926,70.0,2,0,NULL,.0850,.1200,.0850,.1200,LOCAL,SAO PAULO - SP,1.0000,01132816900,1,1
149,2006-03-01 10:35:22.000,01934692606,6926,110.0,2,0,NULL,.7140,.9350,.4200,.5500,DDD,AMERICANA - SP,1.7000,01132816900,1,1
149,2006-03-01 10:41:11.000,01161280656,6924,28.0,2,0,NULL,.0850,.1200,.0850,.1200,LOCAL,SAO PAULO - SP,1.0000,01132816900,1,1
149,2006-03-01 10:38:32.000,01381340603,6921,257.0,2,0,NULL,4.6740,4.9200,1.1400,1.2000,VC2,AREA 013 - CELULAR - SP,4.1000,01132816900,1,1
149,2006-03-01 10:50:16.000,01121617500,6935,73.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:50:46.000,08007728486,6923,186.0,2,0,NULL,NULL,NULL,NULL,NULL,ESP,0800 NACIONAL,2.9000,01132816900,1,1
149,2006-03-01 10:54:16.000,01169574019,6923,124.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:57:38.000,01155102193,6915,87.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:58:53.000,01171484387,6947,47.0,2,0,NULL,.3000,.3660,.5000,.6100,VC1,AREA 011 - CELULAR - SP,.6000,01132816900,1,1
149,2006-03-01 11:00:36.000,01155922414,6935,11.0,2,0,NULL,.0000,.0000,.0850,.1200,LOCAL,SAO PAULO - SP,.0000,01132816900,1,1
149,2006-03-01 10:59:43.000,01171484387,6925,104.0,2,0,NULL,.8000,.9760,.5000,.6100,VC1,AREA 011 - CELULAR - SP,1.6000,01132816900,1,1

And what I need to return is something like(ex not using the above)

DATA_HORA TOTAL

01/03/2006 00:00:30.000 5

01/03/2006 00:01:00.000 2

01/03/2006 00:01:30.000 6

Thanks
JP Gonzalez
Hi,

OK you didn’t mention that you just wanted to have the count, then just replace the SUM() with a COUNT(*) and you’ll be fine.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---
darkangel_17
Coudl you please post some sample data

HTH; Jens Suessmeyer.

---
http://www.sqlserver2005.de
---
Stojance

OK,

But in the case of 30 seconds, it don't work

I tried to change...but it increase the minutes wrongly..

Thanks
Grouping a query in 30 seconds

Answer this question