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
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
|||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:
|||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...
|||As I the code above didn't work can you give me an example ?
Thanks
|||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
|||OK,
But in the case of 30 seconds, it don't work ?
I tried to change...but it increase the minutes wrongly..
Thanks
|||Coudl you please post some sample data ?HTH; Jens Suessmeyer.
http://www.sqlserver2005.de
|||
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);
|||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
|||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|||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);
|||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...
|||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.
|||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)
sql
No comments:
Post a Comment