Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Группировка по тарифным зонам  [new]
beaver06
Member

Откуда:
Сообщений: 681
Подскажите, пожалуйста, как сделать такое:
В таблицу приходят данные от счетчиков электроэнергии (каждые 30 минут). Как вычислить расход электроэнергии за сутки (месяц) за следующиe интервалы времени:
Сутки разбиты на три зоны:
- c 21.00 до 08.00
- с 08.00 до 11.00
- с 11.00 до 18.00
- с 18.00 до 21.00
1 июн 09, 10:46    [7250205]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3264
сделать дополнительную табличку с зонами и заджойнить по
BETRWEEN ... AND ...
набейте тестовые данные - сделаем, а без - включайте мозг.
1 июн 09, 10:50    [7250225]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
beaver06
Member

Откуда:
Сообщений: 681
Ken@t
сделать дополнительную табличку с зонами и заджойнить по
BETRWEEN ... AND ...
набейте тестовые данные - сделаем, а без - включайте мозг.

Вот, пожалуйста, помогите если не трудно...

CREATE TABLE [dbo].[Table_1](
[meas] [int] NULL,
[dd] [datetime] NULL,
[measure] [decimal](18, 6) NULL
) ON [PRIMARY]


insert into dbo.Table_1 values ( 4091, '2009-05-29 12:25:29.000' , 79547.775000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 12:55:30.000' , 79549.94000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 13:25:32.000' , 79552.288000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 13:55:33.000' , 79554.444000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 14:25:34.000' , 79556.802000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 14:55:35.000' , 79559.14000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 15:25:36.000' , 79561.279000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 15:55:38.000' , 79563.419000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 16:25:39.000' , 79565.498000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 16:55:40.000' , 79567.641000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 17:25:41.000' , 79569.736000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 17:55:43.000' , 79571.714000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 18:25:44.000' , 79573.649000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 18:55:45.000' , 79575.538000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 19:25:46.000' , 79577.531000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 19:55:47.000' , 79579.664000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 20:25:49.000' , 79581.639000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 20:55:50.000' , 79583.429000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 21:25:51.000' , 79585.514000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 21:55:52.000' , 79587.436000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 22:20:53.000' , 79589.165000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 22:55:54.000' , 79591.365000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 23:25:56.000' , 79593.322000)
insert into dbo.Table_1 values ( 4091, '2009-05-29 23:55:57.000' , 79595.204000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 00:25:58.000' , 79597.149000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 00:55:59.000' , 79599.055000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 01:26:01.000' , 79600.927000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 01:56:02.000' , 79602.708000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 02:26:03.000' , 79604.747000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 02:51:04.000' , 79606.168000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 03:26:05.000' , 79608.354000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 03:56:06.000' , 79610.313000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 04:26:08.000' , 79612.103000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 04:56:09.000' , 79613.928000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 05:26:10.000' , 79615.733000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 05:56:11.000' , 79617.634000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 06:26:12.000' , 79619.456000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 06:56:14.000' , 79621.25000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 07:26:15.000' , 79623.292000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 07:56:16.000' , 79625.039000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 08:26:17.000' , 79627.049000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 08:56:18.000' , 79628.971000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 09:26:20.000' , 79631.064000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 09:56:21.000' , 79633.178000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 10:26:22.000' , 79635.335000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 10:56:23.000' , 79637.442000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 11:26:24.000' , 79639.701000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 11:56:26.000' , 79641.874000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 12:26:27.000' , 79644.022000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 12:56:28.000' , 79646.333000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 13:26:29.000' , 79648.508000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 13:56:30.000' , 79650.693000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 14:26:32.000' , 79653.039000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 14:56:33.000' , 79655.18000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 15:26:34.000' , 79657.535000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 15:56:35.000' , 79659.901000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 16:26:36.000' , 79662.027000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 16:56:38.000' , 79664.388000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 17:26:39.000' , 79666.744000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 17:56:40.000' , 79668.878000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 18:26:41.000' , 79671.226000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 18:56:42.000' , 79673.342000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 19:26:43.000' , 79675.687000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 19:56:45.000' , 79678.051000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 20:26:46.000' , 79680.126000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 20:56:47.000' , 79682.24000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 21:26:48.000' , 79684.421000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 21:56:49.000' , 79686.454000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 22:26:51.000' , 79688.564000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 22:56:52.000' , 79690.643000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 23:26:53.000' , 79692.555000)
insert into dbo.Table_1 values ( 4091, '2009-05-30 23:56:54.000' , 79694.476000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 00:26:55.000' , 79696.372000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 00:56:57.000' , 79698.285000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 01:26:58.000' , 79700.171000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 01:56:59.000' , 79702.096000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 02:27:00.000' , 79703.898000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 02:57:11.000' , 79705.712000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 03:27:12.000' , 79707.478000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 03:57:14.000' , 79709.192000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 04:27:15.000' , 79710.89000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 04:57:16.000' , 79712.516000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 05:27:17.000' , 79714.397000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 05:57:18.000' , 79716.153000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 06:27:20.000' , 79718.033000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 06:57:21.000' , 79719.83000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 07:27:22.000' , 79721.74000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 07:57:23.000' , 79723.667000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 08:27:24.000' , 79725.652000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 08:57:26.000' , 79727.525000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 09:27:27.000' , 79729.669000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 09:57:28.000' , 79731.968000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 10:27:29.000' , 79734.083000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 10:57:30.000' , 79736.251000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 11:27:32.000' , 79738.415000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 11:57:33.000' , 79740.743000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 12:27:34.000' , 79742.882000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 12:57:35.000' , 79745.022000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 13:27:36.000' , 79747.368000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 13:57:38.000' , 79749.499000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 14:27:39.000' , 79751.86000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 14:57:40.000' , 79754.003000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 15:27:41.000' , 79756.235000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 15:57:42.000' , 79758.598000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 16:27:44.000' , 79760.961000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 16:57:45.000' , 79763.326000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 17:27:46.000' , 79765.494000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 17:57:47.000' , 79767.848000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 18:27:48.000' , 79770.206000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 18:57:50.000' , 79772.564000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 19:27:51.000' , 79774.914000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 19:57:52.000' , 79777.265000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 20:27:53.000' , 79779.625000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 20:57:55.000' , 79781.618000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 21:27:56.000' , 79783.91000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 21:57:57.000' , 79786.078000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 22:27:58.000' , 79788.144000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 22:57:59.000' , 79790.164000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 23:28:01.000' , 79792.142000)
insert into dbo.Table_1 values ( 4091, '2009-05-31 23:58:02.000' , 79794.228000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 00:28:03.000' , 79796.142000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 00:58:04.000' , 79798.1000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 01:28:05.000' , 79799.936000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 01:58:06.000' , 79801.761000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 02:28:08.000' , 79803.53000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 02:58:09.000' , 79805.463000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 03:28:10.000' , 79807.325000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 03:58:11.000' , 79809.142000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 04:28:16.000' , 79810.996000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 04:58:21.000' , 79812.736000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 05:23:22.000' , 79814.238000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 05:59:41.000' , 79816.562000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 06:29:43.000' , 79818.342000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 06:59:44.000' , 79819.995000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 07:29:45.000' , 79822.072000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 07:59:46.000' , 79824.121000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 08:29:47.000' , 79826.206000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 08:59:49.000' , 79828.352000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 09:29:50.000' , 79830.501000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 09:59:51.000' , 79832.845000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 10:29:52.000' , 79835.007000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 10:59:53.000' , 79837.35000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 11:29:55.000' , 79839.531000)
insert into dbo.Table_1 values ( 4091, '2009-06-01 11:59:56.000' , 79841.895000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 12:25:29.000' , 84494.19000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 12:55:33.000' , 84496.425000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 13:25:37.000' , 84498.661000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 13:55:41.000' , 84500.902000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 14:25:44.000' , 84503.133000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 14:55:49.000' , 84505.34000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 15:25:54.000' , 84507.556000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 15:55:58.000' , 84509.769000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 16:26:03.000' , 84511.678000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 16:56:08.000' , 84513.366000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 17:26:11.000' , 0.000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 17:56:19.000' , 84514.11000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 18:26:28.000' , 84516.726000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 18:56:32.000' , 84519.235000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 19:21:36.000' , 84521.132000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 19:56:42.000' , 84523.756000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 20:26:47.000' , 84525.836000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 20:56:51.000' , 84527.88000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 21:26:56.000' , 84529.84000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 21:57:01.000' , 84531.816000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 22:27:06.000' , 84533.873000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 22:57:11.000' , 84536.069000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 23:27:17.000' , 84538.011000)
insert into dbo.Table_1 values ( 4092, '2009-05-29 23:57:21.000' , 84539.957000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 00:27:26.000' , 84541.994000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 00:57:31.000' , 84543.974000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 01:27:36.000' , 84545.926000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 01:57:41.000' , 84547.935000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 02:27:46.000' , 84549.829000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 02:57:52.000' , 84551.698000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 03:27:56.000' , 84553.591000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 03:58:01.000' , 84555.422000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 04:28:07.000' , 84557.101000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 04:58:11.000' , 84558.985000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 05:28:17.000' , 84560.869000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 05:58:29.000' , 84562.757000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 06:28:31.000' , 84564.652000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 06:58:36.000' , 84566.549000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 07:28:41.000' , 84568.43000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 07:58:47.000' , 84570.328000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 08:28:52.000' , 84572.232000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 08:58:57.000' , 84574.333000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 09:29:00.000' , 84576.341000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 09:59:06.000' , 84578.274000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 10:29:11.000' , 84580.469000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 10:59:17.000' , 84582.677000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 11:29:22.000' , 84584.887000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 11:59:26.000' , 84587.113000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 12:29:31.000' , 84589.331000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 12:59:36.000' , 84591.572000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 13:29:40.000' , 84593.807000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 13:59:45.000' , 84596.043000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 14:29:51.000' , 84598.294000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 14:59:55.000' , 84600.533000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 15:24:59.000' , 84602.402000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 15:55:05.000' , 84604.639000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 16:25:10.000' , 84606.873000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 16:55:15.000' , 84609.147000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 17:25:20.000' , 84611.412000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 17:55:26.000' , 84613.652000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 18:25:30.000' , 84615.893000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 18:55:35.000' , 84618.138000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 19:25:40.000' , 84620.374000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 19:55:45.000' , 84622.6000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 20:25:50.000' , 84624.827000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 20:55:55.000' , 84627.058000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 21:26:00.000' , 84629.288000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 21:56:05.000' , 84631.293000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 22:26:10.000' , 84633.511000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 22:56:13.000' , 84635.511000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 23:26:19.000' , 84637.472000)
insert into dbo.Table_1 values ( 4092, '2009-05-30 23:56:24.000' , 84639.322000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 00:26:28.000' , 84641.069000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 00:56:33.000' , 84643.002000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 01:26:39.000' , 84644.828000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 01:56:43.000' , 84646.624000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 02:26:49.000' , 84648.514000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 02:56:54.000' , 84650.307000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 03:27:00.000' , 84652.069000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 03:57:03.000' , 84653.982000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 04:27:09.000' , 84655.789000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 04:57:14.000' , 84657.52000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 05:27:20.000' , 84659.408000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 05:57:24.000' , 84661.155000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 06:27:29.000' , 84663.083000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 06:57:35.000' , 84665.023000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 07:27:40.000' , 84666.962000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 07:57:45.000' , 84668.926000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 08:27:50.000' , 84670.894000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 08:57:55.000' , 84673.09000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 09:27:59.000' , 84675.292000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 09:58:04.000' , 84677.505000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 10:28:09.000' , 84679.723000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 10:58:14.000' , 84681.962000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 11:28:19.000' , 84684.201000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 11:58:24.000' , 84686.462000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 12:29:01.000' , 84688.706000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 12:58:33.000' , 84690.964000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 13:28:38.000' , 84693.223000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 13:58:43.000' , 84695.492000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 14:28:48.000' , 84697.753000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 14:58:53.000' , 84700.006000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 15:28:58.000' , 84702.29000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 15:59:03.000' , 84704.566000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 16:29:08.000' , 84706.846000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 16:59:14.000' , 84709.134000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 17:29:18.000' , 84711.42000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 17:59:23.000' , 84713.699000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 18:29:28.000' , 84715.961000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 18:59:33.000' , 84718.218000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 19:29:38.000' , 84720.448000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 19:59:43.000' , 84722.694000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 20:29:47.000' , 84724.92000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 20:59:53.000' , 84727.144000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 21:29:58.000' , 84729.371000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 21:55:02.000' , 84731.224000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 22:25:07.000' , 84733.443000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 22:55:12.000' , 84735.595000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 23:25:18.000' , 84737.663000)
insert into dbo.Table_1 values ( 4092, '2009-05-31 23:55:23.000' , 84739.602000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 00:25:28.000' , 84741.531000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 00:55:33.000' , 84743.437000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 01:25:38.000' , 84745.402000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 01:55:42.000' , 84747.248000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 02:25:48.000' , 84749.159000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 02:55:53.000' , 84751.068000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 03:25:58.000' , 84752.981000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 03:56:03.000' , 84754.783000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 04:26:08.000' , 84756.568000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 04:56:14.000' , 84758.432000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 05:26:18.000' , 84760.321000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 05:56:24.000' , 84762.188000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 06:26:29.000' , 84763.922000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 06:56:34.000' , 84765.814000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 07:26:39.000' , 84767.802000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 07:56:44.000' , 84769.895000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 08:26:49.000' , 84771.819000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 08:56:55.000' , 84773.953000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 09:27:00.000' , 84775.932000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 09:57:05.000' , 84778.12000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 10:27:10.000' , 84780.321000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 10:57:15.000' , 84782.514000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 11:27:20.000' , 84784.708000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 11:57:24.000' , 84786.915000)
insert into dbo.Table_1 values ( 4092, '2009-06-01 12:02:25.000' , 84787.28500)
1 июн 09, 12:13    [7250744]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3264
типа так
CREATE TABLE #Table_1(
[meas] [int] NULL,
[dd] [datetime] NULL,
[measure] [decimal](18, 6) NULL
) ON [PRIMARY]

CREATE TABLE #REGIONS 
(
  RG int
 ,FTIME datetime
 ,TTIME datetime
)

SET NOCOUNT ON 
INSERT #REGIONS ( RG , FTIME , TTIME)
SELECT 0, '0:00',  '8:59:59'UNION ALL
SELECT 1, '8:00', '11:59:59'UNION ALL
SELECT 2, '11:00','18:59:59'UNION ALL
SELECT 3, '18:00','20:59:59'UNION ALL
SELECT 0, '21:00','23:59:59'


insert into #Table_1 values ( 4091, '2009-05-29 12:25:29.000' , 79547.775000)
insert into #Table_1 values ( 4091, '2009-05-29 12:55:30.000' , 79549.94000)
insert into #Table_1 values ( 4091, '2009-05-29 13:25:32.000' , 79552.288000)
insert into #Table_1 values ( 4091, '2009-05-29 13:55:33.000' , 79554.444000)
insert into #Table_1 values ( 4091, '2009-05-29 14:25:34.000' , 79556.802000)
insert into #Table_1 values ( 4091, '2009-05-29 14:55:35.000' , 79559.14000)
insert into #Table_1 values ( 4091, '2009-05-29 15:25:36.000' , 79561.279000)
insert into #Table_1 values ( 4091, '2009-05-29 15:55:38.000' , 79563.419000)
insert into #Table_1 values ( 4091, '2009-05-29 16:25:39.000' , 79565.498000)
insert into #Table_1 values ( 4091, '2009-05-29 16:55:40.000' , 79567.641000)
insert into #Table_1 values ( 4091, '2009-05-29 17:25:41.000' , 79569.736000)
insert into #Table_1 values ( 4091, '2009-05-29 17:55:43.000' , 79571.714000)
insert into #Table_1 values ( 4091, '2009-05-29 18:25:44.000' , 79573.649000)
insert into #Table_1 values ( 4091, '2009-05-29 18:55:45.000' , 79575.538000)
insert into #Table_1 values ( 4091, '2009-05-29 19:25:46.000' , 79577.531000)
insert into #Table_1 values ( 4091, '2009-05-29 19:55:47.000' , 79579.664000)
insert into #Table_1 values ( 4091, '2009-05-29 20:25:49.000' , 79581.639000)
insert into #Table_1 values ( 4091, '2009-05-29 20:55:50.000' , 79583.429000)
insert into #Table_1 values ( 4091, '2009-05-29 21:25:51.000' , 79585.514000)
insert into #Table_1 values ( 4091, '2009-05-29 21:55:52.000' , 79587.436000)
insert into #Table_1 values ( 4091, '2009-05-29 22:20:53.000' , 79589.165000)
insert into #Table_1 values ( 4091, '2009-05-29 22:55:54.000' , 79591.365000)
insert into #Table_1 values ( 4091, '2009-05-29 23:25:56.000' , 79593.322000)
insert into #Table_1 values ( 4091, '2009-05-29 23:55:57.000' , 79595.204000)
insert into #Table_1 values ( 4091, '2009-05-30 00:25:58.000' , 79597.149000)
insert into #Table_1 values ( 4091, '2009-05-30 00:55:59.000' , 79599.055000)
insert into #Table_1 values ( 4091, '2009-05-30 01:26:01.000' , 79600.927000)
insert into #Table_1 values ( 4091, '2009-05-30 01:56:02.000' , 79602.708000)
insert into #Table_1 values ( 4091, '2009-05-30 02:26:03.000' , 79604.747000)
insert into #Table_1 values ( 4091, '2009-05-30 02:51:04.000' , 79606.168000)
insert into #Table_1 values ( 4091, '2009-05-30 03:26:05.000' , 79608.354000)
insert into #Table_1 values ( 4091, '2009-05-30 03:56:06.000' , 79610.313000)
insert into #Table_1 values ( 4091, '2009-05-30 04:26:08.000' , 79612.103000)
insert into #Table_1 values ( 4091, '2009-05-30 04:56:09.000' , 79613.928000)
insert into #Table_1 values ( 4091, '2009-05-30 05:26:10.000' , 79615.733000)
insert into #Table_1 values ( 4091, '2009-05-30 05:56:11.000' , 79617.634000)
insert into #Table_1 values ( 4091, '2009-05-30 06:26:12.000' , 79619.456000)
insert into #Table_1 values ( 4091, '2009-05-30 06:56:14.000' , 79621.25000)
insert into #Table_1 values ( 4091, '2009-05-30 07:26:15.000' , 79623.292000)
insert into #Table_1 values ( 4091, '2009-05-30 07:56:16.000' , 79625.039000)
insert into #Table_1 values ( 4091, '2009-05-30 08:26:17.000' , 79627.049000)
insert into #Table_1 values ( 4091, '2009-05-30 08:56:18.000' , 79628.971000)
insert into #Table_1 values ( 4091, '2009-05-30 09:26:20.000' , 79631.064000)
insert into #Table_1 values ( 4091, '2009-05-30 09:56:21.000' , 79633.178000)
insert into #Table_1 values ( 4091, '2009-05-30 10:26:22.000' , 79635.335000)
insert into #Table_1 values ( 4091, '2009-05-30 10:56:23.000' , 79637.442000)
insert into #Table_1 values ( 4091, '2009-05-30 11:26:24.000' , 79639.701000)
insert into #Table_1 values ( 4091, '2009-05-30 11:56:26.000' , 79641.874000)
insert into #Table_1 values ( 4091, '2009-05-30 12:26:27.000' , 79644.022000)
insert into #Table_1 values ( 4091, '2009-05-30 12:56:28.000' , 79646.333000)
insert into #Table_1 values ( 4091, '2009-05-30 13:26:29.000' , 79648.508000)
insert into #Table_1 values ( 4091, '2009-05-30 13:56:30.000' , 79650.693000)
insert into #Table_1 values ( 4091, '2009-05-30 14:26:32.000' , 79653.039000)
insert into #Table_1 values ( 4091, '2009-05-30 14:56:33.000' , 79655.18000)
insert into #Table_1 values ( 4091, '2009-05-30 15:26:34.000' , 79657.535000)
insert into #Table_1 values ( 4091, '2009-05-30 15:56:35.000' , 79659.901000)
insert into #Table_1 values ( 4091, '2009-05-30 16:26:36.000' , 79662.027000)
insert into #Table_1 values ( 4091, '2009-05-30 16:56:38.000' , 79664.388000)
insert into #Table_1 values ( 4091, '2009-05-30 17:26:39.000' , 79666.744000)
insert into #Table_1 values ( 4091, '2009-05-30 17:56:40.000' , 79668.878000)
insert into #Table_1 values ( 4091, '2009-05-30 18:26:41.000' , 79671.226000)
insert into #Table_1 values ( 4091, '2009-05-30 18:56:42.000' , 79673.342000)
insert into #Table_1 values ( 4091, '2009-05-30 19:26:43.000' , 79675.687000)
insert into #Table_1 values ( 4091, '2009-05-30 19:56:45.000' , 79678.051000)
insert into #Table_1 values ( 4091, '2009-05-30 20:26:46.000' , 79680.126000)
insert into #Table_1 values ( 4091, '2009-05-30 20:56:47.000' , 79682.24000)
insert into #Table_1 values ( 4091, '2009-05-30 21:26:48.000' , 79684.421000)
insert into #Table_1 values ( 4091, '2009-05-30 21:56:49.000' , 79686.454000)
insert into #Table_1 values ( 4091, '2009-05-30 22:26:51.000' , 79688.564000)
insert into #Table_1 values ( 4091, '2009-05-30 22:56:52.000' , 79690.643000)
insert into #Table_1 values ( 4091, '2009-05-30 23:26:53.000' , 79692.555000)
insert into #Table_1 values ( 4091, '2009-05-30 23:56:54.000' , 79694.476000)
insert into #Table_1 values ( 4091, '2009-05-31 00:26:55.000' , 79696.372000)
insert into #Table_1 values ( 4091, '2009-05-31 00:56:57.000' , 79698.285000)
insert into #Table_1 values ( 4091, '2009-05-31 01:26:58.000' , 79700.171000)
insert into #Table_1 values ( 4091, '2009-05-31 01:56:59.000' , 79702.096000)
insert into #Table_1 values ( 4091, '2009-05-31 02:27:00.000' , 79703.898000)
insert into #Table_1 values ( 4091, '2009-05-31 02:57:11.000' , 79705.712000)
insert into #Table_1 values ( 4091, '2009-05-31 03:27:12.000' , 79707.478000)
insert into #Table_1 values ( 4091, '2009-05-31 03:57:14.000' , 79709.192000)
insert into #Table_1 values ( 4091, '2009-05-31 04:27:15.000' , 79710.89000)
insert into #Table_1 values ( 4091, '2009-05-31 04:57:16.000' , 79712.516000)
insert into #Table_1 values ( 4091, '2009-05-31 05:27:17.000' , 79714.397000)
insert into #Table_1 values ( 4091, '2009-05-31 05:57:18.000' , 79716.153000)
insert into #Table_1 values ( 4091, '2009-05-31 06:27:20.000' , 79718.033000)
insert into #Table_1 values ( 4091, '2009-05-31 06:57:21.000' , 79719.83000)
insert into #Table_1 values ( 4091, '2009-05-31 07:27:22.000' , 79721.74000)
insert into #Table_1 values ( 4091, '2009-05-31 07:57:23.000' , 79723.667000)
insert into #Table_1 values ( 4091, '2009-05-31 08:27:24.000' , 79725.652000)
insert into #Table_1 values ( 4091, '2009-05-31 08:57:26.000' , 79727.525000)
insert into #Table_1 values ( 4091, '2009-05-31 09:27:27.000' , 79729.669000)
insert into #Table_1 values ( 4091, '2009-05-31 09:57:28.000' , 79731.968000)
insert into #Table_1 values ( 4091, '2009-05-31 10:27:29.000' , 79734.083000)
insert into #Table_1 values ( 4091, '2009-05-31 10:57:30.000' , 79736.251000)
insert into #Table_1 values ( 4091, '2009-05-31 11:27:32.000' , 79738.415000)
insert into #Table_1 values ( 4091, '2009-05-31 11:57:33.000' , 79740.743000)
insert into #Table_1 values ( 4091, '2009-05-31 12:27:34.000' , 79742.882000)
insert into #Table_1 values ( 4091, '2009-05-31 12:57:35.000' , 79745.022000)
insert into #Table_1 values ( 4091, '2009-05-31 13:27:36.000' , 79747.368000)
insert into #Table_1 values ( 4091, '2009-05-31 13:57:38.000' , 79749.499000)
insert into #Table_1 values ( 4091, '2009-05-31 14:27:39.000' , 79751.86000)
insert into #Table_1 values ( 4091, '2009-05-31 14:57:40.000' , 79754.003000)
insert into #Table_1 values ( 4091, '2009-05-31 15:27:41.000' , 79756.235000)
insert into #Table_1 values ( 4091, '2009-05-31 15:57:42.000' , 79758.598000)
insert into #Table_1 values ( 4091, '2009-05-31 16:27:44.000' , 79760.961000)
insert into #Table_1 values ( 4091, '2009-05-31 16:57:45.000' , 79763.326000)
insert into #Table_1 values ( 4091, '2009-05-31 17:27:46.000' , 79765.494000)
insert into #Table_1 values ( 4091, '2009-05-31 17:57:47.000' , 79767.848000)
insert into #Table_1 values ( 4091, '2009-05-31 18:27:48.000' , 79770.206000)
insert into #Table_1 values ( 4091, '2009-05-31 18:57:50.000' , 79772.564000)
insert into #Table_1 values ( 4091, '2009-05-31 19:27:51.000' , 79774.914000)
insert into #Table_1 values ( 4091, '2009-05-31 19:57:52.000' , 79777.265000)
insert into #Table_1 values ( 4091, '2009-05-31 20:27:53.000' , 79779.625000)
insert into #Table_1 values ( 4091, '2009-05-31 20:57:55.000' , 79781.618000)
insert into #Table_1 values ( 4091, '2009-05-31 21:27:56.000' , 79783.91000)
insert into #Table_1 values ( 4091, '2009-05-31 21:57:57.000' , 79786.078000)
insert into #Table_1 values ( 4091, '2009-05-31 22:27:58.000' , 79788.144000)
insert into #Table_1 values ( 4091, '2009-05-31 22:57:59.000' , 79790.164000)
insert into #Table_1 values ( 4091, '2009-05-31 23:28:01.000' , 79792.142000)
insert into #Table_1 values ( 4091, '2009-05-31 23:58:02.000' , 79794.228000)
insert into #Table_1 values ( 4091, '2009-06-01 00:28:03.000' , 79796.142000)
insert into #Table_1 values ( 4091, '2009-06-01 00:58:04.000' , 79798.1000)
insert into #Table_1 values ( 4091, '2009-06-01 01:28:05.000' , 79799.936000)
insert into #Table_1 values ( 4091, '2009-06-01 01:58:06.000' , 79801.761000)
insert into #Table_1 values ( 4091, '2009-06-01 02:28:08.000' , 79803.53000)
insert into #Table_1 values ( 4091, '2009-06-01 02:58:09.000' , 79805.463000)
insert into #Table_1 values ( 4091, '2009-06-01 03:28:10.000' , 79807.325000)
insert into #Table_1 values ( 4091, '2009-06-01 03:58:11.000' , 79809.142000)
insert into #Table_1 values ( 4091, '2009-06-01 04:28:16.000' , 79810.996000)
insert into #Table_1 values ( 4091, '2009-06-01 04:58:21.000' , 79812.736000)
insert into #Table_1 values ( 4091, '2009-06-01 05:23:22.000' , 79814.238000)
insert into #Table_1 values ( 4091, '2009-06-01 05:59:41.000' , 79816.562000)
insert into #Table_1 values ( 4091, '2009-06-01 06:29:43.000' , 79818.342000)
insert into #Table_1 values ( 4091, '2009-06-01 06:59:44.000' , 79819.995000)
insert into #Table_1 values ( 4091, '2009-06-01 07:29:45.000' , 79822.072000)
insert into #Table_1 values ( 4091, '2009-06-01 07:59:46.000' , 79824.121000)
insert into #Table_1 values ( 4091, '2009-06-01 08:29:47.000' , 79826.206000)
insert into #Table_1 values ( 4091, '2009-06-01 08:59:49.000' , 79828.352000)
insert into #Table_1 values ( 4091, '2009-06-01 09:29:50.000' , 79830.501000)
insert into #Table_1 values ( 4091, '2009-06-01 09:59:51.000' , 79832.845000)
insert into #Table_1 values ( 4091, '2009-06-01 10:29:52.000' , 79835.007000)
insert into #Table_1 values ( 4091, '2009-06-01 10:59:53.000' , 79837.35000)
insert into #Table_1 values ( 4091, '2009-06-01 11:29:55.000' , 79839.531000)
insert into #Table_1 values ( 4091, '2009-06-01 11:59:56.000' , 79841.895000)
insert into #Table_1 values ( 4092, '2009-05-29 12:25:29.000' , 84494.19000)
insert into #Table_1 values ( 4092, '2009-05-29 12:55:33.000' , 84496.425000)
insert into #Table_1 values ( 4092, '2009-05-29 13:25:37.000' , 84498.661000)
insert into #Table_1 values ( 4092, '2009-05-29 13:55:41.000' , 84500.902000)
insert into #Table_1 values ( 4092, '2009-05-29 14:25:44.000' , 84503.133000)
insert into #Table_1 values ( 4092, '2009-05-29 14:55:49.000' , 84505.34000)
insert into #Table_1 values ( 4092, '2009-05-29 15:25:54.000' , 84507.556000)
insert into #Table_1 values ( 4092, '2009-05-29 15:55:58.000' , 84509.769000)
insert into #Table_1 values ( 4092, '2009-05-29 16:26:03.000' , 84511.678000)
insert into #Table_1 values ( 4092, '2009-05-29 16:56:08.000' , 84513.366000)
insert into #Table_1 values ( 4092, '2009-05-29 17:26:11.000' , 0.000)
insert into #Table_1 values ( 4092, '2009-05-29 17:56:19.000' , 84514.11000)
insert into #Table_1 values ( 4092, '2009-05-29 18:26:28.000' , 84516.726000)
insert into #Table_1 values ( 4092, '2009-05-29 18:56:32.000' , 84519.235000)
insert into #Table_1 values ( 4092, '2009-05-29 19:21:36.000' , 84521.132000)
insert into #Table_1 values ( 4092, '2009-05-29 19:56:42.000' , 84523.756000)
insert into #Table_1 values ( 4092, '2009-05-29 20:26:47.000' , 84525.836000)
insert into #Table_1 values ( 4092, '2009-05-29 20:56:51.000' , 84527.88000)
insert into #Table_1 values ( 4092, '2009-05-29 21:26:56.000' , 84529.84000)
insert into #Table_1 values ( 4092, '2009-05-29 21:57:01.000' , 84531.816000)
insert into #Table_1 values ( 4092, '2009-05-29 22:27:06.000' , 84533.873000)
insert into #Table_1 values ( 4092, '2009-05-29 22:57:11.000' , 84536.069000)
insert into #Table_1 values ( 4092, '2009-05-29 23:27:17.000' , 84538.011000)
insert into #Table_1 values ( 4092, '2009-05-29 23:57:21.000' , 84539.957000)
insert into #Table_1 values ( 4092, '2009-05-30 00:27:26.000' , 84541.994000)
insert into #Table_1 values ( 4092, '2009-05-30 00:57:31.000' , 84543.974000)
insert into #Table_1 values ( 4092, '2009-05-30 01:27:36.000' , 84545.926000)
insert into #Table_1 values ( 4092, '2009-05-30 01:57:41.000' , 84547.935000)
insert into #Table_1 values ( 4092, '2009-05-30 02:27:46.000' , 84549.829000)
insert into #Table_1 values ( 4092, '2009-05-30 02:57:52.000' , 84551.698000)
insert into #Table_1 values ( 4092, '2009-05-30 03:27:56.000' , 84553.591000)
insert into #Table_1 values ( 4092, '2009-05-30 03:58:01.000' , 84555.422000)
insert into #Table_1 values ( 4092, '2009-05-30 04:28:07.000' , 84557.101000)
insert into #Table_1 values ( 4092, '2009-05-30 04:58:11.000' , 84558.985000)
insert into #Table_1 values ( 4092, '2009-05-30 05:28:17.000' , 84560.869000)
insert into #Table_1 values ( 4092, '2009-05-30 05:58:29.000' , 84562.757000)
insert into #Table_1 values ( 4092, '2009-05-30 06:28:31.000' , 84564.652000)
insert into #Table_1 values ( 4092, '2009-05-30 06:58:36.000' , 84566.549000)
insert into #Table_1 values ( 4092, '2009-05-30 07:28:41.000' , 84568.43000)
insert into #Table_1 values ( 4092, '2009-05-30 07:58:47.000' , 84570.328000)
insert into #Table_1 values ( 4092, '2009-05-30 08:28:52.000' , 84572.232000)
insert into #Table_1 values ( 4092, '2009-05-30 08:58:57.000' , 84574.333000)
insert into #Table_1 values ( 4092, '2009-05-30 09:29:00.000' , 84576.341000)
insert into #Table_1 values ( 4092, '2009-05-30 09:59:06.000' , 84578.274000)
insert into #Table_1 values ( 4092, '2009-05-30 10:29:11.000' , 84580.469000)
insert into #Table_1 values ( 4092, '2009-05-30 10:59:17.000' , 84582.677000)
insert into #Table_1 values ( 4092, '2009-05-30 11:29:22.000' , 84584.887000)
insert into #Table_1 values ( 4092, '2009-05-30 11:59:26.000' , 84587.113000)
insert into #Table_1 values ( 4092, '2009-05-30 12:29:31.000' , 84589.331000)
insert into #Table_1 values ( 4092, '2009-05-30 12:59:36.000' , 84591.572000)
insert into #Table_1 values ( 4092, '2009-05-30 13:29:40.000' , 84593.807000)
insert into #Table_1 values ( 4092, '2009-05-30 13:59:45.000' , 84596.043000)
insert into #Table_1 values ( 4092, '2009-05-30 14:29:51.000' , 84598.294000)
insert into #Table_1 values ( 4092, '2009-05-30 14:59:55.000' , 84600.533000)
insert into #Table_1 values ( 4092, '2009-05-30 15:24:59.000' , 84602.402000)
insert into #Table_1 values ( 4092, '2009-05-30 15:55:05.000' , 84604.639000)
insert into #Table_1 values ( 4092, '2009-05-30 16:25:10.000' , 84606.873000)
insert into #Table_1 values ( 4092, '2009-05-30 16:55:15.000' , 84609.147000)
insert into #Table_1 values ( 4092, '2009-05-30 17:25:20.000' , 84611.412000)
insert into #Table_1 values ( 4092, '2009-05-30 17:55:26.000' , 84613.652000)
insert into #Table_1 values ( 4092, '2009-05-30 18:25:30.000' , 84615.893000)
insert into #Table_1 values ( 4092, '2009-05-30 18:55:35.000' , 84618.138000)
insert into #Table_1 values ( 4092, '2009-05-30 19:25:40.000' , 84620.374000)
insert into #Table_1 values ( 4092, '2009-05-30 19:55:45.000' , 84622.6000)
insert into #Table_1 values ( 4092, '2009-05-30 20:25:50.000' , 84624.827000)
insert into #Table_1 values ( 4092, '2009-05-30 20:55:55.000' , 84627.058000)
insert into #Table_1 values ( 4092, '2009-05-30 21:26:00.000' , 84629.288000)
insert into #Table_1 values ( 4092, '2009-05-30 21:56:05.000' , 84631.293000)
insert into #Table_1 values ( 4092, '2009-05-30 22:26:10.000' , 84633.511000)
insert into #Table_1 values ( 4092, '2009-05-30 22:56:13.000' , 84635.511000)
insert into #Table_1 values ( 4092, '2009-05-30 23:26:19.000' , 84637.472000)
insert into #Table_1 values ( 4092, '2009-05-30 23:56:24.000' , 84639.322000)
insert into #Table_1 values ( 4092, '2009-05-31 00:26:28.000' , 84641.069000)
insert into #Table_1 values ( 4092, '2009-05-31 00:56:33.000' , 84643.002000)
insert into #Table_1 values ( 4092, '2009-05-31 01:26:39.000' , 84644.828000)
insert into #Table_1 values ( 4092, '2009-05-31 01:56:43.000' , 84646.624000)
insert into #Table_1 values ( 4092, '2009-05-31 02:26:49.000' , 84648.514000)
insert into #Table_1 values ( 4092, '2009-05-31 02:56:54.000' , 84650.307000)
insert into #Table_1 values ( 4092, '2009-05-31 03:27:00.000' , 84652.069000)
insert into #Table_1 values ( 4092, '2009-05-31 03:57:03.000' , 84653.982000)
insert into #Table_1 values ( 4092, '2009-05-31 04:27:09.000' , 84655.789000)
insert into #Table_1 values ( 4092, '2009-05-31 04:57:14.000' , 84657.52000)
insert into #Table_1 values ( 4092, '2009-05-31 05:27:20.000' , 84659.408000)
insert into #Table_1 values ( 4092, '2009-05-31 05:57:24.000' , 84661.155000)
insert into #Table_1 values ( 4092, '2009-05-31 06:27:29.000' , 84663.083000)
insert into #Table_1 values ( 4092, '2009-05-31 06:57:35.000' , 84665.023000)
insert into #Table_1 values ( 4092, '2009-05-31 07:27:40.000' , 84666.962000)
insert into #Table_1 values ( 4092, '2009-05-31 07:57:45.000' , 84668.926000)
insert into #Table_1 values ( 4092, '2009-05-31 08:27:50.000' , 84670.894000)
insert into #Table_1 values ( 4092, '2009-05-31 08:57:55.000' , 84673.09000)
insert into #Table_1 values ( 4092, '2009-05-31 09:27:59.000' , 84675.292000)
insert into #Table_1 values ( 4092, '2009-05-31 09:58:04.000' , 84677.505000)
insert into #Table_1 values ( 4092, '2009-05-31 10:28:09.000' , 84679.723000)
insert into #Table_1 values ( 4092, '2009-05-31 10:58:14.000' , 84681.962000)
insert into #Table_1 values ( 4092, '2009-05-31 11:28:19.000' , 84684.201000)
insert into #Table_1 values ( 4092, '2009-05-31 11:58:24.000' , 84686.462000)
insert into #Table_1 values ( 4092, '2009-05-31 12:29:01.000' , 84688.706000)
insert into #Table_1 values ( 4092, '2009-05-31 12:58:33.000' , 84690.964000)
insert into #Table_1 values ( 4092, '2009-05-31 13:28:38.000' , 84693.223000)
insert into #Table_1 values ( 4092, '2009-05-31 13:58:43.000' , 84695.492000)
insert into #Table_1 values ( 4092, '2009-05-31 14:28:48.000' , 84697.753000)
insert into #Table_1 values ( 4092, '2009-05-31 14:58:53.000' , 84700.006000)
insert into #Table_1 values ( 4092, '2009-05-31 15:28:58.000' , 84702.29000)
insert into #Table_1 values ( 4092, '2009-05-31 15:59:03.000' , 84704.566000)
insert into #Table_1 values ( 4092, '2009-05-31 16:29:08.000' , 84706.846000)
insert into #Table_1 values ( 4092, '2009-05-31 16:59:14.000' , 84709.134000)
insert into #Table_1 values ( 4092, '2009-05-31 17:29:18.000' , 84711.42000)
insert into #Table_1 values ( 4092, '2009-05-31 17:59:23.000' , 84713.699000)
insert into #Table_1 values ( 4092, '2009-05-31 18:29:28.000' , 84715.961000)
insert into #Table_1 values ( 4092, '2009-05-31 18:59:33.000' , 84718.218000)
insert into #Table_1 values ( 4092, '2009-05-31 19:29:38.000' , 84720.448000)
insert into #Table_1 values ( 4092, '2009-05-31 19:59:43.000' , 84722.694000)
insert into #Table_1 values ( 4092, '2009-05-31 20:29:47.000' , 84724.92000)
insert into #Table_1 values ( 4092, '2009-05-31 20:59:53.000' , 84727.144000)
insert into #Table_1 values ( 4092, '2009-05-31 21:29:58.000' , 84729.371000)
insert into #Table_1 values ( 4092, '2009-05-31 21:55:02.000' , 84731.224000)
insert into #Table_1 values ( 4092, '2009-05-31 22:25:07.000' , 84733.443000)
insert into #Table_1 values ( 4092, '2009-05-31 22:55:12.000' , 84735.595000)
insert into #Table_1 values ( 4092, '2009-05-31 23:25:18.000' , 84737.663000)
insert into #Table_1 values ( 4092, '2009-05-31 23:55:23.000' , 84739.602000)
insert into #Table_1 values ( 4092, '2009-06-01 00:25:28.000' , 84741.531000)
insert into #Table_1 values ( 4092, '2009-06-01 00:55:33.000' , 84743.437000)
insert into #Table_1 values ( 4092, '2009-06-01 01:25:38.000' , 84745.402000)
insert into #Table_1 values ( 4092, '2009-06-01 01:55:42.000' , 84747.248000)
insert into #Table_1 values ( 4092, '2009-06-01 02:25:48.000' , 84749.159000)
insert into #Table_1 values ( 4092, '2009-06-01 02:55:53.000' , 84751.068000)
insert into #Table_1 values ( 4092, '2009-06-01 03:25:58.000' , 84752.981000)
insert into #Table_1 values ( 4092, '2009-06-01 03:56:03.000' , 84754.783000)
insert into #Table_1 values ( 4092, '2009-06-01 04:26:08.000' , 84756.568000)
insert into #Table_1 values ( 4092, '2009-06-01 04:56:14.000' , 84758.432000)
insert into #Table_1 values ( 4092, '2009-06-01 05:26:18.000' , 84760.321000)
insert into #Table_1 values ( 4092, '2009-06-01 05:56:24.000' , 84762.188000)
insert into #Table_1 values ( 4092, '2009-06-01 06:26:29.000' , 84763.922000)
insert into #Table_1 values ( 4092, '2009-06-01 06:56:34.000' , 84765.814000)
insert into #Table_1 values ( 4092, '2009-06-01 07:26:39.000' , 84767.802000)
insert into #Table_1 values ( 4092, '2009-06-01 07:56:44.000' , 84769.895000)
insert into #Table_1 values ( 4092, '2009-06-01 08:26:49.000' , 84771.819000)
insert into #Table_1 values ( 4092, '2009-06-01 08:56:55.000' , 84773.953000)
insert into #Table_1 values ( 4092, '2009-06-01 09:27:00.000' , 84775.932000)
insert into #Table_1 values ( 4092, '2009-06-01 09:57:05.000' , 84778.12000)
insert into #Table_1 values ( 4092, '2009-06-01 10:27:10.000' , 84780.321000)
insert into #Table_1 values ( 4092, '2009-06-01 10:57:15.000' , 84782.514000)
insert into #Table_1 values ( 4092, '2009-06-01 11:27:20.000' , 84784.708000)
insert into #Table_1 values ( 4092, '2009-06-01 11:57:24.000' , 84786.915000)
insert into #Table_1 values ( 4092, '2009-06-01 12:02:25.000' , 84787.28500)

SET NOCOUNT OFF
 

SELECT 
  T2.RG
 ,SUM ( T1.[measure] ) [measure] 
FROM 
 #Table_1 T1
INNER JOIN  #REGIONS T2 ON (
 T1.dd BETWEEN 
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) + RIGHT(CONVERT (varchar(26),T2.FTIME,120), 9 ) 
 AND
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) + RIGHT(CONVERT (varchar(26),T2.TTIME,120), 9 ) 
)
  
GROUP BY  T2.RG

DROP TABLE #Table_1 
DROP TABLE #REGIONS
1 июн 09, 13:25    [7251130]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
beaver06
Member

Откуда:
Сообщений: 681
Ken@t
типа так
CREATE TABLE #Table_1(
[meas] [int] NULL,
[dd] [datetime] NULL,
[measure] [decimal](18, 6) NULL
) ON [PRIMARY]

CREATE TABLE #REGIONS 
(
  RG int
 ,FTIME datetime
 ,TTIME datetime
)

SET NOCOUNT ON 
INSERT #REGIONS ( RG , FTIME , TTIME)
SELECT 0, '0:00',  '8:59:59'UNION ALL
SELECT 1, '8:00', '11:59:59'UNION ALL
SELECT 2, '11:00','18:59:59'UNION ALL
SELECT 3, '18:00','20:59:59'UNION ALL
SELECT 0, '21:00','23:59:59'


insert into #Table_1 values ( 4091, '2009-05-29 12:25:29.000' , 79547.775000)
insert into #Table_1 values ( 4091, '2009-05-29 12:55:30.000' , 79549.94000)
insert into #Table_1 values ( 4091, '2009-05-29 13:25:32.000' , 79552.288000)
insert into #Table_1 values ( 4091, '2009-05-29 13:55:33.000' , 79554.444000)
insert into #Table_1 values ( 4091, '2009-05-29 14:25:34.000' , 79556.802000)
insert into #Table_1 values ( 4091, '2009-05-29 14:55:35.000' , 79559.14000)
insert into #Table_1 values ( 4091, '2009-05-29 15:25:36.000' , 79561.279000)
insert into #Table_1 values ( 4091, '2009-05-29 15:55:38.000' , 79563.419000)
insert into #Table_1 values ( 4091, '2009-05-29 16:25:39.000' , 79565.498000)
insert into #Table_1 values ( 4091, '2009-05-29 16:55:40.000' , 79567.641000)
insert into #Table_1 values ( 4091, '2009-05-29 17:25:41.000' , 79569.736000)
insert into #Table_1 values ( 4091, '2009-05-29 17:55:43.000' , 79571.714000)
insert into #Table_1 values ( 4091, '2009-05-29 18:25:44.000' , 79573.649000)
insert into #Table_1 values ( 4091, '2009-05-29 18:55:45.000' , 79575.538000)
insert into #Table_1 values ( 4091, '2009-05-29 19:25:46.000' , 79577.531000)
insert into #Table_1 values ( 4091, '2009-05-29 19:55:47.000' , 79579.664000)
insert into #Table_1 values ( 4091, '2009-05-29 20:25:49.000' , 79581.639000)
insert into #Table_1 values ( 4091, '2009-05-29 20:55:50.000' , 79583.429000)
insert into #Table_1 values ( 4091, '2009-05-29 21:25:51.000' , 79585.514000)
insert into #Table_1 values ( 4091, '2009-05-29 21:55:52.000' , 79587.436000)
insert into #Table_1 values ( 4091, '2009-05-29 22:20:53.000' , 79589.165000)
insert into #Table_1 values ( 4091, '2009-05-29 22:55:54.000' , 79591.365000)
insert into #Table_1 values ( 4091, '2009-05-29 23:25:56.000' , 79593.322000)
insert into #Table_1 values ( 4091, '2009-05-29 23:55:57.000' , 79595.204000)
insert into #Table_1 values ( 4091, '2009-05-30 00:25:58.000' , 79597.149000)
insert into #Table_1 values ( 4091, '2009-05-30 00:55:59.000' , 79599.055000)
insert into #Table_1 values ( 4091, '2009-05-30 01:26:01.000' , 79600.927000)
insert into #Table_1 values ( 4091, '2009-05-30 01:56:02.000' , 79602.708000)
insert into #Table_1 values ( 4091, '2009-05-30 02:26:03.000' , 79604.747000)
insert into #Table_1 values ( 4091, '2009-05-30 02:51:04.000' , 79606.168000)
insert into #Table_1 values ( 4091, '2009-05-30 03:26:05.000' , 79608.354000)
insert into #Table_1 values ( 4091, '2009-05-30 03:56:06.000' , 79610.313000)
insert into #Table_1 values ( 4091, '2009-05-30 04:26:08.000' , 79612.103000)
insert into #Table_1 values ( 4091, '2009-05-30 04:56:09.000' , 79613.928000)
insert into #Table_1 values ( 4091, '2009-05-30 05:26:10.000' , 79615.733000)
insert into #Table_1 values ( 4091, '2009-05-30 05:56:11.000' , 79617.634000)
insert into #Table_1 values ( 4091, '2009-05-30 06:26:12.000' , 79619.456000)
insert into #Table_1 values ( 4091, '2009-05-30 06:56:14.000' , 79621.25000)
insert into #Table_1 values ( 4091, '2009-05-30 07:26:15.000' , 79623.292000)
insert into #Table_1 values ( 4091, '2009-05-30 07:56:16.000' , 79625.039000)
insert into #Table_1 values ( 4091, '2009-05-30 08:26:17.000' , 79627.049000)
insert into #Table_1 values ( 4091, '2009-05-30 08:56:18.000' , 79628.971000)
insert into #Table_1 values ( 4091, '2009-05-30 09:26:20.000' , 79631.064000)
insert into #Table_1 values ( 4091, '2009-05-30 09:56:21.000' , 79633.178000)
insert into #Table_1 values ( 4091, '2009-05-30 10:26:22.000' , 79635.335000)
insert into #Table_1 values ( 4091, '2009-05-30 10:56:23.000' , 79637.442000)
insert into #Table_1 values ( 4091, '2009-05-30 11:26:24.000' , 79639.701000)
insert into #Table_1 values ( 4091, '2009-05-30 11:56:26.000' , 79641.874000)
insert into #Table_1 values ( 4091, '2009-05-30 12:26:27.000' , 79644.022000)
insert into #Table_1 values ( 4091, '2009-05-30 12:56:28.000' , 79646.333000)
insert into #Table_1 values ( 4091, '2009-05-30 13:26:29.000' , 79648.508000)
insert into #Table_1 values ( 4091, '2009-05-30 13:56:30.000' , 79650.693000)
insert into #Table_1 values ( 4091, '2009-05-30 14:26:32.000' , 79653.039000)
insert into #Table_1 values ( 4091, '2009-05-30 14:56:33.000' , 79655.18000)
insert into #Table_1 values ( 4091, '2009-05-30 15:26:34.000' , 79657.535000)
insert into #Table_1 values ( 4091, '2009-05-30 15:56:35.000' , 79659.901000)
insert into #Table_1 values ( 4091, '2009-05-30 16:26:36.000' , 79662.027000)
insert into #Table_1 values ( 4091, '2009-05-30 16:56:38.000' , 79664.388000)
insert into #Table_1 values ( 4091, '2009-05-30 17:26:39.000' , 79666.744000)
insert into #Table_1 values ( 4091, '2009-05-30 17:56:40.000' , 79668.878000)
insert into #Table_1 values ( 4091, '2009-05-30 18:26:41.000' , 79671.226000)
insert into #Table_1 values ( 4091, '2009-05-30 18:56:42.000' , 79673.342000)
insert into #Table_1 values ( 4091, '2009-05-30 19:26:43.000' , 79675.687000)
insert into #Table_1 values ( 4091, '2009-05-30 19:56:45.000' , 79678.051000)
insert into #Table_1 values ( 4091, '2009-05-30 20:26:46.000' , 79680.126000)
insert into #Table_1 values ( 4091, '2009-05-30 20:56:47.000' , 79682.24000)
insert into #Table_1 values ( 4091, '2009-05-30 21:26:48.000' , 79684.421000)
insert into #Table_1 values ( 4091, '2009-05-30 21:56:49.000' , 79686.454000)
insert into #Table_1 values ( 4091, '2009-05-30 22:26:51.000' , 79688.564000)
insert into #Table_1 values ( 4091, '2009-05-30 22:56:52.000' , 79690.643000)
insert into #Table_1 values ( 4091, '2009-05-30 23:26:53.000' , 79692.555000)
insert into #Table_1 values ( 4091, '2009-05-30 23:56:54.000' , 79694.476000)
insert into #Table_1 values ( 4091, '2009-05-31 00:26:55.000' , 79696.372000)
insert into #Table_1 values ( 4091, '2009-05-31 00:56:57.000' , 79698.285000)
insert into #Table_1 values ( 4091, '2009-05-31 01:26:58.000' , 79700.171000)
insert into #Table_1 values ( 4091, '2009-05-31 01:56:59.000' , 79702.096000)
insert into #Table_1 values ( 4091, '2009-05-31 02:27:00.000' , 79703.898000)
insert into #Table_1 values ( 4091, '2009-05-31 02:57:11.000' , 79705.712000)
insert into #Table_1 values ( 4091, '2009-05-31 03:27:12.000' , 79707.478000)
insert into #Table_1 values ( 4091, '2009-05-31 03:57:14.000' , 79709.192000)
insert into #Table_1 values ( 4091, '2009-05-31 04:27:15.000' , 79710.89000)
insert into #Table_1 values ( 4091, '2009-05-31 04:57:16.000' , 79712.516000)
insert into #Table_1 values ( 4091, '2009-05-31 05:27:17.000' , 79714.397000)
insert into #Table_1 values ( 4091, '2009-05-31 05:57:18.000' , 79716.153000)
insert into #Table_1 values ( 4091, '2009-05-31 06:27:20.000' , 79718.033000)
insert into #Table_1 values ( 4091, '2009-05-31 06:57:21.000' , 79719.83000)
insert into #Table_1 values ( 4091, '2009-05-31 07:27:22.000' , 79721.74000)
insert into #Table_1 values ( 4091, '2009-05-31 07:57:23.000' , 79723.667000)
insert into #Table_1 values ( 4091, '2009-05-31 08:27:24.000' , 79725.652000)
insert into #Table_1 values ( 4091, '2009-05-31 08:57:26.000' , 79727.525000)
insert into #Table_1 values ( 4091, '2009-05-31 09:27:27.000' , 79729.669000)
insert into #Table_1 values ( 4091, '2009-05-31 09:57:28.000' , 79731.968000)
insert into #Table_1 values ( 4091, '2009-05-31 10:27:29.000' , 79734.083000)
insert into #Table_1 values ( 4091, '2009-05-31 10:57:30.000' , 79736.251000)
insert into #Table_1 values ( 4091, '2009-05-31 11:27:32.000' , 79738.415000)
insert into #Table_1 values ( 4091, '2009-05-31 11:57:33.000' , 79740.743000)
insert into #Table_1 values ( 4091, '2009-05-31 12:27:34.000' , 79742.882000)
insert into #Table_1 values ( 4091, '2009-05-31 12:57:35.000' , 79745.022000)
insert into #Table_1 values ( 4091, '2009-05-31 13:27:36.000' , 79747.368000)
insert into #Table_1 values ( 4091, '2009-05-31 13:57:38.000' , 79749.499000)
insert into #Table_1 values ( 4091, '2009-05-31 14:27:39.000' , 79751.86000)
insert into #Table_1 values ( 4091, '2009-05-31 14:57:40.000' , 79754.003000)
insert into #Table_1 values ( 4091, '2009-05-31 15:27:41.000' , 79756.235000)
insert into #Table_1 values ( 4091, '2009-05-31 15:57:42.000' , 79758.598000)
insert into #Table_1 values ( 4091, '2009-05-31 16:27:44.000' , 79760.961000)
insert into #Table_1 values ( 4091, '2009-05-31 16:57:45.000' , 79763.326000)
insert into #Table_1 values ( 4091, '2009-05-31 17:27:46.000' , 79765.494000)
insert into #Table_1 values ( 4091, '2009-05-31 17:57:47.000' , 79767.848000)
insert into #Table_1 values ( 4091, '2009-05-31 18:27:48.000' , 79770.206000)
insert into #Table_1 values ( 4091, '2009-05-31 18:57:50.000' , 79772.564000)
insert into #Table_1 values ( 4091, '2009-05-31 19:27:51.000' , 79774.914000)
insert into #Table_1 values ( 4091, '2009-05-31 19:57:52.000' , 79777.265000)
insert into #Table_1 values ( 4091, '2009-05-31 20:27:53.000' , 79779.625000)
insert into #Table_1 values ( 4091, '2009-05-31 20:57:55.000' , 79781.618000)
insert into #Table_1 values ( 4091, '2009-05-31 21:27:56.000' , 79783.91000)
insert into #Table_1 values ( 4091, '2009-05-31 21:57:57.000' , 79786.078000)
insert into #Table_1 values ( 4091, '2009-05-31 22:27:58.000' , 79788.144000)
insert into #Table_1 values ( 4091, '2009-05-31 22:57:59.000' , 79790.164000)
insert into #Table_1 values ( 4091, '2009-05-31 23:28:01.000' , 79792.142000)
insert into #Table_1 values ( 4091, '2009-05-31 23:58:02.000' , 79794.228000)
insert into #Table_1 values ( 4091, '2009-06-01 00:28:03.000' , 79796.142000)
insert into #Table_1 values ( 4091, '2009-06-01 00:58:04.000' , 79798.1000)
insert into #Table_1 values ( 4091, '2009-06-01 01:28:05.000' , 79799.936000)
insert into #Table_1 values ( 4091, '2009-06-01 01:58:06.000' , 79801.761000)
insert into #Table_1 values ( 4091, '2009-06-01 02:28:08.000' , 79803.53000)
insert into #Table_1 values ( 4091, '2009-06-01 02:58:09.000' , 79805.463000)
insert into #Table_1 values ( 4091, '2009-06-01 03:28:10.000' , 79807.325000)
insert into #Table_1 values ( 4091, '2009-06-01 03:58:11.000' , 79809.142000)
insert into #Table_1 values ( 4091, '2009-06-01 04:28:16.000' , 79810.996000)
insert into #Table_1 values ( 4091, '2009-06-01 04:58:21.000' , 79812.736000)
insert into #Table_1 values ( 4091, '2009-06-01 05:23:22.000' , 79814.238000)
insert into #Table_1 values ( 4091, '2009-06-01 05:59:41.000' , 79816.562000)
insert into #Table_1 values ( 4091, '2009-06-01 06:29:43.000' , 79818.342000)
insert into #Table_1 values ( 4091, '2009-06-01 06:59:44.000' , 79819.995000)
insert into #Table_1 values ( 4091, '2009-06-01 07:29:45.000' , 79822.072000)
insert into #Table_1 values ( 4091, '2009-06-01 07:59:46.000' , 79824.121000)
insert into #Table_1 values ( 4091, '2009-06-01 08:29:47.000' , 79826.206000)
insert into #Table_1 values ( 4091, '2009-06-01 08:59:49.000' , 79828.352000)
insert into #Table_1 values ( 4091, '2009-06-01 09:29:50.000' , 79830.501000)
insert into #Table_1 values ( 4091, '2009-06-01 09:59:51.000' , 79832.845000)
insert into #Table_1 values ( 4091, '2009-06-01 10:29:52.000' , 79835.007000)
insert into #Table_1 values ( 4091, '2009-06-01 10:59:53.000' , 79837.35000)
insert into #Table_1 values ( 4091, '2009-06-01 11:29:55.000' , 79839.531000)
insert into #Table_1 values ( 4091, '2009-06-01 11:59:56.000' , 79841.895000)
insert into #Table_1 values ( 4092, '2009-05-29 12:25:29.000' , 84494.19000)
insert into #Table_1 values ( 4092, '2009-05-29 12:55:33.000' , 84496.425000)
insert into #Table_1 values ( 4092, '2009-05-29 13:25:37.000' , 84498.661000)
insert into #Table_1 values ( 4092, '2009-05-29 13:55:41.000' , 84500.902000)
insert into #Table_1 values ( 4092, '2009-05-29 14:25:44.000' , 84503.133000)
insert into #Table_1 values ( 4092, '2009-05-29 14:55:49.000' , 84505.34000)
insert into #Table_1 values ( 4092, '2009-05-29 15:25:54.000' , 84507.556000)
insert into #Table_1 values ( 4092, '2009-05-29 15:55:58.000' , 84509.769000)
insert into #Table_1 values ( 4092, '2009-05-29 16:26:03.000' , 84511.678000)
insert into #Table_1 values ( 4092, '2009-05-29 16:56:08.000' , 84513.366000)
insert into #Table_1 values ( 4092, '2009-05-29 17:26:11.000' , 0.000)
insert into #Table_1 values ( 4092, '2009-05-29 17:56:19.000' , 84514.11000)
insert into #Table_1 values ( 4092, '2009-05-29 18:26:28.000' , 84516.726000)
insert into #Table_1 values ( 4092, '2009-05-29 18:56:32.000' , 84519.235000)
insert into #Table_1 values ( 4092, '2009-05-29 19:21:36.000' , 84521.132000)
insert into #Table_1 values ( 4092, '2009-05-29 19:56:42.000' , 84523.756000)
insert into #Table_1 values ( 4092, '2009-05-29 20:26:47.000' , 84525.836000)
insert into #Table_1 values ( 4092, '2009-05-29 20:56:51.000' , 84527.88000)
insert into #Table_1 values ( 4092, '2009-05-29 21:26:56.000' , 84529.84000)
insert into #Table_1 values ( 4092, '2009-05-29 21:57:01.000' , 84531.816000)
insert into #Table_1 values ( 4092, '2009-05-29 22:27:06.000' , 84533.873000)
insert into #Table_1 values ( 4092, '2009-05-29 22:57:11.000' , 84536.069000)
insert into #Table_1 values ( 4092, '2009-05-29 23:27:17.000' , 84538.011000)
insert into #Table_1 values ( 4092, '2009-05-29 23:57:21.000' , 84539.957000)
insert into #Table_1 values ( 4092, '2009-05-30 00:27:26.000' , 84541.994000)
insert into #Table_1 values ( 4092, '2009-05-30 00:57:31.000' , 84543.974000)
insert into #Table_1 values ( 4092, '2009-05-30 01:27:36.000' , 84545.926000)
insert into #Table_1 values ( 4092, '2009-05-30 01:57:41.000' , 84547.935000)
insert into #Table_1 values ( 4092, '2009-05-30 02:27:46.000' , 84549.829000)
insert into #Table_1 values ( 4092, '2009-05-30 02:57:52.000' , 84551.698000)
insert into #Table_1 values ( 4092, '2009-05-30 03:27:56.000' , 84553.591000)
insert into #Table_1 values ( 4092, '2009-05-30 03:58:01.000' , 84555.422000)
insert into #Table_1 values ( 4092, '2009-05-30 04:28:07.000' , 84557.101000)
insert into #Table_1 values ( 4092, '2009-05-30 04:58:11.000' , 84558.985000)
insert into #Table_1 values ( 4092, '2009-05-30 05:28:17.000' , 84560.869000)
insert into #Table_1 values ( 4092, '2009-05-30 05:58:29.000' , 84562.757000)
insert into #Table_1 values ( 4092, '2009-05-30 06:28:31.000' , 84564.652000)
insert into #Table_1 values ( 4092, '2009-05-30 06:58:36.000' , 84566.549000)
insert into #Table_1 values ( 4092, '2009-05-30 07:28:41.000' , 84568.43000)
insert into #Table_1 values ( 4092, '2009-05-30 07:58:47.000' , 84570.328000)
insert into #Table_1 values ( 4092, '2009-05-30 08:28:52.000' , 84572.232000)
insert into #Table_1 values ( 4092, '2009-05-30 08:58:57.000' , 84574.333000)
insert into #Table_1 values ( 4092, '2009-05-30 09:29:00.000' , 84576.341000)
insert into #Table_1 values ( 4092, '2009-05-30 09:59:06.000' , 84578.274000)
insert into #Table_1 values ( 4092, '2009-05-30 10:29:11.000' , 84580.469000)
insert into #Table_1 values ( 4092, '2009-05-30 10:59:17.000' , 84582.677000)
insert into #Table_1 values ( 4092, '2009-05-30 11:29:22.000' , 84584.887000)
insert into #Table_1 values ( 4092, '2009-05-30 11:59:26.000' , 84587.113000)
insert into #Table_1 values ( 4092, '2009-05-30 12:29:31.000' , 84589.331000)
insert into #Table_1 values ( 4092, '2009-05-30 12:59:36.000' , 84591.572000)
insert into #Table_1 values ( 4092, '2009-05-30 13:29:40.000' , 84593.807000)
insert into #Table_1 values ( 4092, '2009-05-30 13:59:45.000' , 84596.043000)
insert into #Table_1 values ( 4092, '2009-05-30 14:29:51.000' , 84598.294000)
insert into #Table_1 values ( 4092, '2009-05-30 14:59:55.000' , 84600.533000)
insert into #Table_1 values ( 4092, '2009-05-30 15:24:59.000' , 84602.402000)
insert into #Table_1 values ( 4092, '2009-05-30 15:55:05.000' , 84604.639000)
insert into #Table_1 values ( 4092, '2009-05-30 16:25:10.000' , 84606.873000)
insert into #Table_1 values ( 4092, '2009-05-30 16:55:15.000' , 84609.147000)
insert into #Table_1 values ( 4092, '2009-05-30 17:25:20.000' , 84611.412000)
insert into #Table_1 values ( 4092, '2009-05-30 17:55:26.000' , 84613.652000)
insert into #Table_1 values ( 4092, '2009-05-30 18:25:30.000' , 84615.893000)
insert into #Table_1 values ( 4092, '2009-05-30 18:55:35.000' , 84618.138000)
insert into #Table_1 values ( 4092, '2009-05-30 19:25:40.000' , 84620.374000)
insert into #Table_1 values ( 4092, '2009-05-30 19:55:45.000' , 84622.6000)
insert into #Table_1 values ( 4092, '2009-05-30 20:25:50.000' , 84624.827000)
insert into #Table_1 values ( 4092, '2009-05-30 20:55:55.000' , 84627.058000)
insert into #Table_1 values ( 4092, '2009-05-30 21:26:00.000' , 84629.288000)
insert into #Table_1 values ( 4092, '2009-05-30 21:56:05.000' , 84631.293000)
insert into #Table_1 values ( 4092, '2009-05-30 22:26:10.000' , 84633.511000)
insert into #Table_1 values ( 4092, '2009-05-30 22:56:13.000' , 84635.511000)
insert into #Table_1 values ( 4092, '2009-05-30 23:26:19.000' , 84637.472000)
insert into #Table_1 values ( 4092, '2009-05-30 23:56:24.000' , 84639.322000)
insert into #Table_1 values ( 4092, '2009-05-31 00:26:28.000' , 84641.069000)
insert into #Table_1 values ( 4092, '2009-05-31 00:56:33.000' , 84643.002000)
insert into #Table_1 values ( 4092, '2009-05-31 01:26:39.000' , 84644.828000)
insert into #Table_1 values ( 4092, '2009-05-31 01:56:43.000' , 84646.624000)
insert into #Table_1 values ( 4092, '2009-05-31 02:26:49.000' , 84648.514000)
insert into #Table_1 values ( 4092, '2009-05-31 02:56:54.000' , 84650.307000)
insert into #Table_1 values ( 4092, '2009-05-31 03:27:00.000' , 84652.069000)
insert into #Table_1 values ( 4092, '2009-05-31 03:57:03.000' , 84653.982000)
insert into #Table_1 values ( 4092, '2009-05-31 04:27:09.000' , 84655.789000)
insert into #Table_1 values ( 4092, '2009-05-31 04:57:14.000' , 84657.52000)
insert into #Table_1 values ( 4092, '2009-05-31 05:27:20.000' , 84659.408000)
insert into #Table_1 values ( 4092, '2009-05-31 05:57:24.000' , 84661.155000)
insert into #Table_1 values ( 4092, '2009-05-31 06:27:29.000' , 84663.083000)
insert into #Table_1 values ( 4092, '2009-05-31 06:57:35.000' , 84665.023000)
insert into #Table_1 values ( 4092, '2009-05-31 07:27:40.000' , 84666.962000)
insert into #Table_1 values ( 4092, '2009-05-31 07:57:45.000' , 84668.926000)
insert into #Table_1 values ( 4092, '2009-05-31 08:27:50.000' , 84670.894000)
insert into #Table_1 values ( 4092, '2009-05-31 08:57:55.000' , 84673.09000)
insert into #Table_1 values ( 4092, '2009-05-31 09:27:59.000' , 84675.292000)
insert into #Table_1 values ( 4092, '2009-05-31 09:58:04.000' , 84677.505000)
insert into #Table_1 values ( 4092, '2009-05-31 10:28:09.000' , 84679.723000)
insert into #Table_1 values ( 4092, '2009-05-31 10:58:14.000' , 84681.962000)
insert into #Table_1 values ( 4092, '2009-05-31 11:28:19.000' , 84684.201000)
insert into #Table_1 values ( 4092, '2009-05-31 11:58:24.000' , 84686.462000)
insert into #Table_1 values ( 4092, '2009-05-31 12:29:01.000' , 84688.706000)
insert into #Table_1 values ( 4092, '2009-05-31 12:58:33.000' , 84690.964000)
insert into #Table_1 values ( 4092, '2009-05-31 13:28:38.000' , 84693.223000)
insert into #Table_1 values ( 4092, '2009-05-31 13:58:43.000' , 84695.492000)
insert into #Table_1 values ( 4092, '2009-05-31 14:28:48.000' , 84697.753000)
insert into #Table_1 values ( 4092, '2009-05-31 14:58:53.000' , 84700.006000)
insert into #Table_1 values ( 4092, '2009-05-31 15:28:58.000' , 84702.29000)
insert into #Table_1 values ( 4092, '2009-05-31 15:59:03.000' , 84704.566000)
insert into #Table_1 values ( 4092, '2009-05-31 16:29:08.000' , 84706.846000)
insert into #Table_1 values ( 4092, '2009-05-31 16:59:14.000' , 84709.134000)
insert into #Table_1 values ( 4092, '2009-05-31 17:29:18.000' , 84711.42000)
insert into #Table_1 values ( 4092, '2009-05-31 17:59:23.000' , 84713.699000)
insert into #Table_1 values ( 4092, '2009-05-31 18:29:28.000' , 84715.961000)
insert into #Table_1 values ( 4092, '2009-05-31 18:59:33.000' , 84718.218000)
insert into #Table_1 values ( 4092, '2009-05-31 19:29:38.000' , 84720.448000)
insert into #Table_1 values ( 4092, '2009-05-31 19:59:43.000' , 84722.694000)
insert into #Table_1 values ( 4092, '2009-05-31 20:29:47.000' , 84724.92000)
insert into #Table_1 values ( 4092, '2009-05-31 20:59:53.000' , 84727.144000)
insert into #Table_1 values ( 4092, '2009-05-31 21:29:58.000' , 84729.371000)
insert into #Table_1 values ( 4092, '2009-05-31 21:55:02.000' , 84731.224000)
insert into #Table_1 values ( 4092, '2009-05-31 22:25:07.000' , 84733.443000)
insert into #Table_1 values ( 4092, '2009-05-31 22:55:12.000' , 84735.595000)
insert into #Table_1 values ( 4092, '2009-05-31 23:25:18.000' , 84737.663000)
insert into #Table_1 values ( 4092, '2009-05-31 23:55:23.000' , 84739.602000)
insert into #Table_1 values ( 4092, '2009-06-01 00:25:28.000' , 84741.531000)
insert into #Table_1 values ( 4092, '2009-06-01 00:55:33.000' , 84743.437000)
insert into #Table_1 values ( 4092, '2009-06-01 01:25:38.000' , 84745.402000)
insert into #Table_1 values ( 4092, '2009-06-01 01:55:42.000' , 84747.248000)
insert into #Table_1 values ( 4092, '2009-06-01 02:25:48.000' , 84749.159000)
insert into #Table_1 values ( 4092, '2009-06-01 02:55:53.000' , 84751.068000)
insert into #Table_1 values ( 4092, '2009-06-01 03:25:58.000' , 84752.981000)
insert into #Table_1 values ( 4092, '2009-06-01 03:56:03.000' , 84754.783000)
insert into #Table_1 values ( 4092, '2009-06-01 04:26:08.000' , 84756.568000)
insert into #Table_1 values ( 4092, '2009-06-01 04:56:14.000' , 84758.432000)
insert into #Table_1 values ( 4092, '2009-06-01 05:26:18.000' , 84760.321000)
insert into #Table_1 values ( 4092, '2009-06-01 05:56:24.000' , 84762.188000)
insert into #Table_1 values ( 4092, '2009-06-01 06:26:29.000' , 84763.922000)
insert into #Table_1 values ( 4092, '2009-06-01 06:56:34.000' , 84765.814000)
insert into #Table_1 values ( 4092, '2009-06-01 07:26:39.000' , 84767.802000)
insert into #Table_1 values ( 4092, '2009-06-01 07:56:44.000' , 84769.895000)
insert into #Table_1 values ( 4092, '2009-06-01 08:26:49.000' , 84771.819000)
insert into #Table_1 values ( 4092, '2009-06-01 08:56:55.000' , 84773.953000)
insert into #Table_1 values ( 4092, '2009-06-01 09:27:00.000' , 84775.932000)
insert into #Table_1 values ( 4092, '2009-06-01 09:57:05.000' , 84778.12000)
insert into #Table_1 values ( 4092, '2009-06-01 10:27:10.000' , 84780.321000)
insert into #Table_1 values ( 4092, '2009-06-01 10:57:15.000' , 84782.514000)
insert into #Table_1 values ( 4092, '2009-06-01 11:27:20.000' , 84784.708000)
insert into #Table_1 values ( 4092, '2009-06-01 11:57:24.000' , 84786.915000)
insert into #Table_1 values ( 4092, '2009-06-01 12:02:25.000' , 84787.28500)

SET NOCOUNT OFF
 

SELECT 
  T2.RG
 ,SUM ( T1.[measure] ) [measure] 
FROM 
 #Table_1 T1
INNER JOIN  #REGIONS T2 ON (
 T1.dd BETWEEN 
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) + RIGHT(CONVERT (varchar(26),T2.FTIME,120), 9 ) 
 AND
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) + RIGHT(CONVERT (varchar(26),T2.TTIME,120), 9 ) 
)
  
GROUP BY  T2.RG

DROP TABLE #Table_1 
DROP TABLE #REGIONS


В итоге получается сумма показаний счетчика, а надо расход за эти периоды, т.е. разницу между max и min в интервале...
1 июн 09, 13:42    [7251219]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
beaver06
Member

Откуда:
Сообщений: 681
Как?
1 июн 09, 13:44    [7251236]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3264
может всё же опишите всю задачу ,
а надо расход за эти периоды, т.е. разницу между max и min в интервале...
каком интервале ?
чётко задачу сформулировать можете ?
1 июн 09, 13:45    [7251238]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3264
Может так ?
SELECT 
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) as DATE
 ,T2.RG
 ,SUM ( T1.[measure] ) [measure] 
FROM 
 #Table_1 T1
INNER JOIN  #REGIONS T2 ON (
 T1.dd BETWEEN 
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) + RIGHT(CONVERT (varchar(26),T2.FTIME,120), 9 ) 
 AND
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) + RIGHT(CONVERT (varchar(26),T2.TTIME,120), 9 ) 
)
  
GROUP BY  T2.RG,LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) 

ORDER BY LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8),T2.RG
1 июн 09, 13:49    [7251262]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3264
а , ступил )))) но всё равно - получить значения на граничные значения ))))
1 июн 09, 13:53    [7251281]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
beaver06
Member

Откуда:
Сообщений: 681
Ken@t
может всё же опишите всю задачу ,
а надо расход за эти периоды, т.е. разницу между max и min в интервале...
каком интервале ?
чётко задачу сформулировать можете ?


measure - это показание счетчика на момент времени dd
Надо рассчитать расход электроэнергии во временных интервалах, т.е. сколько израсходовалось электроэнергии - c 21.00 до 08.00
- с 08.00 до 11.00
- с 11.00 до 18.00
- с 18.00 до 21.00.
1 июн 09, 13:56    [7251301]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3264
Ну так , воспользуйтесь ф-ями MIN, MAX .
только это не всё, а если за период нет значений, как быть ?
1 июн 09, 14:09    [7251374]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
beaver06
Member

Откуда:
Сообщений: 681
Ken@t
Ну так , воспользуйтесь ф-ями MIN, MAX .
только это не всё, а если за период нет значений, как быть ?


Да это щас не важно. будем считать, что есть...
1 июн 09, 14:14    [7251402]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3264
SELECT 
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) as DATE
 ,T2.RG
 ,MIN ( T1.[measure] ) [MIN.measure] 
 ,MAX ( T1.[measure] ) [MAX.measure] 
 ,(MAX ( T1.[measure] ) -  MIN( T1.[measure] ) )[DELTA.measure] 
 FROM
 #Table_1 T1
INNER JOIN  #REGIONS T2 ON (
 T1.dd BETWEEN 
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) + RIGHT(CONVERT (varchar(26),T2.FTIME,120), 9 ) 
 AND
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) + RIGHT(CONVERT (varchar(26),T2.TTIME,120), 9 ) 
)
  
GROUP BY  T2.RG,LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) 

ORDER BY LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8),T2.RG
1 июн 09, 14:15    [7251413]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
beaver06
Member

Откуда:
Сообщений: 681
Ken@t
SELECT 
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) as DATE
 ,T2.RG
 ,MIN ( T1.[measure] ) [MIN.measure] 
 ,MAX ( T1.[measure] ) [MAX.measure] 
 ,(MAX ( T1.[measure] ) -  MIN( T1.[measure] ) )[DELTA.measure] 
 FROM
 #Table_1 T1
INNER JOIN  #REGIONS T2 ON (
 T1.dd BETWEEN 
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) + RIGHT(CONVERT (varchar(26),T2.FTIME,120), 9 ) 
 AND
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) + RIGHT(CONVERT (varchar(26),T2.TTIME,120), 9 ) 
)
  
GROUP BY  T2.RG,LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) 

ORDER BY LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8),T2.RG


Спасибо... большое...
1 июн 09, 14:18    [7251437]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
beaver06
Member

Откуда:
Сообщений: 681
Ken@t
SELECT 
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) as DATE
 ,T2.RG
 ,MIN ( T1.[measure] ) [MIN.measure] 
 ,MAX ( T1.[measure] ) [MAX.measure] 
 ,(MAX ( T1.[measure] ) -  MIN( T1.[measure] ) )[DELTA.measure] 
 FROM
 #Table_1 T1
INNER JOIN  #REGIONS T2 ON (
 T1.dd BETWEEN 
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) + RIGHT(CONVERT (varchar(26),T2.FTIME,120), 9 ) 
 AND
  LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) + RIGHT(CONVERT (varchar(26),T2.TTIME,120), 9 ) 
)
  
GROUP BY  T2.RG,LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8) 

ORDER BY LEFT(CONVERT(  VARCHAR (30) , T1.[dd] ,112 ),8),T2.RG


Только почему-то нет RG = 3
1 июн 09, 14:47    [7251606]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
aleks2
Guest
Ой... горе нам, ежели они так НА САМОМ ДЕЛЕ СЧИТАЮТ!!!

Иллюстрируем на пальцах:

insert into #Table_1 values ( 4092, '2009-06-01 07:56:44.000' , 84769.895000)
insert into #Table_1 values ( 4092, '2009-06-01 08:26:49.000' , 84771.819000)

Куды попадет этот расход?
1 июн 09, 14:48    [7251609]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
beaver06
Member

Откуда:
Сообщений: 681
aleks2
Ой... горе нам, ежели они так НА САМОМ ДЕЛЕ СЧИТАЮТ!!!

Иллюстрируем на пальцах:

insert into #Table_1 values ( 4092, '2009-06-01 07:56:44.000' , 84769.895000)
insert into #Table_1 values ( 4092, '2009-06-01 08:26:49.000' , 84771.819000)

Куды попадет этот расход?


Тогда может вы подскажите каким образом это решается без горя?
1 июн 09, 14:49    [7251621]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
beaver06
Member

Откуда:
Сообщений: 681
beaver06
aleks2
Ой... горе нам, ежели они так НА САМОМ ДЕЛЕ СЧИТАЮТ!!!

Иллюстрируем на пальцах:

insert into #Table_1 values ( 4092, '2009-06-01 07:56:44.000' , 84769.895000)
insert into #Table_1 values ( 4092, '2009-06-01 08:26:49.000' , 84771.819000)

Куды попадет этот расход?


Тогда может вы подскажите каким образом это решается без горя?


Ну как ?
1 июн 09, 14:51    [7251633]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
aleks2
Ой... горе нам, ежели они так НА САМОМ ДЕЛЕ СЧИТАЮТ!!!

Иллюстрируем на пальцах:

insert into #Table_1 values ( 4092, '2009-06-01 07:56:44.000' , 84769.895000)
insert into #Table_1 values ( 4092, '2009-06-01 08:26:49.000' , 84771.819000)

Куды попадет этот расход?

Намекаешь на очепятку? (SELECT 0, '0:00', '8:59:59') Ну дык бывает.
1 июн 09, 15:28    [7251862]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Модератор: Господа, имейте уважение к другим участникам форума и такие длинные скрипты цепляйте аттачем.
1 июн 09, 15:30    [7251874]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
beaver06
Member

Откуда:
Сообщений: 681
pkarklin
Модератор: Господа, имейте уважение к другим участникам форума и такие длинные скрипты цепляйте аттачем.


Виноват, исправлюсь...
1 июн 09, 15:31    [7251883]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
beaver06
Member

Откуда:
Сообщений: 681
Так что, никто не знает как это сделать оптимально?
1 июн 09, 15:43    [7251987]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
aleks2
Guest
beaver06
Так что, никто не знает как это сделать оптимально?


Я знаю, но сначала ответь на вопрос.
1 июн 09, 15:56    [7252122]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
aleks2
beaver06
Так что, никто не знает как это сделать оптимально?


Я знаю, но сначала ответь на вопрос.

в первую и вторую группу соответсвенно (21.00 до 08.00 и 08.00 до 11.00).
1 июн 09, 16:00    [7252166]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по тарифным зонам  [new]
beaver06
Member

Откуда:
Сообщений: 681
locky
aleks2
beaver06
Так что, никто не знает как это сделать оптимально?


Я знаю, но сначала ответь на вопрос.

в первую и вторую группу соответсвенно (21.00 до 08.00 и 08.00 до 11.00).


Совершенно точно!!!
1 июн 09, 16:15    [7252264]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить