SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 average each 15 min
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maarten.del
Starting Member

Belgium
17 Posts

Posted - 03/11/2010 :  03:32:11  Show Profile  Reply with Quote
I have a table like this:

2009-12-23 13:52:24.710 3342
2009-12-23 13:51:14.710 3347
2009-12-23 13:50:04.710 3349
2009-12-23 13:49:14.710 3337
2009-12-23 13:48:04.710 3316
2009-12-23 13:47:14.710 3318
2009-12-23 13:46:24.690 3321
2009-12-23 13:45:14.690 3328
2009-12-23 13:44:04.760 3351
2009-12-23 13:43:14.760 3330
2009-12-23 13:42:04.760 3345
2009-12-23 13:41:14.740 3332
2009-12-23 13:40:04.740 3356
2009-12-23 13:39:14.740 3349
2009-12-23 13:38:04.740 3345
2009-12-23 13:37:14.740 3326
2009-12-23 13:36:04.740 3283
2009-12-23 13:35:14.740 3573
2009-12-23 13:34:04.720 4177
2009-12-23 13:33:14.720 14
2009-12-23 13:32:04.720 14
2009-12-23 13:31:14.720 16
2009-12-23 13:30:24.720 14
2009-12-23 13:29:14.370 16
2009-12-23 13:28:24.370 3168
2009-12-23 13:27:14.370 3267
2009-12-23 13:26:24.370 3314
2009-12-23 13:25:14.370 3325
2009-12-23 13:24:24.370 3319
2009-12-23 13:23:14.350 3321
2009-12-23 13:22:24.350 3333
2009-12-23 13:21:14.350 3319
2009-12-23 13:20:24.350 3344
2009-12-23 13:19:14.350 3342
2009-12-23 13:18:24.350 3356
2009-12-23 13:17:14.350 3359
2009-12-23 13:16:24.350 3344
2009-12-23 13:15:14.330 3351
2009-12-23 13:14:19.710 3347
2009-12-23 13:13:09.690 3352
2009-12-23 13:12:19.690 3356
2009-12-23 13:11:09.690 3340
2009-12-23 13:10:19.690 3326
2009-12-23 13:09:09.690 3342
2009-12-23 13:08:19.690 3339
2009-12-23 13:07:09.690 3325
2009-12-23 13:06:19.680 3330
2009-12-23 13:05:09.680 3328
2009-12-23 13:04:19.680 3349
2009-12-23 13:03:04.680 3319
2009-12-23 13:02:14.680 3328
2009-12-23 13:01:04.680 3333
2009-12-23 13:00:14.660 3344
2009-12-23 12:59:24.630 3349
2009-12-23 12:58:14.630 3333
2009-12-23 12:57:24.630 3344
2009-12-23 12:56:14.620 3351
2009-12-23 12:55:24.620 3352
2009-12-23 12:54:14.620 3342
2009-12-23 12:53:24.620 3344
2009-12-23 12:52:14.620 3342
2009-12-23 12:51:24.620 3339
2009-12-23 12:50:14.620 3351
2009-12-23 12:49:04.620 3345
2009-12-23 12:48:14.600 3345
2009-12-23 12:47:04.600 3347
2009-12-23 12:46:14.600 3352
2009-12-23 12:45:24.600 3342
2009-12-23 12:44:14.460 3345
2009-12-23 12:43:24.460 3344
2009-12-23 12:42:14.460 3356
2009-12-23 12:41:24.460 3345
2009-12-23 12:40:14.460 3344
2009-12-23 12:39:24.460 3352
2009-12-23 12:38:14.460 3347
2009-12-23 12:37:24.460 3356
2009-12-23 12:36:14.440 3351
2009-12-23 12:35:24.440 3351
2009-12-23 12:34:14.440 3340
2009-12-23 12:33:24.440 3354
2009-12-23 12:32:14.440 3347
2009-12-23 12:31:04.440 3345
2009-12-23 12:30:14.440 3361
2009-12-23 12:29:24.290 3361
2009-12-23 12:28:14.290 3363
2009-12-23 12:27:24.290 3363
2009-12-23 12:26:14.270 3361
2009-12-23 12:25:24.270 3361
2009-12-23 12:24:14.270 3361
2009-12-23 12:23:24.270 3359
2009-12-23 12:22:14.270 3365
2009-12-23 12:21:24.270 3365
2009-12-23 12:20:14.270 3361
2009-12-23 12:19:24.270 3351
2009-12-23 12:18:14.260 3359
2009-12-23 12:17:24.260 3370
2009-12-23 12:16:14.260 3375
2009-12-23 12:15:24.260 3365
2009-12-23 12:14:14.510 3368
2009-12-23 12:13:24.510 3375
2009-12-23 12:12:14.510 3358
2009-12-23 12:11:24.510 3382
2009-12-23 12:10:14.510 3377
2009-12-23 12:09:24.510 3378
2009-12-23 12:08:14.490 3389
2009-12-23 12:07:24.490 3403
2009-12-23 12:06:14.490 3399
2009-12-23 12:05:24.490 3403
2009-12-23 12:04:14.490 3396
2009-12-23 12:03:24.490 3399
2009-12-23 12:02:14.490 3406
2009-12-23 12:01:04.490 3413
2009-12-23 12:00:14.470 3424
2009-12-23 11:59:24.540 3420
2009-12-23 11:58:14.540 3446
2009-12-23 11:57:24.540 3476
2009-12-23 11:56:14.520 3689
2009-12-23 11:55:24.520 14
2009-12-23 11:54:14.520 14
2009-12-23 11:53:24.520 14
2009-12-23 11:52:14.520 14
2009-12-23 11:51:24.520 14
2009-12-23 11:50:14.520 16
2009-12-23 11:49:24.520 12
2009-12-23 11:48:14.510 16
2009-12-23 11:47:24.510 14
2009-12-23 11:46:14.510 14
2009-12-23 11:45:04.770 14
2009-12-23 11:44:14.760 14
2009-12-23 11:43:04.760 14
2009-12-23 11:42:14.760 14
2009-12-23 11:41:04.760 14
2009-12-23 11:40:14.760 10
2009-12-23 11:39:04.760 10
2009-12-23 11:38:14.760 14
2009-12-23 11:37:24.760 16
2009-12-23 11:36:14.740 14
2009-12-23 11:35:04.740 16
2009-12-23 11:34:14.740 12
2009-12-23 11:33:04.740 14
2009-12-23 11:32:14.740 12
2009-12-23 11:31:04.740 14
2009-12-23 11:30:14.720 16
2009-12-23 11:29:24.570 16
2009-12-23 11:28:14.570 16
2009-12-23 11:27:24.570 12
2009-12-23 11:26:14.570 16
2009-12-23 11:25:24.570 12
2009-12-23 11:24:14.570 12
2009-12-23 11:23:24.570 12
2009-12-23 11:22:14.570 10
2009-12-23 11:21:24.550 14
2009-12-23 11:20:14.550 14
2009-12-23 11:19:24.550 12
2009-12-23 11:18:14.550 14
2009-12-23 11:17:24.550 10
2009-12-23 11:16:14.550 10
2009-12-23 11:15:24.550 16
2009-12-23 11:14:14.510 10
2009-12-23 11:13:24.510 16
2009-12-23 11:12:14.510 16
2009-12-23 11:11:24.510 10
2009-12-23 11:10:14.510 14
2009-12-23 11:09:24.510 10
2009-12-23 11:08:14.510 10
2009-12-23 11:07:24.510 16
2009-12-23 11:06:14.490 16
2009-12-23 11:05:24.490 10
2009-12-23 11:04:14.490 14
2009-12-23 11:03:24.490 12
2009-12-23 11:02:14.490 10
2009-12-23 11:01:24.490 14
2009-12-23 11:00:14.490 12
2009-12-23 10:59:24.150 16
2009-12-23 10:58:14.130 14
2009-12-23 10:57:24.130 16
2009-12-23 10:56:14.130 16
2009-12-23 10:55:24.130 14
2009-12-23 10:54:14.130 10
2009-12-23 10:53:24.130 12
2009-12-23 10:52:14.130 12
2009-12-23 10:51:24.130 16
2009-12-23 10:50:14.120 14
2009-12-23 10:49:24.120 17
2009-12-23 10:48:14.120 16
2009-12-23 10:47:24.120 12
2009-12-23 10:46:14.120 16
2009-12-23 10:45:24.120 10
2009-12-23 10:44:14.460 10
2009-12-23 10:43:24.460 12
2009-12-23 10:42:14.460 10
2009-12-23 10:41:24.460 16
2009-12-23 10:40:14.440 16
2009-12-23 10:39:24.440 16
2009-12-23 10:38:14.440 14
2009-12-23 10:37:24.440 10
2009-12-23 10:36:14.440 12
2009-12-23 10:35:24.440 12
2009-12-23 10:34:14.440 14
2009-12-23 10:33:24.440 12
2009-12-23 10:32:14.430 12
2009-12-23 10:31:24.430 16
2009-12-23 10:30:14.430 16
2009-12-23 10:29:19.680 14
2009-12-23 10:28:09.680 14
2009-12-23 10:27:19.680 16
2009-12-23 10:26:09.680 16
2009-12-23 10:25:19.680 10
2009-12-23 10:24:09.680 10
2009-12-23 10:23:19.680 12
2009-12-23 10:22:09.680 10
2009-12-23 10:21:19.660 16
2009-12-23 10:20:09.660 16
2009-12-23 10:19:19.660 12
2009-12-23 10:18:09.660 16
2009-12-23 10:17:19.660 16
2009-12-23 10:16:09.660 16
2009-12-23 10:15:19.660 12
2009-12-23 10:14:10.020 14
2009-12-23 10:13:20.010 14
2009-12-23 10:12:10.010 14
2009-12-23 10:11:20.010 16
2009-12-23 10:10:10.010 16
2009-12-23 10:09:20.010 16
2009-12-23 10:08:10.010 14
2009-12-23 10:07:20.010 14
2009-12-23 10:06:10.010 12
2009-12-23 10:05:19.990 14
2009-12-23 10:04:09.990 14
2009-12-23 10:03:19.990 16
2009-12-23 10:02:09.990 14
2009-12-23 10:01:19.990 12
2009-12-23 10:00:09.970 10
2009-12-23 09:59:15.130 16
2009-12-23 09:58:05.130 16
2009-12-23 09:57:15.130 12
2009-12-23 09:56:05.130 14
2009-12-23 09:55:15.130 3299
2009-12-23 09:54:05.130 3290
2009-12-23 09:53:15.120 3290
2009-12-23 09:52:05.120 3302
2009-12-23 09:51:15.120 3288
2009-12-23 09:50:05.120 3299
2009-12-23 09:49:15.120 3297
2009-12-23 09:48:05.120 3278
2009-12-23 09:47:15.120 3281
2009-12-23 09:46:05.120 3281
2009-12-23 09:45:15.100 3297
2009-12-23 09:44:19.960 3285
2009-12-23 09:43:09.960 3281
2009-12-23 09:42:19.960 3299
2009-12-23 09:41:09.960 3293
2009-12-23 09:40:19.940 3299
2009-12-23 09:39:09.910 3292
2009-12-23 09:38:19.830 3286
2009-12-23 09:37:09.830 3292
2009-12-23 09:36:19.830 3281
2009-12-23 09:35:09.830 3280
2009-12-23 09:34:19.830 3292
2009-12-23 09:33:09.830 3297
2009-12-23 09:32:19.820 3297
2009-12-23 09:31:09.820 3295
2009-12-23 09:30:19.820 3297
2009-12-23 09:29:09.970 3290
2009-12-23 09:28:19.970 3280
2009-12-23 09:27:09.970 3300
2009-12-23 09:26:19.970 3283
2009-12-23 09:25:09.970 3288
2009-12-23 09:24:19.960 3285
2009-12-23 09:23:09.960 3295
2009-12-23 09:22:19.960 3286
2009-12-23 09:21:09.960 3290
2009-12-23 09:20:19.960 3278
2009-12-23 09:19:09.960 3281
2009-12-23 09:18:19.960 3273
2009-12-23 09:17:09.960 3286
2009-12-23 09:16:19.940 3302
2009-12-23 09:15:09.940 3281
2009-12-23 09:14:15.290 3306
2009-12-23 09:13:05.290 3293
2009-12-23 09:12:15.290 3274
2009-12-23 09:11:05.290 3290
2009-12-23 09:10:15.290 3285
2009-12-23 09:09:05.290 3273
2009-12-23 09:08:15.290 3292
2009-12-23 09:07:05.270 3300
2009-12-23 09:06:15.270 3283
2009-12-23 09:05:05.270 3288
2009-12-23 09:04:15.270 3293
2009-12-23 09:03:05.270 3288
2009-12-23 09:02:15.270 3285
2009-12-23 09:01:05.270 3288
2009-12-23 09:00:15.270 3285
2009-12-23 08:59:05.260 3295
2009-12-23 08:58:15.240 3288
2009-12-23 08:57:05.240 3295
2009-12-23 08:56:15.240 3253
2009-12-23 08:55:05.240 3328
2009-12-23 08:54:15.240 3314
2009-12-23 08:53:05.240 3326
2009-12-23 08:52:15.240 3307
2009-12-23 08:51:05.240 3318
2009-12-23 08:50:15.220 3300
2009-12-23 08:49:05.220 3309
2009-12-23 08:48:15.220 3321
2009-12-23 08:47:05.220 3319
2009-12-23 08:46:15.220 3314
2009-12-23 08:45:04.970 3321
2009-12-23 08:44:14.970 3300
2009-12-23 08:43:04.970 3307
2009-12-23 08:42:14.970 3314
2009-12-23 08:41:04.960 3304
2009-12-23 08:40:14.960 3307
2009-12-23 08:39:04.960 3311
2009-12-23 08:38:14.960 3300
2009-12-23 08:37:04.960 3311
2009-12-23 08:36:14.960 3318
2009-12-23 08:35:04.960 3316
2009-12-23 08:34:14.960 3311
2009-12-23 08:33:04.940 3297
2009-12-23 08:32:14.940 3314

And I want to have the average value for each 15 min.

Like this:
2009-12-23 13:45:00.000 2345 (average for this 15 min)
2009-12-23 13:30:00.000 3974 (average for this 15 min)
2009-12-23 13:15:00.000 24 (average for this 15 min)

Can someone help me please?
Thanks in advance.

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 03/11/2010 :  03:43:58  Show Profile  Reply with Quote

select	dateadd(minute, datediff(minute, 0, datecol) / 15 * 15, 0), avg(value)
from	yourtable
group by dateadd(minute, datediff(minute, 0, datecol) / 15 * 15, 0)



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000