Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
maarten.del
Starting Member
17 Posts |
 Posted - 2010-03-11 : 03:32:11
|
| I have a table like this:2009-12-23 13:52:24.710 33422009-12-23 13:51:14.710 33472009-12-23 13:50:04.710 33492009-12-23 13:49:14.710 33372009-12-23 13:48:04.710 33162009-12-23 13:47:14.710 33182009-12-23 13:46:24.690 33212009-12-23 13:45:14.690 33282009-12-23 13:44:04.760 33512009-12-23 13:43:14.760 33302009-12-23 13:42:04.760 33452009-12-23 13:41:14.740 33322009-12-23 13:40:04.740 33562009-12-23 13:39:14.740 33492009-12-23 13:38:04.740 33452009-12-23 13:37:14.740 33262009-12-23 13:36:04.740 32832009-12-23 13:35:14.740 35732009-12-23 13:34:04.720 41772009-12-23 13:33:14.720 142009-12-23 13:32:04.720 142009-12-23 13:31:14.720 162009-12-23 13:30:24.720 142009-12-23 13:29:14.370 162009-12-23 13:28:24.370 31682009-12-23 13:27:14.370 32672009-12-23 13:26:24.370 33142009-12-23 13:25:14.370 33252009-12-23 13:24:24.370 33192009-12-23 13:23:14.350 33212009-12-23 13:22:24.350 33332009-12-23 13:21:14.350 33192009-12-23 13:20:24.350 33442009-12-23 13:19:14.350 33422009-12-23 13:18:24.350 33562009-12-23 13:17:14.350 33592009-12-23 13:16:24.350 33442009-12-23 13:15:14.330 33512009-12-23 13:14:19.710 33472009-12-23 13:13:09.690 33522009-12-23 13:12:19.690 33562009-12-23 13:11:09.690 33402009-12-23 13:10:19.690 33262009-12-23 13:09:09.690 33422009-12-23 13:08:19.690 33392009-12-23 13:07:09.690 33252009-12-23 13:06:19.680 33302009-12-23 13:05:09.680 33282009-12-23 13:04:19.680 33492009-12-23 13:03:04.680 33192009-12-23 13:02:14.680 33282009-12-23 13:01:04.680 33332009-12-23 13:00:14.660 33442009-12-23 12:59:24.630 33492009-12-23 12:58:14.630 33332009-12-23 12:57:24.630 33442009-12-23 12:56:14.620 33512009-12-23 12:55:24.620 33522009-12-23 12:54:14.620 33422009-12-23 12:53:24.620 33442009-12-23 12:52:14.620 33422009-12-23 12:51:24.620 33392009-12-23 12:50:14.620 33512009-12-23 12:49:04.620 33452009-12-23 12:48:14.600 33452009-12-23 12:47:04.600 33472009-12-23 12:46:14.600 33522009-12-23 12:45:24.600 33422009-12-23 12:44:14.460 33452009-12-23 12:43:24.460 33442009-12-23 12:42:14.460 33562009-12-23 12:41:24.460 33452009-12-23 12:40:14.460 33442009-12-23 12:39:24.460 33522009-12-23 12:38:14.460 33472009-12-23 12:37:24.460 33562009-12-23 12:36:14.440 33512009-12-23 12:35:24.440 33512009-12-23 12:34:14.440 33402009-12-23 12:33:24.440 33542009-12-23 12:32:14.440 33472009-12-23 12:31:04.440 33452009-12-23 12:30:14.440 33612009-12-23 12:29:24.290 33612009-12-23 12:28:14.290 33632009-12-23 12:27:24.290 33632009-12-23 12:26:14.270 33612009-12-23 12:25:24.270 33612009-12-23 12:24:14.270 33612009-12-23 12:23:24.270 33592009-12-23 12:22:14.270 33652009-12-23 12:21:24.270 33652009-12-23 12:20:14.270 33612009-12-23 12:19:24.270 33512009-12-23 12:18:14.260 33592009-12-23 12:17:24.260 33702009-12-23 12:16:14.260 33752009-12-23 12:15:24.260 33652009-12-23 12:14:14.510 33682009-12-23 12:13:24.510 33752009-12-23 12:12:14.510 33582009-12-23 12:11:24.510 33822009-12-23 12:10:14.510 33772009-12-23 12:09:24.510 33782009-12-23 12:08:14.490 33892009-12-23 12:07:24.490 34032009-12-23 12:06:14.490 33992009-12-23 12:05:24.490 34032009-12-23 12:04:14.490 33962009-12-23 12:03:24.490 33992009-12-23 12:02:14.490 34062009-12-23 12:01:04.490 34132009-12-23 12:00:14.470 34242009-12-23 11:59:24.540 34202009-12-23 11:58:14.540 34462009-12-23 11:57:24.540 34762009-12-23 11:56:14.520 36892009-12-23 11:55:24.520 142009-12-23 11:54:14.520 142009-12-23 11:53:24.520 142009-12-23 11:52:14.520 142009-12-23 11:51:24.520 142009-12-23 11:50:14.520 162009-12-23 11:49:24.520 122009-12-23 11:48:14.510 162009-12-23 11:47:24.510 142009-12-23 11:46:14.510 142009-12-23 11:45:04.770 142009-12-23 11:44:14.760 142009-12-23 11:43:04.760 142009-12-23 11:42:14.760 142009-12-23 11:41:04.760 142009-12-23 11:40:14.760 102009-12-23 11:39:04.760 102009-12-23 11:38:14.760 142009-12-23 11:37:24.760 162009-12-23 11:36:14.740 142009-12-23 11:35:04.740 162009-12-23 11:34:14.740 122009-12-23 11:33:04.740 142009-12-23 11:32:14.740 122009-12-23 11:31:04.740 142009-12-23 11:30:14.720 162009-12-23 11:29:24.570 162009-12-23 11:28:14.570 162009-12-23 11:27:24.570 122009-12-23 11:26:14.570 162009-12-23 11:25:24.570 122009-12-23 11:24:14.570 122009-12-23 11:23:24.570 122009-12-23 11:22:14.570 102009-12-23 11:21:24.550 142009-12-23 11:20:14.550 142009-12-23 11:19:24.550 122009-12-23 11:18:14.550 142009-12-23 11:17:24.550 102009-12-23 11:16:14.550 102009-12-23 11:15:24.550 162009-12-23 11:14:14.510 102009-12-23 11:13:24.510 162009-12-23 11:12:14.510 162009-12-23 11:11:24.510 102009-12-23 11:10:14.510 142009-12-23 11:09:24.510 102009-12-23 11:08:14.510 102009-12-23 11:07:24.510 162009-12-23 11:06:14.490 162009-12-23 11:05:24.490 102009-12-23 11:04:14.490 142009-12-23 11:03:24.490 122009-12-23 11:02:14.490 102009-12-23 11:01:24.490 142009-12-23 11:00:14.490 122009-12-23 10:59:24.150 162009-12-23 10:58:14.130 142009-12-23 10:57:24.130 162009-12-23 10:56:14.130 162009-12-23 10:55:24.130 142009-12-23 10:54:14.130 102009-12-23 10:53:24.130 122009-12-23 10:52:14.130 122009-12-23 10:51:24.130 162009-12-23 10:50:14.120 142009-12-23 10:49:24.120 172009-12-23 10:48:14.120 162009-12-23 10:47:24.120 122009-12-23 10:46:14.120 162009-12-23 10:45:24.120 102009-12-23 10:44:14.460 102009-12-23 10:43:24.460 122009-12-23 10:42:14.460 102009-12-23 10:41:24.460 162009-12-23 10:40:14.440 162009-12-23 10:39:24.440 162009-12-23 10:38:14.440 142009-12-23 10:37:24.440 102009-12-23 10:36:14.440 122009-12-23 10:35:24.440 122009-12-23 10:34:14.440 142009-12-23 10:33:24.440 122009-12-23 10:32:14.430 122009-12-23 10:31:24.430 162009-12-23 10:30:14.430 162009-12-23 10:29:19.680 142009-12-23 10:28:09.680 142009-12-23 10:27:19.680 162009-12-23 10:26:09.680 162009-12-23 10:25:19.680 102009-12-23 10:24:09.680 102009-12-23 10:23:19.680 122009-12-23 10:22:09.680 102009-12-23 10:21:19.660 162009-12-23 10:20:09.660 162009-12-23 10:19:19.660 122009-12-23 10:18:09.660 162009-12-23 10:17:19.660 162009-12-23 10:16:09.660 162009-12-23 10:15:19.660 122009-12-23 10:14:10.020 142009-12-23 10:13:20.010 142009-12-23 10:12:10.010 142009-12-23 10:11:20.010 162009-12-23 10:10:10.010 162009-12-23 10:09:20.010 162009-12-23 10:08:10.010 142009-12-23 10:07:20.010 142009-12-23 10:06:10.010 122009-12-23 10:05:19.990 142009-12-23 10:04:09.990 142009-12-23 10:03:19.990 162009-12-23 10:02:09.990 142009-12-23 10:01:19.990 122009-12-23 10:00:09.970 102009-12-23 09:59:15.130 162009-12-23 09:58:05.130 162009-12-23 09:57:15.130 122009-12-23 09:56:05.130 142009-12-23 09:55:15.130 32992009-12-23 09:54:05.130 32902009-12-23 09:53:15.120 32902009-12-23 09:52:05.120 33022009-12-23 09:51:15.120 32882009-12-23 09:50:05.120 32992009-12-23 09:49:15.120 32972009-12-23 09:48:05.120 32782009-12-23 09:47:15.120 32812009-12-23 09:46:05.120 32812009-12-23 09:45:15.100 32972009-12-23 09:44:19.960 32852009-12-23 09:43:09.960 32812009-12-23 09:42:19.960 32992009-12-23 09:41:09.960 32932009-12-23 09:40:19.940 32992009-12-23 09:39:09.910 32922009-12-23 09:38:19.830 32862009-12-23 09:37:09.830 32922009-12-23 09:36:19.830 32812009-12-23 09:35:09.830 32802009-12-23 09:34:19.830 32922009-12-23 09:33:09.830 32972009-12-23 09:32:19.820 32972009-12-23 09:31:09.820 32952009-12-23 09:30:19.820 32972009-12-23 09:29:09.970 32902009-12-23 09:28:19.970 32802009-12-23 09:27:09.970 33002009-12-23 09:26:19.970 32832009-12-23 09:25:09.970 32882009-12-23 09:24:19.960 32852009-12-23 09:23:09.960 32952009-12-23 09:22:19.960 32862009-12-23 09:21:09.960 32902009-12-23 09:20:19.960 32782009-12-23 09:19:09.960 32812009-12-23 09:18:19.960 32732009-12-23 09:17:09.960 32862009-12-23 09:16:19.940 33022009-12-23 09:15:09.940 32812009-12-23 09:14:15.290 33062009-12-23 09:13:05.290 32932009-12-23 09:12:15.290 32742009-12-23 09:11:05.290 32902009-12-23 09:10:15.290 32852009-12-23 09:09:05.290 32732009-12-23 09:08:15.290 32922009-12-23 09:07:05.270 33002009-12-23 09:06:15.270 32832009-12-23 09:05:05.270 32882009-12-23 09:04:15.270 32932009-12-23 09:03:05.270 32882009-12-23 09:02:15.270 32852009-12-23 09:01:05.270 32882009-12-23 09:00:15.270 32852009-12-23 08:59:05.260 32952009-12-23 08:58:15.240 32882009-12-23 08:57:05.240 32952009-12-23 08:56:15.240 32532009-12-23 08:55:05.240 33282009-12-23 08:54:15.240 33142009-12-23 08:53:05.240 33262009-12-23 08:52:15.240 33072009-12-23 08:51:05.240 33182009-12-23 08:50:15.220 33002009-12-23 08:49:05.220 33092009-12-23 08:48:15.220 33212009-12-23 08:47:05.220 33192009-12-23 08:46:15.220 33142009-12-23 08:45:04.970 33212009-12-23 08:44:14.970 33002009-12-23 08:43:04.970 33072009-12-23 08:42:14.970 33142009-12-23 08:41:04.960 33042009-12-23 08:40:14.960 33072009-12-23 08:39:04.960 33112009-12-23 08:38:14.960 33002009-12-23 08:37:04.960 33112009-12-23 08:36:14.960 33182009-12-23 08:35:04.960 33162009-12-23 08:34:14.960 33112009-12-23 08:33:04.940 32972009-12-23 08:32:14.940 3314And 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)
17689 Posts |
Posted - 2010-03-11 : 03:43:58
|
[code]select dateadd(minute, datediff(minute, 0, datecol) / 15 * 15, 0), avg(value)from yourtablegroup by dateadd(minute, datediff(minute, 0, datecol) / 15 * 15, 0)[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
|
|
|
|
|
average each 15 min