Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
17689 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  
 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.2 seconds. Powered By: Snitz Forums 2000