BalinTomsk 10 фев 2016 в 19:36

Работа над ошибками аппаратуры на стороне SQL сервера и пользе нагрузочных тестов

4 мин

4.7K

Oracle*SQL*Microsoft SQL Server*

Recovery Mode

Комментарии 39

gleb_l 11 фев 2016 в 13:10

Мне кажется, что первый пример с coalesce можно заставить работать быстро, если правильно построить индексы. Кроме того, похоже, что подзапрос (select max(....)) у вас не фильтрован по dev_id, и соответственно берет просто максимальную дату считывания показаний.

В любом случае, курсор — это убить парадигму операций над множествами, чем собственно и сильны БД, и вместо нее применить парадигму последовательной обработки — то есть типичную для процедурных ЯП.

BalinTomsk 11 фев 2016 в 17:09

Индексы на что? на dev_id и дату есть, на 57 полей? дак их построение займет больше времени.

---Это убить парадигму операций над множествами

В этом и суть статью — не все решается парадигмами с нужной скоростью, иначе бы курсоры и while в язык не добавляли, у меня была задача, которая красиво ложилась на TSQL, но из-за низкой производительности была имплементирована на ESP.

-2

gleb_l 11 фев 2016 в 23:15

если NULL редки во всей серии — то можно сделать persisted computed column, сделав:

HasNull AS CASE WHEN COALESCE(col1, col2,… col57) IS NULL THEN 1 ELSE 0 END PERSISTED

дальше построить индекс dev_id, dev_counter_date, HasNull

если нужно (посмотреть execution plan), в индекс добавить icnluded-колонками поля, ради которых после выборки по индексу sql engine лезет по PK за самими данными, чтобы исключить эту операцию

gleb_l 11 фев 2016 в 23:41

Нет, вру — нужно задетектировать не все колонки NULL, а хотя бы одну. Тогда в вычислимой колонке нужно просто сложить логически их значения (можно и арифметически, но 57 колонок могут дать переполнение):

CASE WHEN col1 | col2 |… | col57 IS NULL THEN 1 ELSE 0 END

pihel 11 фев 2016 в 14:08

А что с Oracle запросом? Он остался в прежнем виде, с ним не было проблем?

xtender 11 фев 2016 в 18:24

Я бы для Oracle еще уменьшил сначала объем для апдейта, отсеяв значения которые обновлять не надо, да и сразу несколько полей можно:

merge 
into DEVICE_COUNTER t 
using ( 
        select t.rid, t.new_cnt_value1,t.new_cnt_value2
        from (
           select 
                ROWID as rid
              , dev_counter_duplex1
              , dev_counter_duplex2
              , last_value(nullif(dev_counter_duplex1,0) ignore nulls ) 
                    over (partition by dev_id order by dev_counter_date, dev_counter_id) as new_cnt_value1
              , last_value(nullif(dev_counter_duplex1,0) ignore nulls ) 
                    over (partition by dev_id order by dev_counter_date, dev_counter_id) as new_cnt_value2
           from DEVICE_COUNTER
        ) t
        where t.dev_counter_duplex1 is null or t.dev_counter_duplex1=0
           or t.dev_counter_duplex2 is null or t.dev_counter_duplex2=0
      ) v
on (t.rowid = v.rid) 
   when matched then 
      update set dev_counter_duplex1 = new_cnt_value1
                ,dev_counter_duplex2 = new_cnt_value2

К тому же сделал учитывая, что нужно не только NULLs, но и 0 проапдейтить, а то в решении автора топика 0 вообще не учитываются почему-то, несмотря на озвученное условие в начале.

BalinTomsk 11 фев 2016 в 22:40

Я рад что вы умеете включать голову, но вы не учли что колонок не 2 а 57.

Если решение на курсоре зависит только от количества строк, а решение не только от количества строк, но и от количество колонок.

Вот вам тестовые REPO:

table creation

CREATE TABLE device_counter
(
	[dev_counter_id] [int] IDENTITY(1,1) NOT NULL,
	[dev_id] [int] NOT NULL,
	[dev_counter_date] [datetime] NOT NULL,
	[dev_counter_total_color] [int] NULL,
	[dev_counter_total_mono] [int] NULL,
	[dev_counter_copy_black] [int] NULL,
	[dev_counter_copy_color_full] [int] NULL,
	[dev_counter_copy_color_mono] [int] NULL,
	[dev_counter_copy_color_twin] [int] NULL,
	[dev_counter_printer_black] [int] NULL,
	[dev_counter_printer_color_full] [int] NULL,
	[dev_counter_printer_color_mono] [int] NULL,
	[dev_counter_printer_color_twin] [int] NULL,
	[dev_counter_printer_color_levl] [int] NULL,
	[dev_counter_fax_black] [int] NULL,
	[dev_counter_fax_color_full] [int] NULL,
	[dev_counter_fax_color_mono] [int] NULL,
	[dev_counter_fax_send] [int] NULL,
	[dev_counter_gpc] [int] NULL,
	[dev_counter_gpc_printer] [int] NULL,
	[dev_counter_gpc_color_full] [int] NULL,
	[dev_counter_a3_dlt] [int] NULL,
	[dev_counter_duplex] [int] NULL,
	[dev_counter_send_color] [int] NULL,
	[dev_counter_send_mono] [int] NULL,
	[dev_counter_fax_color_twin] [int] NULL,
	[dev_counter_total] [int] NULL,
	[dev_counter_coverage_color] [int] NULL,
	[dev_counter_coverage_black] [int] NULL,
	[dev_counter_cov_color_prt_page] [int] NULL,
	[dev_counter_cov_black_prt_page] [int] NULL,
	[dev_counter_a2] [int] NULL,
	[dev_counter_scanner_send_color] [int] NULL,
	[dev_counter_scanner_send_black] [int] NULL,
	[dev_counter_fcolor_sheet_dom] [int] NULL,
	[dev_counter_mcolor_sheet_dom] [int] NULL,
	[dev_counter_fcolor_copy_charge] [int] NULL,
	[dev_counter_black_copy_charge] [int] NULL,
	[dev_counter_fcolor_prt_charge] [int] NULL,
	[dev_counter_black_print_charge] [int] NULL,
	[dev_counter_fcolor_tot_charge] [int] NULL,
	[dev_counter_black_total_charge] [int] NULL,
	[dev_counter_fcolor_economy_prt] [int] NULL,
	[dev_counter_black_economy_prt] [int] NULL,
	[dev_counter_fcolor_sheets_prt] [int] NULL,
	[dev_counter_mcolor_sheets_prt] [int] NULL,
	[dev_counter_fcolor_sheets_a3u] [int] NULL,
	[dev_counter_mcolor_sheets_a3d] [int] NULL,
	[dev_counter_color_coverage1] [int] NULL,
	[dev_counter_color_coverage2] [int] NULL,
	[dev_counter_color_coverage3] [int] NULL,
	[dev_counter_state_operation] [int] NULL,
	[dev_counter_state_waiting] [int] NULL,
	[dev_counter_state_preheat] [int] NULL,
	[dev_counter_state_sleep] [int] NULL,
	[dev_counter_state_offmode] [int] NULL,
	[dev_counter_state_down_sc] [int] NULL,
	[dev_counter_state_down_pj] [int] NULL,
	[dev_counter_state_down_sj] [int] NULL,
	[dev_counter_state_down_sup_pm] [int] NULL,
    CONSTRAINT PK_device_counter PRIMARY KEY CLUSTERED (dev_counter_id, dev_counter_date)
);
GO

Filling

CREATE VIEW dbo.vw_rnd
AS
	SELECT CAST(round(rand() * 10 + 1, 0) AS INT) AS rndVal
GO

DECLARE
@val1 int = 0,
@val2 int = 0,
@val3 int = 0,
@val4 int = 0,
@val5 int = 0,
@val6 int = 0,
@val7 int = 0,
@val8 int = 0,
@val9 int = 0,
@val10 int = 0,
@val11 int = 0,
@val12 int = 0,
@val13 int = 0,
@val14 int = 0,
@val15 int = 0,
@val16 int = 0,
@val17 int = 0,
@val18 int = 0,
@val19 int = 0,
@val20 int = 0,
@val21 int = 0,
@val22 int = 0,
@val23 int = 0,
@val24 int = 0,
@val25 int = 0,
@val26 int = 0,
@val27 int = 0,
@val28 int = 0,
@val29 int = 0,
@val30 int = 0,
@val31 int = 0,
@val32 int = 0,
@val33 int = 0,
@val34 int = 0,
@val35 int = 0,
@val36 int = 0,
@val37 int = 0,
@val38 int = 0,
@val39 int = 0,
@val40 int = 0,
@val41 int = 0,
@val42 int = 0,
@val43 int = 0,
@val44 int = 0,
@val45 int = 0,
@val46 int = 0,
@val47 int = 0,
@val48 int = 0,
@val49 int = 0,
@val50 int = 0,
@val51 int = 0,
@val52 int = 0,
@val53 int = 0,
@val54 int = 0,
@val55 int = 0,
@val56 int = 0,
@val57 int = 0;
DECLARE @dev_id int = 0, @day int = 0;	
BEGIN
    while @dev_id < 8000
    begin
       select @dev_id = @dev_id + 1, @day = 0;
        while @day < 365
        begin
            set @day = @day + 1
            select @val1 = CASE WHEN rndVal % 7 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val2 = CASE WHEN rndVal % 2 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val3 = CASE WHEN rndVal % 4 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val4 = CASE WHEN rndVal % 3 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val5 = CASE WHEN rndVal % 6 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val6 = CASE WHEN rndVal % 7 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val7 = CASE WHEN rndVal % 8 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val8 = CASE WHEN rndVal % 2 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val9 = CASE WHEN rndVal % 3 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val10 = CASE WHEN rndVal % 3 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val11 = CASE WHEN rndVal % 5 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val12 = CASE WHEN rndVal % 6 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val13 = CASE WHEN rndVal % 7 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val14 = CASE WHEN rndVal % 8 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val15 = CASE WHEN rndVal % 9 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val16 = CASE WHEN rndVal % 3 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val17 = CASE WHEN rndVal % 4 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val18 = CASE WHEN rndVal % 5 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val19 = CASE WHEN rndVal % 7 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val20 = CASE WHEN rndVal % 5 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val21 = CASE WHEN rndVal % 7 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val22 = CASE WHEN rndVal % 9 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val23 = CASE WHEN rndVal % 7 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val24 = CASE WHEN rndVal % 6 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val25 = CASE WHEN rndVal % 5 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val26 = CASE WHEN rndVal % 4 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val27 = CASE WHEN rndVal % 8 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val28 = CASE WHEN rndVal % 9 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val29 = CASE WHEN rndVal % 5 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val30 = CASE WHEN rndVal % 3 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val31 = CASE WHEN rndVal % 4 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val32 = CASE WHEN rndVal % 5 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val33 = CASE WHEN rndVal % 3 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val34 = CASE WHEN rndVal % 3 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val35 = CASE WHEN rndVal % 3 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val36 = CASE WHEN rndVal % 3 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val37 = CASE WHEN rndVal % 3 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val38 = CASE WHEN rndVal % 3 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val39 = CASE WHEN rndVal % 7 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val40 = CASE WHEN rndVal % 3 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val41 = CASE WHEN rndVal % 8 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val42 = CASE WHEN rndVal % 3 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val43 = CASE WHEN rndVal % 3 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val44 = CASE WHEN rndVal % 3 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val45 = CASE WHEN rndVal % 9 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val46 = CASE WHEN rndVal % 3 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val47 = CASE WHEN rndVal % 3 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val48 = CASE WHEN rndVal % 3 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val49 = CASE WHEN rndVal % 5 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val50 = CASE WHEN rndVal % 4 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val51 = CASE WHEN rndVal % 6 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val52 = CASE WHEN rndVal % 7 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val53 = CASE WHEN rndVal % 8 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val54 = CASE WHEN rndVal % 5 = 0 THEN 0 ELSE rndVal END from vw_rnd;
            select @val55 = CASE WHEN rndVal % 9 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val56 = CASE WHEN rndVal % 6 = 0 THEN NULL ELSE rndVal END from vw_rnd;
            select @val57 = CASE WHEN rndVal % 9 = 0 THEN 0 ELSE rndVal END from vw_rnd;
             
              INSERT INTO device_counter( dev_id, dev_counter_date, dev_counter_total_color, dev_counter_total_mono, dev_counter_copy_black, dev_counter_copy_color_full, dev_counter_copy_color_mono, dev_counter_copy_color_twin, dev_counter_printer_black, dev_counter_printer_color_full, dev_counter_printer_color_mono, dev_counter_printer_color_twin, dev_counter_printer_color_levl, dev_counter_fax_black, dev_counter_fax_color_full, dev_counter_fax_color_mono, dev_counter_fax_send, dev_counter_gpc, dev_counter_gpc_printer, dev_counter_gpc_color_full, dev_counter_a3_dlt, dev_counter_duplex, dev_counter_send_color, dev_counter_send_mono, dev_counter_fax_color_twin, dev_counter_total, dev_counter_coverage_color, dev_counter_coverage_black, dev_counter_cov_color_prt_page, dev_counter_cov_black_prt_page, dev_counter_a2, dev_counter_scanner_send_color, dev_counter_scanner_send_black, dev_counter_fcolor_sheet_dom, dev_counter_mcolor_sheet_dom, dev_counter_fcolor_copy_charge, dev_counter_black_copy_charge, dev_counter_fcolor_prt_charge, dev_counter_black_print_charge, dev_counter_fcolor_tot_charge, dev_counter_black_total_charge, dev_counter_fcolor_economy_prt, dev_counter_black_economy_prt, dev_counter_fcolor_sheets_prt, dev_counter_mcolor_sheets_prt, dev_counter_fcolor_sheets_a3u, dev_counter_mcolor_sheets_a3d, dev_counter_color_coverage1, dev_counter_color_coverage2, dev_counter_color_coverage3, dev_counter_state_operation, dev_counter_state_waiting, dev_counter_state_preheat, dev_counter_state_sleep, dev_counter_state_offmode, dev_counter_state_down_sc, dev_counter_state_down_pj, dev_counter_state_down_sj, dev_counter_state_down_sup_pm ) 
                                    VALUES (@dev_id, DATEADD( hour, @dev_id, '2000-01-01'), @val1, @val2, @val3, @val4, @val5, @val6, @val7, @val8, @val9, @val10, @val11, @val12, @val13, @val14, @val15, @val16, @val17, @val18, @val19, @val20, @val21, @val22, @val23, @val24, @val25, @val26, @val27, @val28, @val29, @val30, @val31, @val32, @val33, @val34, @val35, @val36, @val37, @val38, @val39, @val40, @val41, @val42, @val43, @val44, @val45, @val46, @val47, @val48, @val49, @val50, @val51, @val52, @val53, @val54, @val55, @val56, @val57 );
        end
    end
END;

SQL way

update curr set curr.dev_counter_total_color = coalesce( curr.dev_counter_total_color, prev.dev_counter_total_color ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_total_color is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_total_color is not null )
GO
update curr set curr.dev_counter_total_mono = coalesce( curr.dev_counter_total_mono, prev.dev_counter_total_mono ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_total_mono is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_total_mono is not null )
GO
update curr set curr.dev_counter_copy_black = coalesce( curr.dev_counter_copy_black, prev.dev_counter_copy_black ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_copy_black is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_copy_black is not null )
GO
update curr set curr.dev_counter_copy_color_full = coalesce( curr.dev_counter_copy_color_full, prev.dev_counter_copy_color_full ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_copy_color_full is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_copy_color_full is not null )
GO
update curr set curr.dev_counter_copy_color_mono = coalesce( curr.dev_counter_copy_color_mono, prev.dev_counter_copy_color_mono ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_copy_color_mono is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_copy_color_mono is not null )
GO
update curr set curr.dev_counter_copy_color_twin = coalesce( curr.dev_counter_copy_color_twin, prev.dev_counter_copy_color_twin ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_copy_color_twin is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_copy_color_twin is not null )
GO
update curr set curr.dev_counter_printer_black = coalesce( curr.dev_counter_printer_black, prev.dev_counter_printer_black ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_printer_black is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_printer_black is not null )
GO
update curr set curr.dev_counter_printer_color_full = coalesce( curr.dev_counter_printer_color_full, prev.dev_counter_printer_color_full ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_printer_color_full is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_printer_color_full is not null )
GO
update curr set curr.dev_counter_printer_color_mono = coalesce( curr.dev_counter_printer_color_mono, prev.dev_counter_printer_color_mono ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_printer_color_mono is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_printer_color_mono is not null )
GO
update curr set curr.dev_counter_printer_color_twin = coalesce( curr.dev_counter_printer_color_twin, prev.dev_counter_printer_color_twin ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_printer_color_twin is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_printer_color_twin is not null )
GO
update curr set curr.dev_counter_printer_color_levl = coalesce( curr.dev_counter_printer_color_levl, prev.dev_counter_printer_color_levl ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_printer_color_levl is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_printer_color_levl is not null )
GO
update curr set curr.dev_counter_fax_black = coalesce( curr.dev_counter_fax_black, prev.dev_counter_fax_black ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_fax_black is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_fax_black is not null )
GO
update curr set curr.dev_counter_fax_color_full = coalesce( curr.dev_counter_fax_color_full, prev.dev_counter_fax_color_full ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_fax_color_full is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_fax_color_full is not null )
GO
update curr set curr.dev_counter_fax_color_mono = coalesce( curr.dev_counter_fax_color_mono, prev.dev_counter_fax_color_mono ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_fax_color_mono is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_fax_color_mono is not null )
GO
update curr set curr.dev_counter_fax_send = coalesce( curr.dev_counter_fax_send, prev.dev_counter_fax_send ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_fax_send is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_fax_send is not null )
GO
update curr set curr.dev_counter_gpc = coalesce( curr.dev_counter_gpc, prev.dev_counter_gpc ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_gpc is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_gpc is not null )
GO
update curr set curr.dev_counter_gpc_printer = coalesce( curr.dev_counter_gpc_printer, prev.dev_counter_gpc_printer ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_gpc_printer is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_gpc_printer is not null )
GO
update curr set curr.dev_counter_gpc_color_full = coalesce( curr.dev_counter_gpc_color_full, prev.dev_counter_gpc_color_full ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_gpc_color_full is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_gpc_color_full is not null )
GO
update curr set curr.dev_counter_a3_dlt = coalesce( curr.dev_counter_a3_dlt, prev.dev_counter_a3_dlt ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_a3_dlt is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_a3_dlt is not null )
GO
update curr set curr.dev_counter_duplex = coalesce( curr.dev_counter_duplex, prev.dev_counter_duplex ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_duplex is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_duplex is not null )
GO
update curr set curr.dev_counter_send_color = coalesce( curr.dev_counter_send_color, prev.dev_counter_send_color ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_send_color is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_send_color is not null )
GO
update curr set curr.dev_counter_send_mono = coalesce( curr.dev_counter_send_mono, prev.dev_counter_send_mono ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_send_mono is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_send_mono is not null )
GO
update curr set curr.dev_counter_fax_color_twin = coalesce( curr.dev_counter_fax_color_twin, prev.dev_counter_fax_color_twin ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_fax_color_twin is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_fax_color_twin is not null )
GO
update curr set curr.dev_counter_total = coalesce( curr.dev_counter_total, prev.dev_counter_total ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_total is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_total is not null )
GO
update curr set curr.dev_counter_coverage_color = coalesce( curr.dev_counter_coverage_color, prev.dev_counter_coverage_color ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_coverage_color is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_coverage_color is not null )
GO
update curr set curr.dev_counter_coverage_black = coalesce( curr.dev_counter_coverage_black, prev.dev_counter_coverage_black ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_coverage_black is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_coverage_black is not null )
GO
update curr set curr.dev_counter_cov_color_prt_page = coalesce( curr.dev_counter_cov_color_prt_page, prev.dev_counter_cov_color_prt_page ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_cov_color_prt_page is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_cov_color_prt_page is not null )
GO
update curr set curr.dev_counter_cov_black_prt_page = coalesce( curr.dev_counter_cov_black_prt_page, prev.dev_counter_cov_black_prt_page ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_cov_black_prt_page is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_cov_black_prt_page is not null )
GO
update curr set curr.dev_counter_a2 = coalesce( curr.dev_counter_a2, prev.dev_counter_a2 ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_a2 is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_a2 is not null )
GO
update curr set curr.dev_counter_scanner_send_color = coalesce( curr.dev_counter_scanner_send_color, prev.dev_counter_scanner_send_color ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_scanner_send_color is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_scanner_send_color is not null )
GO
update curr set curr.dev_counter_scanner_send_black = coalesce( curr.dev_counter_scanner_send_black, prev.dev_counter_scanner_send_black ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_scanner_send_black is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_scanner_send_black is not null )
GO
update curr set curr.dev_counter_fcolor_sheet_dom = coalesce( curr.dev_counter_fcolor_sheet_dom, prev.dev_counter_fcolor_sheet_dom ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_fcolor_sheet_dom is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_fcolor_sheet_dom is not null )
GO
update curr set curr.dev_counter_mcolor_sheet_dom = coalesce( curr.dev_counter_mcolor_sheet_dom, prev.dev_counter_mcolor_sheet_dom ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_mcolor_sheet_dom is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_mcolor_sheet_dom is not null )
GO
update curr set curr.dev_counter_fcolor_copy_charge = coalesce( curr.dev_counter_fcolor_copy_charge, prev.dev_counter_fcolor_copy_charge ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_fcolor_copy_charge is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_fcolor_copy_charge is not null )
GO
update curr set curr.dev_counter_black_copy_charge = coalesce( curr.dev_counter_black_copy_charge, prev.dev_counter_black_copy_charge ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_black_copy_charge is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_black_copy_charge is not null )
GO
update curr set curr.dev_counter_fcolor_prt_charge = coalesce( curr.dev_counter_fcolor_prt_charge, prev.dev_counter_fcolor_prt_charge ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_fcolor_prt_charge is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_fcolor_prt_charge is not null )
GO
update curr set curr.dev_counter_black_print_charge = coalesce( curr.dev_counter_black_print_charge, prev.dev_counter_black_print_charge ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_black_print_charge is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_black_print_charge is not null )
GO
update curr set curr.dev_counter_fcolor_tot_charge = coalesce( curr.dev_counter_fcolor_tot_charge, prev.dev_counter_fcolor_tot_charge ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_fcolor_tot_charge is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_fcolor_tot_charge is not null )
GO
update curr set curr.dev_counter_black_total_charge = coalesce( curr.dev_counter_black_total_charge, prev.dev_counter_black_total_charge ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_black_total_charge is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_black_total_charge is not null )
GO
update curr set curr.dev_counter_fcolor_economy_prt = coalesce( curr.dev_counter_fcolor_economy_prt, prev.dev_counter_fcolor_economy_prt ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_fcolor_economy_prt is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_fcolor_economy_prt is not null )
GO
update curr set curr.dev_counter_black_economy_prt = coalesce( curr.dev_counter_black_economy_prt, prev.dev_counter_black_economy_prt ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_black_economy_prt is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_black_economy_prt is not null )
GO
update curr set curr.dev_counter_fcolor_sheets_prt = coalesce( curr.dev_counter_fcolor_sheets_prt, prev.dev_counter_fcolor_sheets_prt ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_fcolor_sheets_prt is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_fcolor_sheets_prt is not null )
GO
update curr set curr.dev_counter_mcolor_sheets_prt = coalesce( curr.dev_counter_mcolor_sheets_prt, prev.dev_counter_mcolor_sheets_prt ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_mcolor_sheets_prt is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_mcolor_sheets_prt is not null )
GO
update curr set curr.dev_counter_fcolor_sheets_a3u = coalesce( curr.dev_counter_fcolor_sheets_a3u, prev.dev_counter_fcolor_sheets_a3u ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_fcolor_sheets_a3u is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_fcolor_sheets_a3u is not null )
GO
update curr set curr.dev_counter_mcolor_sheets_a3d = coalesce( curr.dev_counter_mcolor_sheets_a3d, prev.dev_counter_mcolor_sheets_a3d ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_mcolor_sheets_a3d is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_mcolor_sheets_a3d is not null )
GO
update curr set curr.dev_counter_color_coverage1 = coalesce( curr.dev_counter_color_coverage1, prev.dev_counter_color_coverage1 ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_color_coverage1 is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_color_coverage1 is not null )
GO
update curr set curr.dev_counter_color_coverage2 = coalesce( curr.dev_counter_color_coverage2, prev.dev_counter_color_coverage2 ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_color_coverage2 is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_color_coverage2 is not null )
GO
update curr set curr.dev_counter_color_coverage3 = coalesce( curr.dev_counter_color_coverage3, prev.dev_counter_color_coverage3 ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_color_coverage3 is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_color_coverage3 is not null )
GO
update curr set curr.dev_counter_state_operation = coalesce( curr.dev_counter_state_operation, prev.dev_counter_state_operation ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_state_operation is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_state_operation is not null )
GO
update curr set curr.dev_counter_state_waiting = coalesce( curr.dev_counter_state_waiting, prev.dev_counter_state_waiting ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_state_waiting is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_state_waiting is not null )
GO
update curr set curr.dev_counter_state_preheat = coalesce( curr.dev_counter_state_preheat, prev.dev_counter_state_preheat ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_state_preheat is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_state_preheat is not null )
GO
update curr set curr.dev_counter_state_sleep = coalesce( curr.dev_counter_state_sleep, prev.dev_counter_state_sleep ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_state_sleep is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_state_sleep is not null )
GO
update curr set curr.dev_counter_state_offmode = coalesce( curr.dev_counter_state_offmode, prev.dev_counter_state_offmode ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_state_offmode is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_state_offmode is not null )
GO
update curr set curr.dev_counter_state_down_sc = coalesce( curr.dev_counter_state_down_sc, prev.dev_counter_state_down_sc ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_state_down_sc is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_state_down_sc is not null )
GO
update curr set curr.dev_counter_state_down_pj = coalesce( curr.dev_counter_state_down_pj, prev.dev_counter_state_down_pj ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_state_down_pj is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_state_down_pj is not null )
GO
update curr set curr.dev_counter_state_down_sj = coalesce( curr.dev_counter_state_down_sj, prev.dev_counter_state_down_sj ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_state_down_sj is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_state_down_sj is not null )
GO
update curr set curr.dev_counter_state_down_sup_pm = coalesce( curr.dev_counter_state_down_sup_pm, prev.dev_counter_state_down_sup_pm ) from device_counter curr left join
   device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_state_down_sup_pm is null and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub
   where sub.dev_id = curr.dev_id and sub.dev_counter_date < curr.dev_counter_date and  prev.dev_counter_state_down_sup_pm is not null )
GO

В другом посте Cursor way — тут ограничение на размер поста. На машине с SSD выполняется 2 минуты.

BalinTomsk 11 фев 2016 в 22:46

Cursor way

BEGIN
DECLARE @updated int = 0;
DECLARE @cur_dev_id int = NULL, @cur_id int = NULL
DECLARE @cur_counter_total_color int = NULL,
@cur_counter_total_mono int = NULL,
@cur_counter_copy_black int = NULL,
@cur_counter_copy_color_full int = NULL,
@cur_counter_copy_color_mono int = NULL,
@cur_counter_copy_color_twin int = NULL,
@cur_counter_printer_black int = NULL,
@cur_counter_printer_color_full int = NULL,
@cur_counter_printer_color_mono int = NULL,
@cur_counter_printer_color_twin int = NULL,
@cur_counter_printer_color_levl int = NULL,
@cur_counter_fax_black int = NULL,
@cur_counter_fax_color_full int = NULL,
@cur_counter_fax_color_mono int = NULL,
@cur_counter_fax_send int = NULL,
@cur_counter_gpc int = NULL,
@cur_counter_gpc_printer int = NULL,
@cur_counter_gpc_color_full int = NULL,
@cur_counter_a3_dlt int = NULL,
@cur_counter_duplex int = NULL,
@cur_counter_send_color int = NULL,
@cur_counter_send_mono int = NULL,
@cur_counter_fax_color_twin int = NULL,
@cur_counter_total int = NULL,
@cur_counter_coverage_color int = NULL,
@cur_counter_coverage_black int = NULL,
@cur_counter_cov_color_prt_page int = NULL,
@cur_counter_cov_black_prt_page int = NULL,
@cur_counter_a2 int = NULL,
@cur_counter_scanner_send_color int = NULL,
@cur_counter_scanner_send_black int = NULL,
@cur_counter_fcolor_sheet_dom int = NULL,
@cur_counter_mcolor_sheet_dom int = NULL,
@cur_counter_fcolor_copy_charge int = NULL,
@cur_counter_black_copy_charge int = NULL,
@cur_counter_fcolor_prt_charge int = NULL,
@cur_counter_black_print_charge int = NULL,
@cur_counter_fcolor_tot_charge int = NULL,
@cur_counter_black_total_charge int = NULL,
@cur_counter_fcolor_economy_prt int = NULL,
@cur_counter_black_economy_prt int = NULL,
@cur_counter_fcolor_sheets_prt int = NULL,
@cur_counter_mcolor_sheets_prt int = NULL,
@cur_counter_fcolor_sheets_a3u int = NULL,
@cur_counter_mcolor_sheets_a3d int = NULL,
@cur_counter_color_coverage1 int = NULL,
@cur_counter_color_coverage2 int = NULL,
@cur_counter_color_coverage3 int = NULL,
@cur_counter_state_operation int = NULL,
@cur_counter_state_waiting int = NULL,
@cur_counter_state_preheat int = NULL,
@cur_counter_state_sleep int = NULL,
@cur_counter_state_offmode int = NULL,
@cur_counter_state_down_sc int = NULL,
@cur_counter_state_down_pj int = NULL,
@cur_counter_state_down_sj int = NULL,
@cur_counter_state_down_sup_pm int = NULL

DECLARE @next_dev_id int = NULL, @next_id int = NULL

DECLARE @next_counter_total_color int = NULL,
@next_counter_total_mono int = NULL,
@next_counter_copy_black int = NULL,
@next_counter_copy_color_full int = NULL,
@next_counter_copy_color_mono int = NULL,
@next_counter_copy_color_twin int = NULL,
@next_counter_printer_black int = NULL,
@next_counter_printer_color_full int = NULL,
@next_counter_printer_color_mono int = NULL,
@next_counter_printer_color_twin int = NULL,
@next_counter_printer_color_levl int = NULL,
@next_counter_fax_black int = NULL,
@next_counter_fax_color_full int = NULL,
@next_counter_fax_color_mono int = NULL,
@next_counter_fax_send int = NULL,
@next_counter_gpc int = NULL,
@next_counter_gpc_printer int = NULL,
@next_counter_gpc_color_full int = NULL,
@next_counter_a3_dlt int = NULL,
@next_counter_duplex int = NULL,
@next_counter_send_color int = NULL,
@next_counter_send_mono int = NULL,
@next_counter_fax_color_twin int = NULL,
@next_counter_total int = NULL,
@next_counter_coverage_color int = NULL,
@next_counter_coverage_black int = NULL,
@next_counter_cov_color_prt_page int = NULL,
@next_counter_cov_black_prt_page int = NULL,
@next_counter_a2 int = NULL,
@next_counter_scanner_send_color int = NULL,
@next_counter_scanner_send_black int = NULL,
@next_counter_fcolor_sheet_dom int = NULL,
@next_counter_mcolor_sheet_dom int = NULL,
@next_counter_fcolor_copy_charge int = NULL,
@next_counter_black_copy_charge int = NULL,
@next_counter_fcolor_prt_charge int = NULL,
@next_counter_black_print_charge int = NULL,
@next_counter_fcolor_tot_charge int = NULL,
@next_counter_black_total_charge int = NULL,
@next_counter_fcolor_economy_prt int = NULL,
@next_counter_black_economy_prt int = NULL,
@next_counter_fcolor_sheets_prt int = NULL,
@next_counter_mcolor_sheets_prt int = NULL,
@next_counter_fcolor_sheets_a3u int = NULL,
@next_counter_mcolor_sheets_a3d int = NULL,
@next_counter_color_coverage1 int = NULL,
@next_counter_color_coverage2 int = NULL,
@next_counter_color_coverage3 int = NULL,
@next_counter_state_operation int = NULL,
@next_counter_state_waiting int = NULL,
@next_counter_state_preheat int = NULL,
@next_counter_state_sleep int = NULL,
@next_counter_state_offmode int = NULL,
@next_counter_state_down_sc int = NULL,
@next_counter_state_down_pj int = NULL,
@next_counter_state_down_sj int = NULL,
@next_counter_state_down_sup_pm int = NULL

DECLARE UPDCURSOR CURSOR for select dev_id, dev_counter_id, 
dev_counter_total_color,
dev_counter_total_mono,
dev_counter_copy_black,
dev_counter_copy_color_full,
dev_counter_copy_color_mono,
dev_counter_copy_color_twin,
dev_counter_printer_black,
dev_counter_printer_color_full,
dev_counter_printer_color_mono,
dev_counter_printer_color_twin,
dev_counter_printer_color_levl,
dev_counter_fax_black,
dev_counter_fax_color_full,
dev_counter_fax_color_mono,
dev_counter_fax_send,
dev_counter_gpc,
dev_counter_gpc_printer,
dev_counter_gpc_color_full,
dev_counter_a3_dlt,
dev_counter_duplex,
dev_counter_send_color,
dev_counter_send_mono,
dev_counter_fax_color_twin,
dev_counter_total,
dev_counter_coverage_color,
dev_counter_coverage_black,
dev_counter_cov_color_prt_page,
dev_counter_cov_black_prt_page,
dev_counter_a2,
dev_counter_scanner_send_color,
dev_counter_scanner_send_black,
dev_counter_fcolor_sheet_dom,
dev_counter_mcolor_sheet_dom,
dev_counter_fcolor_copy_charge,
dev_counter_black_copy_charge,
dev_counter_fcolor_prt_charge,
dev_counter_black_print_charge,
dev_counter_fcolor_tot_charge,
dev_counter_black_total_charge,
dev_counter_fcolor_economy_prt,
dev_counter_black_economy_prt,
dev_counter_fcolor_sheets_prt,
dev_counter_mcolor_sheets_prt,
dev_counter_fcolor_sheets_a3u,
dev_counter_mcolor_sheets_a3d,
dev_counter_color_coverage1,
dev_counter_color_coverage2,
dev_counter_color_coverage3,
dev_counter_state_operation,
dev_counter_state_waiting,
dev_counter_state_preheat,
dev_counter_state_sleep,
dev_counter_state_offmode,
dev_counter_state_down_sc,
dev_counter_state_down_pj,
dev_counter_state_down_sj,
dev_counter_state_down_sup_pm
    from device_counter d  order by dev_id, dev_counter_date
    
OPEN UPDCURSOR
FETCH NEXT FROM UPDCURSOR into @cur_dev_id, @cur_id, 
@cur_counter_total_color,
@cur_counter_total_mono,
@cur_counter_copy_black,
@cur_counter_copy_color_full,
@cur_counter_copy_color_mono,
@cur_counter_copy_color_twin,
@cur_counter_printer_black,
@cur_counter_printer_color_full,
@cur_counter_printer_color_mono,
@cur_counter_printer_color_twin,
@cur_counter_printer_color_levl,
@cur_counter_fax_black,
@cur_counter_fax_color_full,
@cur_counter_fax_color_mono,
@cur_counter_fax_send,
@cur_counter_gpc,
@cur_counter_gpc_printer,
@cur_counter_gpc_color_full,
@cur_counter_a3_dlt,
@cur_counter_duplex,
@cur_counter_send_color,
@cur_counter_send_mono,
@cur_counter_fax_color_twin,
@cur_counter_total,
@cur_counter_coverage_color,
@cur_counter_coverage_black,
@cur_counter_cov_color_prt_page,
@cur_counter_cov_black_prt_page,
@cur_counter_a2,
@cur_counter_scanner_send_color,
@cur_counter_scanner_send_black,
@cur_counter_fcolor_sheet_dom,
@cur_counter_mcolor_sheet_dom,
@cur_counter_fcolor_copy_charge,
@cur_counter_black_copy_charge,
@cur_counter_fcolor_prt_charge,
@cur_counter_black_print_charge,
@cur_counter_fcolor_tot_charge,
@cur_counter_black_total_charge,
@cur_counter_fcolor_economy_prt,
@cur_counter_black_economy_prt,
@cur_counter_fcolor_sheets_prt,
@cur_counter_mcolor_sheets_prt,
@cur_counter_fcolor_sheets_a3u,
@cur_counter_mcolor_sheets_a3d,
@cur_counter_color_coverage1,
@cur_counter_color_coverage2,
@cur_counter_color_coverage3,
@cur_counter_state_operation,
@cur_counter_state_waiting,
@cur_counter_state_preheat,
@cur_counter_state_sleep,
@cur_counter_state_offmode,
@cur_counter_state_down_sc,
@cur_counter_state_down_pj,
@cur_counter_state_down_sj,
@cur_counter_state_down_sup_pm

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM UPDCURSOR INTO @next_dev_id, @next_id
, @next_counter_total_color
, @next_counter_total_mono
, @next_counter_copy_black
, @next_counter_copy_color_full
, @next_counter_copy_color_mono
, @next_counter_copy_color_twin
, @next_counter_printer_black
, @next_counter_printer_color_full
, @next_counter_printer_color_mono
, @next_counter_printer_color_twin
, @next_counter_printer_color_levl
, @next_counter_fax_black
, @next_counter_fax_color_full
, @next_counter_fax_color_mono
, @next_counter_fax_send
, @next_counter_gpc
, @next_counter_gpc_printer
, @next_counter_gpc_color_full
, @next_counter_a3_dlt
, @next_counter_duplex
, @next_counter_send_color
, @next_counter_send_mono
, @next_counter_fax_color_twin
, @next_counter_total
, @next_counter_coverage_color
, @next_counter_coverage_black
, @next_counter_cov_color_prt_page
, @next_counter_cov_black_prt_page
, @next_counter_a2
, @next_counter_scanner_send_color
, @next_counter_scanner_send_black
, @next_counter_fcolor_sheet_dom
, @next_counter_mcolor_sheet_dom
, @next_counter_fcolor_copy_charge
, @next_counter_black_copy_charge
, @next_counter_fcolor_prt_charge
, @next_counter_black_print_charge
, @next_counter_fcolor_tot_charge
, @next_counter_black_total_charge
, @next_counter_fcolor_economy_prt
, @next_counter_black_economy_prt
, @next_counter_fcolor_sheets_prt
, @next_counter_mcolor_sheets_prt
, @next_counter_fcolor_sheets_a3u
, @next_counter_mcolor_sheets_a3d
, @next_counter_color_coverage1
, @next_counter_color_coverage2
, @next_counter_color_coverage3
, @next_counter_state_operation
, @next_counter_state_waiting
, @next_counter_state_preheat
, @next_counter_state_sleep
, @next_counter_state_offmode
, @next_counter_state_down_sc
, @next_counter_state_down_pj
, @next_counter_state_down_sj
, @next_counter_state_down_sup_pm

IF @@FETCH_STATUS = 0 AND @cur_dev_id = @next_dev_id AND (
	((@next_counter_total_color IS NULL AND @cur_counter_total_color IS NOT NULL) OR (@next_counter_total_color = 0 AND (@cur_counter_total_color > 0 OR @cur_counter_total_color Is NULL ))) OR
	((@next_counter_total_mono IS NULL AND @cur_counter_total_mono IS NOT NULL) OR (@next_counter_total_mono = 0 AND (@cur_counter_total_mono > 0 OR @cur_counter_total_mono Is NULL ))) OR
	((@next_counter_copy_black IS NULL AND @cur_counter_copy_black IS NOT NULL) OR (@next_counter_copy_black = 0 AND (@cur_counter_copy_black > 0 OR @cur_counter_copy_black Is NULL ))) OR
	((@next_counter_copy_color_full IS NULL AND @cur_counter_copy_color_full IS NOT NULL) OR (@next_counter_copy_color_full = 0 AND (@cur_counter_copy_color_full > 0 OR @cur_counter_copy_color_full Is NULL ))) OR
	((@next_counter_copy_color_mono IS NULL AND @cur_counter_copy_color_mono IS NOT NULL) OR (@next_counter_copy_color_mono = 0 AND (@cur_counter_copy_color_mono > 0 OR @cur_counter_copy_color_mono Is NULL ))) OR
	((@next_counter_copy_color_twin IS NULL AND @cur_counter_copy_color_twin IS NOT NULL) OR (@next_counter_copy_color_twin = 0 AND (@cur_counter_copy_color_twin > 0 OR @cur_counter_copy_color_twin Is NULL ))) OR
	((@next_counter_printer_black IS NULL AND @cur_counter_printer_black IS NOT NULL) OR (@next_counter_printer_black = 0 AND (@cur_counter_printer_black > 0 OR @cur_counter_printer_black Is NULL ))) OR
	((@next_counter_printer_color_full IS NULL AND @cur_counter_printer_color_full IS NOT NULL) OR (@next_counter_printer_color_full = 0 AND (@cur_counter_printer_color_full > 0 OR @cur_counter_printer_color_full Is NULL ))) OR
	((@next_counter_printer_color_mono IS NULL AND @cur_counter_printer_color_mono IS NOT NULL) OR (@next_counter_printer_color_mono = 0 AND (@cur_counter_printer_color_mono > 0 OR @cur_counter_printer_color_mono Is NULL ))) OR
	((@next_counter_printer_color_twin IS NULL AND @cur_counter_printer_color_twin IS NOT NULL) OR (@next_counter_printer_color_twin = 0 AND (@cur_counter_printer_color_twin > 0 OR @cur_counter_printer_color_twin Is NULL ))) OR
	((@next_counter_printer_color_levl IS NULL AND @cur_counter_printer_color_levl IS NOT NULL) OR (@next_counter_printer_color_levl = 0 AND (@cur_counter_printer_color_levl > 0 OR @cur_counter_printer_color_levl Is NULL ))) OR
	((@next_counter_fax_black IS NULL AND @cur_counter_fax_black IS NOT NULL) OR (@next_counter_fax_black = 0 AND (@cur_counter_fax_black > 0 OR @cur_counter_fax_black Is NULL ))) OR
	((@next_counter_fax_color_full IS NULL AND @cur_counter_fax_color_full IS NOT NULL) OR (@next_counter_fax_color_full = 0 AND (@cur_counter_fax_color_full > 0 OR @cur_counter_fax_color_full Is NULL ))) OR
	((@next_counter_fax_color_mono IS NULL AND @cur_counter_fax_color_mono IS NOT NULL) OR (@next_counter_fax_color_mono = 0 AND (@cur_counter_fax_color_mono > 0 OR @cur_counter_fax_color_mono Is NULL ))) OR
	((@next_counter_fax_send IS NULL AND @cur_counter_fax_send IS NOT NULL) OR (@next_counter_fax_send = 0 AND (@cur_counter_fax_send > 0 OR @cur_counter_fax_send Is NULL ))) OR
	((@next_counter_gpc IS NULL AND @cur_counter_gpc IS NOT NULL) OR (@next_counter_gpc = 0 AND (@cur_counter_gpc > 0 OR @cur_counter_gpc Is NULL ))) OR
	((@next_counter_gpc_printer IS NULL AND @cur_counter_gpc_printer IS NOT NULL) OR (@next_counter_gpc_printer = 0 AND (@cur_counter_gpc_printer > 0 OR @cur_counter_gpc_printer Is NULL ))) OR
	((@next_counter_gpc_color_full IS NULL AND @cur_counter_gpc_color_full IS NOT NULL) OR (@next_counter_gpc_color_full = 0 AND (@cur_counter_gpc_color_full > 0 OR @cur_counter_gpc_color_full Is NULL ))) OR
	((@next_counter_a3_dlt IS NULL AND @cur_counter_a3_dlt IS NOT NULL) OR (@next_counter_a3_dlt = 0 AND (@cur_counter_a3_dlt > 0 OR @cur_counter_a3_dlt Is NULL ))) OR
	((@next_counter_duplex IS NULL AND @cur_counter_duplex IS NOT NULL) OR (@next_counter_duplex = 0 AND (@cur_counter_duplex > 0 OR @cur_counter_duplex Is NULL ))) OR
	((@next_counter_send_color IS NULL AND @cur_counter_send_color IS NOT NULL) OR (@next_counter_send_color = 0 AND (@cur_counter_send_color > 0 OR @cur_counter_send_color Is NULL ))) OR
	((@next_counter_send_mono IS NULL AND @cur_counter_send_mono IS NOT NULL) OR (@next_counter_send_mono = 0 AND (@cur_counter_send_mono > 0 OR @cur_counter_send_mono Is NULL ))) OR
	((@next_counter_fax_color_twin IS NULL AND @cur_counter_fax_color_twin IS NOT NULL) OR (@next_counter_fax_color_twin = 0 AND (@cur_counter_fax_color_twin > 0 OR @cur_counter_fax_color_twin Is NULL ))) OR
	((@next_counter_total IS NULL AND @cur_counter_total IS NOT NULL) OR (@next_counter_total = 0 AND (@cur_counter_total > 0 OR @cur_counter_total Is NULL ))) OR
	((@next_counter_coverage_color IS NULL AND @cur_counter_coverage_color IS NOT NULL) OR (@next_counter_coverage_color = 0 AND (@cur_counter_coverage_color > 0 OR @cur_counter_coverage_color Is NULL ))) OR
	((@next_counter_coverage_black IS NULL AND @cur_counter_coverage_black IS NOT NULL) OR (@next_counter_coverage_black = 0 AND (@cur_counter_coverage_black > 0 OR @cur_counter_coverage_black Is NULL ))) OR
	((@next_counter_cov_color_prt_page IS NULL AND @cur_counter_cov_color_prt_page IS NOT NULL) OR (@next_counter_cov_color_prt_page = 0 AND (@cur_counter_cov_color_prt_page > 0 OR @cur_counter_cov_color_prt_page Is NULL ))) OR
	((@next_counter_cov_black_prt_page IS NULL AND @cur_counter_cov_black_prt_page IS NOT NULL) OR (@next_counter_cov_black_prt_page = 0 AND (@cur_counter_cov_black_prt_page > 0 OR @cur_counter_cov_black_prt_page Is NULL ))) OR
	((@next_counter_a2 IS NULL AND @cur_counter_a2 IS NOT NULL) OR (@next_counter_a2 = 0 AND (@cur_counter_a2 > 0 OR @cur_counter_a2 Is NULL ))) OR
	((@next_counter_scanner_send_color IS NULL AND @cur_counter_scanner_send_color IS NOT NULL) OR (@next_counter_scanner_send_color = 0 AND (@cur_counter_scanner_send_color > 0 OR @cur_counter_scanner_send_color Is NULL ))) OR
	((@next_counter_scanner_send_black IS NULL AND @cur_counter_scanner_send_black IS NOT NULL) OR (@next_counter_scanner_send_black = 0 AND (@cur_counter_scanner_send_black > 0 OR @cur_counter_scanner_send_black Is NULL ))) OR
	((@next_counter_fcolor_sheet_dom IS NULL AND @cur_counter_fcolor_sheet_dom IS NOT NULL) OR (@next_counter_fcolor_sheet_dom = 0 AND (@cur_counter_fcolor_sheet_dom > 0 OR @cur_counter_fcolor_sheet_dom Is NULL ))) OR
	((@next_counter_mcolor_sheet_dom IS NULL AND @cur_counter_mcolor_sheet_dom IS NOT NULL) OR (@next_counter_mcolor_sheet_dom = 0 AND (@cur_counter_mcolor_sheet_dom > 0 OR @cur_counter_mcolor_sheet_dom Is NULL ))) OR
	((@next_counter_fcolor_copy_charge IS NULL AND @cur_counter_fcolor_copy_charge IS NOT NULL) OR (@next_counter_fcolor_copy_charge = 0 AND (@cur_counter_fcolor_copy_charge > 0 OR @cur_counter_fcolor_copy_charge Is NULL ))) OR
	((@next_counter_black_copy_charge IS NULL AND @cur_counter_black_copy_charge IS NOT NULL) OR (@next_counter_black_copy_charge = 0 AND (@cur_counter_black_copy_charge > 0 OR @cur_counter_black_copy_charge Is NULL ))) OR
	((@next_counter_fcolor_prt_charge IS NULL AND @cur_counter_fcolor_prt_charge IS NOT NULL) OR (@next_counter_fcolor_prt_charge = 0 AND (@cur_counter_fcolor_prt_charge > 0 OR @cur_counter_fcolor_prt_charge Is NULL ))) OR
	((@next_counter_black_print_charge IS NULL AND @cur_counter_black_print_charge IS NOT NULL) OR (@next_counter_black_print_charge = 0 AND (@cur_counter_black_print_charge > 0 OR @cur_counter_black_print_charge Is NULL ))) OR
	((@next_counter_fcolor_tot_charge IS NULL AND @cur_counter_fcolor_tot_charge IS NOT NULL) OR (@next_counter_fcolor_tot_charge = 0 AND (@cur_counter_fcolor_tot_charge > 0 OR @cur_counter_fcolor_tot_charge Is NULL ))) OR
	((@next_counter_black_total_charge IS NULL AND @cur_counter_black_total_charge IS NOT NULL) OR (@next_counter_black_total_charge = 0 AND (@cur_counter_black_total_charge > 0 OR @cur_counter_black_total_charge Is NULL ))) OR
	((@next_counter_fcolor_economy_prt IS NULL AND @cur_counter_fcolor_economy_prt IS NOT NULL) OR (@next_counter_fcolor_economy_prt = 0 AND (@cur_counter_fcolor_economy_prt > 0 OR @cur_counter_fcolor_economy_prt Is NULL ))) OR
	((@next_counter_black_economy_prt IS NULL AND @cur_counter_black_economy_prt IS NOT NULL) OR (@next_counter_black_economy_prt = 0 AND (@cur_counter_black_economy_prt > 0 OR @cur_counter_black_economy_prt Is NULL ))) OR
	((@next_counter_fcolor_sheets_prt IS NULL AND @cur_counter_fcolor_sheets_prt IS NOT NULL) OR (@next_counter_fcolor_sheets_prt = 0 AND (@cur_counter_fcolor_sheets_prt > 0 OR @cur_counter_fcolor_sheets_prt Is NULL ))) OR
	((@next_counter_mcolor_sheets_prt IS NULL AND @cur_counter_mcolor_sheets_prt IS NOT NULL) OR (@next_counter_mcolor_sheets_prt = 0 AND (@cur_counter_mcolor_sheets_prt > 0 OR @cur_counter_mcolor_sheets_prt Is NULL ))) OR
	((@next_counter_fcolor_sheets_a3u IS NULL AND @cur_counter_fcolor_sheets_a3u IS NOT NULL) OR (@next_counter_fcolor_sheets_a3u = 0 AND (@cur_counter_fcolor_sheets_a3u > 0 OR @cur_counter_fcolor_sheets_a3u Is NULL ))) OR
	((@next_counter_mcolor_sheets_a3d IS NULL AND @cur_counter_mcolor_sheets_a3d IS NOT NULL) OR (@next_counter_mcolor_sheets_a3d = 0 AND (@cur_counter_mcolor_sheets_a3d > 0 OR @cur_counter_mcolor_sheets_a3d Is NULL ))) OR
	((@next_counter_color_coverage1 IS NULL AND @cur_counter_color_coverage1 IS NOT NULL) OR (@next_counter_color_coverage1 = 0 AND (@cur_counter_color_coverage1 > 0 OR @cur_counter_color_coverage1 Is NULL ))) OR
	((@next_counter_color_coverage2 IS NULL AND @cur_counter_color_coverage2 IS NOT NULL) OR (@next_counter_color_coverage2 = 0 AND (@cur_counter_color_coverage2 > 0 OR @cur_counter_color_coverage2 Is NULL ))) OR
	((@next_counter_color_coverage3 IS NULL AND @cur_counter_color_coverage3 IS NOT NULL) OR (@next_counter_color_coverage3 = 0 AND (@cur_counter_color_coverage3 > 0 OR @cur_counter_color_coverage3 Is NULL ))) OR
	((@next_counter_state_operation IS NULL AND @cur_counter_state_operation IS NOT NULL) OR (@next_counter_state_operation = 0 AND (@cur_counter_state_operation > 0 OR @cur_counter_state_operation Is NULL ))) OR
	((@next_counter_state_waiting IS NULL AND @cur_counter_state_waiting IS NOT NULL) OR (@next_counter_state_waiting = 0 AND (@cur_counter_state_waiting > 0 OR @cur_counter_state_waiting Is NULL ))) OR
	((@next_counter_state_preheat IS NULL AND @cur_counter_state_preheat IS NOT NULL) OR (@next_counter_state_preheat = 0 AND (@cur_counter_state_preheat > 0 OR @cur_counter_state_preheat Is NULL ))) OR
	((@next_counter_state_sleep IS NULL AND @cur_counter_state_sleep IS NOT NULL) OR (@next_counter_state_sleep = 0 AND (@cur_counter_state_sleep > 0 OR @cur_counter_state_sleep Is NULL ))) OR
	((@next_counter_state_offmode IS NULL AND @cur_counter_state_offmode IS NOT NULL) OR (@next_counter_state_offmode = 0 AND (@cur_counter_state_offmode > 0 OR @cur_counter_state_offmode Is NULL ))) OR
	((@next_counter_state_down_sc IS NULL AND @cur_counter_state_down_sc IS NOT NULL) OR (@next_counter_state_down_sc = 0 AND (@cur_counter_state_down_sc > 0 OR @cur_counter_state_down_sc Is NULL ))) OR
	((@next_counter_state_down_pj IS NULL AND @cur_counter_state_down_pj IS NOT NULL) OR (@next_counter_state_down_pj = 0 AND (@cur_counter_state_down_pj > 0 OR @cur_counter_state_down_pj Is NULL ))) OR
	((@next_counter_state_down_sj IS NULL AND @cur_counter_state_down_sj IS NOT NULL) OR (@next_counter_state_down_sj = 0 AND (@cur_counter_state_down_sj > 0 OR @cur_counter_state_down_sj Is NULL ))) OR
	((@next_counter_state_down_sup_pm IS NULL AND @cur_counter_state_down_sup_pm IS NOT NULL) OR (@next_counter_state_down_sup_pm = 0 AND (@cur_counter_state_down_sup_pm > 0 OR @cur_counter_state_down_sup_pm Is NULL ))) )
BEGIN
    SELECT
	@cur_counter_total_color = (CASE WHEN @next_counter_total_color IS NULL OR @next_counter_total_color = 0 THEN @cur_counter_total_color ELSE @next_counter_total_color END),
	@cur_counter_total_mono = (CASE WHEN @next_counter_total_mono IS NULL OR @next_counter_total_mono = 0 THEN @cur_counter_total_mono ELSE @next_counter_total_mono END),
	@cur_counter_copy_black = (CASE WHEN @next_counter_copy_black IS NULL OR @next_counter_copy_black = 0 THEN @cur_counter_copy_black ELSE @next_counter_copy_black END),
	@cur_counter_copy_color_full = (CASE WHEN @next_counter_copy_color_full IS NULL OR @next_counter_copy_color_full = 0 THEN @cur_counter_copy_color_full ELSE @next_counter_copy_color_full END),
	@cur_counter_copy_color_mono = (CASE WHEN @next_counter_copy_color_mono IS NULL OR @next_counter_copy_color_mono = 0 THEN @cur_counter_copy_color_mono ELSE @next_counter_copy_color_mono END),
	@cur_counter_copy_color_twin = (CASE WHEN @next_counter_copy_color_twin IS NULL OR @next_counter_copy_color_twin = 0 THEN @cur_counter_copy_color_twin ELSE @next_counter_copy_color_twin END),
	@cur_counter_printer_black = (CASE WHEN @next_counter_printer_black IS NULL OR @next_counter_printer_black = 0 THEN @cur_counter_printer_black ELSE @next_counter_printer_black END),
	@cur_counter_printer_color_full = (CASE WHEN @next_counter_printer_color_full IS NULL OR @next_counter_printer_color_full = 0 THEN @cur_counter_printer_color_full ELSE @next_counter_printer_color_full END),
	@cur_counter_printer_color_mono = (CASE WHEN @next_counter_printer_color_mono IS NULL OR @next_counter_printer_color_mono = 0 THEN @cur_counter_printer_color_mono ELSE @next_counter_printer_color_mono END),
	@cur_counter_printer_color_twin = (CASE WHEN @next_counter_printer_color_twin IS NULL OR @next_counter_printer_color_twin = 0 THEN @cur_counter_printer_color_twin ELSE @next_counter_printer_color_twin END),
	@cur_counter_printer_color_levl = (CASE WHEN @next_counter_printer_color_levl IS NULL OR @next_counter_printer_color_levl = 0 THEN @cur_counter_printer_color_levl ELSE @next_counter_printer_color_levl END),
	@cur_counter_fax_black = (CASE WHEN @next_counter_fax_black IS NULL OR @next_counter_fax_black = 0 THEN @cur_counter_fax_black ELSE @next_counter_fax_black END),
	@cur_counter_fax_color_full = (CASE WHEN @next_counter_fax_color_full IS NULL OR @next_counter_fax_color_full = 0 THEN @cur_counter_fax_color_full ELSE @next_counter_fax_color_full END),
	@cur_counter_fax_color_mono = (CASE WHEN @next_counter_fax_color_mono IS NULL OR @next_counter_fax_color_mono = 0 THEN @cur_counter_fax_color_mono ELSE @next_counter_fax_color_mono END),
	@cur_counter_fax_send = (CASE WHEN @next_counter_fax_send IS NULL OR @next_counter_fax_send = 0 THEN @cur_counter_fax_send ELSE @next_counter_fax_send END),
	@cur_counter_gpc = (CASE WHEN @next_counter_gpc IS NULL OR @next_counter_gpc = 0 THEN @cur_counter_gpc ELSE @next_counter_gpc END),
	@cur_counter_gpc_printer = (CASE WHEN @next_counter_gpc_printer IS NULL OR @next_counter_gpc_printer = 0 THEN @cur_counter_gpc_printer ELSE @next_counter_gpc_printer END),
	@cur_counter_gpc_color_full = (CASE WHEN @next_counter_gpc_color_full IS NULL OR @next_counter_gpc_color_full = 0 THEN @cur_counter_gpc_color_full ELSE @next_counter_gpc_color_full END),
	@cur_counter_a3_dlt = (CASE WHEN @next_counter_a3_dlt IS NULL OR @next_counter_a3_dlt = 0 THEN @cur_counter_a3_dlt ELSE @next_counter_a3_dlt END),
	@cur_counter_duplex = (CASE WHEN @next_counter_duplex IS NULL OR @next_counter_duplex = 0 THEN @cur_counter_duplex ELSE @next_counter_duplex END),
	@cur_counter_send_color = (CASE WHEN @next_counter_send_color IS NULL OR @next_counter_send_color = 0 THEN @cur_counter_send_color ELSE @next_counter_send_color END),
	@cur_counter_send_mono = (CASE WHEN @next_counter_send_mono IS NULL OR @next_counter_send_mono = 0 THEN @cur_counter_send_mono ELSE @next_counter_send_mono END),
	@cur_counter_fax_color_twin = (CASE WHEN @next_counter_fax_color_twin IS NULL OR @next_counter_fax_color_twin = 0 THEN @cur_counter_fax_color_twin ELSE @next_counter_fax_color_twin END),
	@cur_counter_total = (CASE WHEN @next_counter_total IS NULL OR @next_counter_total = 0 THEN @cur_counter_total ELSE @next_counter_total END),
	@cur_counter_coverage_color = (CASE WHEN @next_counter_coverage_color IS NULL OR @next_counter_coverage_color = 0 THEN @cur_counter_coverage_color ELSE @next_counter_coverage_color END),
	@cur_counter_coverage_black = (CASE WHEN @next_counter_coverage_black IS NULL OR @next_counter_coverage_black = 0 THEN @cur_counter_coverage_black ELSE @next_counter_coverage_black END),
	@cur_counter_cov_color_prt_page = (CASE WHEN @next_counter_cov_color_prt_page IS NULL OR @next_counter_cov_color_prt_page = 0 THEN @cur_counter_cov_color_prt_page ELSE @next_counter_cov_color_prt_page END),
	@cur_counter_cov_black_prt_page = (CASE WHEN @next_counter_cov_black_prt_page IS NULL OR @next_counter_cov_black_prt_page = 0 THEN @cur_counter_cov_black_prt_page ELSE @next_counter_cov_black_prt_page END),
	@cur_counter_a2 = (CASE WHEN @next_counter_a2 IS NULL OR @next_counter_a2 = 0 THEN @cur_counter_a2 ELSE @next_counter_a2 END),
	@cur_counter_scanner_send_color = (CASE WHEN @next_counter_scanner_send_color IS NULL OR @next_counter_scanner_send_color = 0 THEN @cur_counter_scanner_send_color ELSE @next_counter_scanner_send_color END),
	@cur_counter_scanner_send_black = (CASE WHEN @next_counter_scanner_send_black IS NULL OR @next_counter_scanner_send_black = 0 THEN @cur_counter_scanner_send_black ELSE @next_counter_scanner_send_black END),
	@cur_counter_fcolor_sheet_dom = (CASE WHEN @next_counter_fcolor_sheet_dom IS NULL OR @next_counter_fcolor_sheet_dom = 0 THEN @cur_counter_fcolor_sheet_dom ELSE @next_counter_fcolor_sheet_dom END),
	@cur_counter_mcolor_sheet_dom = (CASE WHEN @next_counter_mcolor_sheet_dom IS NULL OR @next_counter_mcolor_sheet_dom = 0 THEN @cur_counter_mcolor_sheet_dom ELSE @next_counter_mcolor_sheet_dom END),
	@cur_counter_fcolor_copy_charge = (CASE WHEN @next_counter_fcolor_copy_charge IS NULL OR @next_counter_fcolor_copy_charge = 0 THEN @cur_counter_fcolor_copy_charge ELSE @next_counter_fcolor_copy_charge END),
	@cur_counter_black_copy_charge = (CASE WHEN @next_counter_black_copy_charge IS NULL OR @next_counter_black_copy_charge = 0 THEN @cur_counter_black_copy_charge ELSE @next_counter_black_copy_charge END),
	@cur_counter_fcolor_prt_charge = (CASE WHEN @next_counter_fcolor_prt_charge IS NULL OR @next_counter_fcolor_prt_charge = 0 THEN @cur_counter_fcolor_prt_charge ELSE @next_counter_fcolor_prt_charge END),
	@cur_counter_black_print_charge = (CASE WHEN @next_counter_black_print_charge IS NULL OR @next_counter_black_print_charge = 0 THEN @cur_counter_black_print_charge ELSE @next_counter_black_print_charge END),
	@cur_counter_fcolor_tot_charge = (CASE WHEN @next_counter_fcolor_tot_charge IS NULL OR @next_counter_fcolor_tot_charge = 0 THEN @cur_counter_fcolor_tot_charge ELSE @next_counter_fcolor_tot_charge END),
	@cur_counter_black_total_charge = (CASE WHEN @next_counter_black_total_charge IS NULL OR @next_counter_black_total_charge = 0 THEN @cur_counter_black_total_charge ELSE @next_counter_black_total_charge END),
	@cur_counter_fcolor_economy_prt = (CASE WHEN @next_counter_fcolor_economy_prt IS NULL OR @next_counter_fcolor_economy_prt = 0 THEN @cur_counter_fcolor_economy_prt ELSE @next_counter_fcolor_economy_prt END),
	@cur_counter_black_economy_prt = (CASE WHEN @next_counter_black_economy_prt IS NULL OR @next_counter_black_economy_prt = 0 THEN @cur_counter_black_economy_prt ELSE @next_counter_black_economy_prt END),
	@cur_counter_fcolor_sheets_prt = (CASE WHEN @next_counter_fcolor_sheets_prt IS NULL OR @next_counter_fcolor_sheets_prt = 0 THEN @cur_counter_fcolor_sheets_prt ELSE @next_counter_fcolor_sheets_prt END),
	@cur_counter_mcolor_sheets_prt = (CASE WHEN @next_counter_mcolor_sheets_prt IS NULL OR @next_counter_mcolor_sheets_prt = 0 THEN @cur_counter_mcolor_sheets_prt ELSE @next_counter_mcolor_sheets_prt END),
	@cur_counter_fcolor_sheets_a3u = (CASE WHEN @next_counter_fcolor_sheets_a3u IS NULL OR @next_counter_fcolor_sheets_a3u = 0 THEN @cur_counter_fcolor_sheets_a3u ELSE @next_counter_fcolor_sheets_a3u END),
	@cur_counter_mcolor_sheets_a3d = (CASE WHEN @next_counter_mcolor_sheets_a3d IS NULL OR @next_counter_mcolor_sheets_a3d = 0 THEN @cur_counter_mcolor_sheets_a3d ELSE @next_counter_mcolor_sheets_a3d END),
	@cur_counter_color_coverage1 = (CASE WHEN @next_counter_color_coverage1 IS NULL OR @next_counter_color_coverage1 = 0 THEN @cur_counter_color_coverage1 ELSE @next_counter_color_coverage1 END),
	@cur_counter_color_coverage2 = (CASE WHEN @next_counter_color_coverage2 IS NULL OR @next_counter_color_coverage2 = 0 THEN @cur_counter_color_coverage2 ELSE @next_counter_color_coverage2 END),
	@cur_counter_color_coverage3 = (CASE WHEN @next_counter_color_coverage3 IS NULL OR @next_counter_color_coverage3 = 0 THEN @cur_counter_color_coverage3 ELSE @next_counter_color_coverage3 END),
	@cur_counter_state_operation = (CASE WHEN @next_counter_state_operation IS NULL OR @next_counter_state_operation = 0 THEN @cur_counter_state_operation ELSE @next_counter_state_operation END),
	@cur_counter_state_waiting = (CASE WHEN @next_counter_state_waiting IS NULL OR @next_counter_state_waiting = 0 THEN @cur_counter_state_waiting ELSE @next_counter_state_waiting END),
	@cur_counter_state_preheat = (CASE WHEN @next_counter_state_preheat IS NULL OR @next_counter_state_preheat = 0 THEN @cur_counter_state_preheat ELSE @next_counter_state_preheat END),
	@cur_counter_state_sleep = (CASE WHEN @next_counter_state_sleep IS NULL OR @next_counter_state_sleep = 0 THEN @cur_counter_state_sleep ELSE @next_counter_state_sleep END),
	@cur_counter_state_offmode = (CASE WHEN @next_counter_state_offmode IS NULL OR @next_counter_state_offmode = 0 THEN @cur_counter_state_offmode ELSE @next_counter_state_offmode END),
	@cur_counter_state_down_sc = (CASE WHEN @next_counter_state_down_sc IS NULL OR @next_counter_state_down_sc = 0 THEN @cur_counter_state_down_sc ELSE @next_counter_state_down_sc END),
	@cur_counter_state_down_pj = (CASE WHEN @next_counter_state_down_pj IS NULL OR @next_counter_state_down_pj = 0 THEN @cur_counter_state_down_pj ELSE @next_counter_state_down_pj END),
	@cur_counter_state_down_sj = (CASE WHEN @next_counter_state_down_sj IS NULL OR @next_counter_state_down_sj = 0 THEN @cur_counter_state_down_sj ELSE @next_counter_state_down_sj END),
	@cur_counter_state_down_sup_pm = (CASE WHEN @next_counter_state_down_sup_pm IS NULL OR @next_counter_state_down_sup_pm = 0 THEN @cur_counter_state_down_sup_pm ELSE @next_counter_state_down_sup_pm END)

    SET @updated = @updated + 1
    UPDATE device_counter SET 
		dev_counter_total_color = @cur_counter_total_color,
		dev_counter_total_mono = @cur_counter_total_mono,
		dev_counter_copy_black = @cur_counter_copy_black,
		dev_counter_copy_color_full = @cur_counter_copy_color_full,
		dev_counter_copy_color_mono = @cur_counter_copy_color_mono,
		dev_counter_copy_color_twin = @cur_counter_copy_color_twin,
		dev_counter_printer_black = @cur_counter_printer_black,
		dev_counter_printer_color_full = @cur_counter_printer_color_full,
		dev_counter_printer_color_mono = @cur_counter_printer_color_mono,
		dev_counter_printer_color_twin = @cur_counter_printer_color_twin,
		dev_counter_printer_color_levl = @cur_counter_printer_color_levl,
		dev_counter_fax_black = @cur_counter_fax_black,
		dev_counter_fax_color_full = @cur_counter_fax_color_full,
		dev_counter_fax_color_mono = @cur_counter_fax_color_mono,
		dev_counter_fax_send = @cur_counter_fax_send,
		dev_counter_gpc = @cur_counter_gpc,
		dev_counter_gpc_printer = @cur_counter_gpc_printer,
		dev_counter_gpc_color_full = @cur_counter_gpc_color_full,
		dev_counter_a3_dlt = @cur_counter_a3_dlt,
		dev_counter_duplex = @cur_counter_duplex,
		dev_counter_send_color = @cur_counter_send_color,
		dev_counter_send_mono = @cur_counter_send_mono,
		dev_counter_fax_color_twin = @cur_counter_fax_color_twin,
		dev_counter_total = @cur_counter_total,
		dev_counter_coverage_color = @cur_counter_coverage_color,
		dev_counter_coverage_black = @cur_counter_coverage_black,
		dev_counter_cov_color_prt_page = @cur_counter_cov_color_prt_page,
		dev_counter_cov_black_prt_page = @cur_counter_cov_black_prt_page,
		dev_counter_a2 = @cur_counter_a2,
		dev_counter_scanner_send_color = @cur_counter_scanner_send_color,
		dev_counter_scanner_send_black = @cur_counter_scanner_send_black,
		dev_counter_fcolor_sheet_dom = @cur_counter_fcolor_sheet_dom,
		dev_counter_mcolor_sheet_dom = @cur_counter_mcolor_sheet_dom,
		dev_counter_fcolor_copy_charge = @cur_counter_fcolor_copy_charge,
		dev_counter_black_copy_charge = @cur_counter_black_copy_charge,
		dev_counter_fcolor_prt_charge = @cur_counter_fcolor_prt_charge,
		dev_counter_black_print_charge = @cur_counter_black_print_charge,
		dev_counter_fcolor_tot_charge = @cur_counter_fcolor_tot_charge,
		dev_counter_black_total_charge = @cur_counter_black_total_charge,
		dev_counter_fcolor_economy_prt = @cur_counter_fcolor_economy_prt,
		dev_counter_black_economy_prt = @cur_counter_black_economy_prt,
		dev_counter_fcolor_sheets_prt = @cur_counter_fcolor_sheets_prt,
		dev_counter_mcolor_sheets_prt = @cur_counter_mcolor_sheets_prt,
		dev_counter_fcolor_sheets_a3u = @cur_counter_fcolor_sheets_a3u,
		dev_counter_mcolor_sheets_a3d = @cur_counter_mcolor_sheets_a3d,
		dev_counter_color_coverage1 = @cur_counter_color_coverage1,
		dev_counter_color_coverage2 = @cur_counter_color_coverage2,
		dev_counter_color_coverage3 = @cur_counter_color_coverage3,
		dev_counter_state_operation = @cur_counter_state_operation,
		dev_counter_state_waiting = @cur_counter_state_waiting,
		dev_counter_state_preheat = @cur_counter_state_preheat,
		dev_counter_state_sleep = @cur_counter_state_sleep,
		dev_counter_state_offmode = @cur_counter_state_offmode,
		dev_counter_state_down_sc = @cur_counter_state_down_sc,
		dev_counter_state_down_pj = @cur_counter_state_down_pj,
		dev_counter_state_down_sj = @cur_counter_state_down_sj,
		dev_counter_state_down_sup_pm = @cur_counter_state_down_sup_pm        
	WHERE CURRENT OF UPDCURSOR

END ELSE
	BEGIN
		SELECT
			@cur_counter_total_color = @next_counter_total_color,
			@cur_counter_total_mono = @next_counter_total_mono,
			@cur_counter_copy_black = @next_counter_copy_black,
			@cur_counter_copy_color_full = @next_counter_copy_color_full,
			@cur_counter_copy_color_mono = @next_counter_copy_color_mono,
			@cur_counter_copy_color_twin = @next_counter_copy_color_twin,
			@cur_counter_printer_black = @next_counter_printer_black,
			@cur_counter_printer_color_full = @next_counter_printer_color_full,
			@cur_counter_printer_color_mono = @next_counter_printer_color_mono,
			@cur_counter_printer_color_twin = @next_counter_printer_color_twin,
			@cur_counter_printer_color_levl = @next_counter_printer_color_levl,
			@cur_counter_fax_black = @next_counter_fax_black,
			@cur_counter_fax_color_full = @next_counter_fax_color_full,
			@cur_counter_fax_color_mono = @next_counter_fax_color_mono,
			@cur_counter_fax_send = @next_counter_fax_send,
			@cur_counter_gpc = @next_counter_gpc,
			@cur_counter_gpc_printer = @next_counter_gpc_printer,
			@cur_counter_gpc_color_full = @next_counter_gpc_color_full,
			@cur_counter_a3_dlt = @next_counter_a3_dlt,
			@cur_counter_duplex = @next_counter_duplex,
			@cur_counter_send_color = @next_counter_send_color,
			@cur_counter_send_mono = @next_counter_send_mono,
			@cur_counter_fax_color_twin = @next_counter_fax_color_twin,
			@cur_counter_total = @next_counter_total,
			@cur_counter_coverage_color = @next_counter_coverage_color,
			@cur_counter_coverage_black = @next_counter_coverage_black,
			@cur_counter_cov_color_prt_page = @next_counter_cov_color_prt_page,
			@cur_counter_cov_black_prt_page = @next_counter_cov_black_prt_page,
			@cur_counter_a2 = @next_counter_a2,
			@cur_counter_scanner_send_color = @next_counter_scanner_send_color,
			@cur_counter_scanner_send_black = @next_counter_scanner_send_black,
			@cur_counter_fcolor_sheet_dom = @next_counter_fcolor_sheet_dom,
			@cur_counter_mcolor_sheet_dom = @next_counter_mcolor_sheet_dom,
			@cur_counter_fcolor_copy_charge = @next_counter_fcolor_copy_charge,
			@cur_counter_black_copy_charge = @next_counter_black_copy_charge,
			@cur_counter_fcolor_prt_charge = @next_counter_fcolor_prt_charge,
			@cur_counter_black_print_charge = @next_counter_black_print_charge,
			@cur_counter_fcolor_tot_charge = @next_counter_fcolor_tot_charge,
			@cur_counter_black_total_charge = @next_counter_black_total_charge,
			@cur_counter_fcolor_economy_prt = @next_counter_fcolor_economy_prt,
			@cur_counter_black_economy_prt = @next_counter_black_economy_prt,
			@cur_counter_fcolor_sheets_prt = @next_counter_fcolor_sheets_prt,
			@cur_counter_mcolor_sheets_prt = @next_counter_mcolor_sheets_prt,
			@cur_counter_fcolor_sheets_a3u = @next_counter_fcolor_sheets_a3u,
			@cur_counter_mcolor_sheets_a3d = @next_counter_mcolor_sheets_a3d,
			@cur_counter_color_coverage1 = @next_counter_color_coverage1,
			@cur_counter_color_coverage2 = @next_counter_color_coverage2,
			@cur_counter_color_coverage3 = @next_counter_color_coverage3,
			@cur_counter_state_operation = @next_counter_state_operation,
			@cur_counter_state_waiting = @next_counter_state_waiting,
			@cur_counter_state_preheat = @next_counter_state_preheat,
			@cur_counter_state_sleep = @next_counter_state_sleep,
			@cur_counter_state_offmode = @next_counter_state_offmode,
			@cur_counter_state_down_sc = @next_counter_state_down_sc,
			@cur_counter_state_down_pj = @next_counter_state_down_pj,
			@cur_counter_state_down_sj = @next_counter_state_down_sj,
			@cur_counter_state_down_sup_pm = @next_counter_state_down_sup_pm
	END
	SET @cur_dev_id = @next_dev_id
END
CLOSE UPDCURSOR
DEALLOCATE UPDCURSOR
END
SELECT @updated
GO

xtender 11 фев 2016 в 22:55

Я не разбираюсь в MS SQL/TSQL, но этот код выглядит адски страшным. И, впредь, смотрите куда комментарии пишете.

minamoto 11 фев 2016 в 23:31

Собственно налицо еще и неверно спроектированная база данных.

Ваша заливка рассчитана на Oracle, если будет время — попробую перевести ее на SQL и привести свой запрос к варианту, учитывающему множество колонок, чтобы сравнить производительность.

xtender 11 фев 2016 в 23:36

Это не оракл :) у нас такого нет :)

minamoto 12 фев 2016 в 01:12

Я использовал ваши тестовые данные для проверки пары гипотез и, несмотря на то, что мне удалось в разы улучшить результат по сравнению с вашим изначальным SQL-кодом, я вынужден все же признать, что курсор здесь дает лучший результат из проверенных мной.

Причина — отсутствие подходящих аналитических функций — last_value(… ignore nulls ) в MS SQL Server.

Вот результаты в миллисекундах (вместо 8000 строк использовал 8 для более быстрых тестов и поправил ошибку в коде генерации исходных данных — должно быть DATEADD( hour, day, '2000-01-01')):
1 — SQL way: 36210
2 — Cursor way: 256
3 — New SQL way: 8863
4 — New SQL way — normalized: 5083

Под New SQL Way подразумевается приведенный мной ниже код, распространенный на все колонки (по одному апдейту для каждой колонки):

New SQL way

Update t1  Set  [dev_counter_total_color]= t2.[dev_counter_total_color] from device_counter t1   cross apply (select top 1 [dev_counter_total_color]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_total_color] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_total_color] is null
Update t1  Set  [dev_counter_total_mono]= t2.[dev_counter_total_mono] from device_counter t1   cross apply (select top 1 [dev_counter_total_mono]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_total_mono] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_total_mono] is null
Update t1  Set  [dev_counter_copy_black]= t2.[dev_counter_copy_black] from device_counter t1   cross apply (select top 1 [dev_counter_copy_black]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_copy_black] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_copy_black] is null
Update t1  Set  [dev_counter_copy_color_full]= t2.[dev_counter_copy_color_full] from device_counter t1   cross apply (select top 1 [dev_counter_copy_color_full]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_copy_color_full] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_copy_color_full] is null
Update t1  Set  [dev_counter_copy_color_mono]= t2.[dev_counter_copy_color_mono] from device_counter t1   cross apply (select top 1 [dev_counter_copy_color_mono]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_copy_color_mono] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_copy_color_mono] is null
Update t1  Set  [dev_counter_copy_color_twin]= t2.[dev_counter_copy_color_twin] from device_counter t1   cross apply (select top 1 [dev_counter_copy_color_twin]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_copy_color_twin] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_copy_color_twin] is null
Update t1  Set  [dev_counter_printer_black]= t2.[dev_counter_printer_black] from device_counter t1   cross apply (select top 1 [dev_counter_printer_black]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_printer_black] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_printer_black] is null
Update t1  Set  [dev_counter_printer_color_full]= t2.[dev_counter_printer_color_full] from device_counter t1   cross apply (select top 1 [dev_counter_printer_color_full]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_printer_color_full] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_printer_color_full] is null
Update t1  Set  [dev_counter_printer_color_mono]= t2.[dev_counter_printer_color_mono] from device_counter t1   cross apply (select top 1 [dev_counter_printer_color_mono]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_printer_color_mono] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_printer_color_mono] is null
Update t1  Set  [dev_counter_printer_color_twin]= t2.[dev_counter_printer_color_twin] from device_counter t1   cross apply (select top 1 [dev_counter_printer_color_twin]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_printer_color_twin] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_printer_color_twin] is null
Update t1  Set  [dev_counter_printer_color_levl]= t2.[dev_counter_printer_color_levl] from device_counter t1   cross apply (select top 1 [dev_counter_printer_color_levl]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_printer_color_levl] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_printer_color_levl] is null
Update t1  Set  [dev_counter_fax_black]= t2.[dev_counter_fax_black] from device_counter t1   cross apply (select top 1 [dev_counter_fax_black]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_fax_black] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_fax_black] is null
Update t1  Set  [dev_counter_fax_color_full]= t2.[dev_counter_fax_color_full] from device_counter t1   cross apply (select top 1 [dev_counter_fax_color_full]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_fax_color_full] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_fax_color_full] is null
Update t1  Set  [dev_counter_fax_color_mono]= t2.[dev_counter_fax_color_mono] from device_counter t1   cross apply (select top 1 [dev_counter_fax_color_mono]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_fax_color_mono] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_fax_color_mono] is null
Update t1  Set  [dev_counter_fax_send]= t2.[dev_counter_fax_send] from device_counter t1   cross apply (select top 1 [dev_counter_fax_send]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_fax_send] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_fax_send] is null
Update t1  Set  [dev_counter_gpc]= t2.[dev_counter_gpc] from device_counter t1   cross apply (select top 1 [dev_counter_gpc]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_gpc] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_gpc] is null
Update t1  Set  [dev_counter_gpc_printer]= t2.[dev_counter_gpc_printer] from device_counter t1   cross apply (select top 1 [dev_counter_gpc_printer]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_gpc_printer] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_gpc_printer] is null
Update t1  Set  [dev_counter_gpc_color_full]= t2.[dev_counter_gpc_color_full] from device_counter t1   cross apply (select top 1 [dev_counter_gpc_color_full]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_gpc_color_full] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_gpc_color_full] is null
Update t1  Set  [dev_counter_a3_dlt]= t2.[dev_counter_a3_dlt] from device_counter t1   cross apply (select top 1 [dev_counter_a3_dlt]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_a3_dlt] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_a3_dlt] is null
Update t1  Set  [dev_counter_duplex]= t2.[dev_counter_duplex] from device_counter t1   cross apply (select top 1 [dev_counter_duplex]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_duplex] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_duplex] is null
Update t1  Set  [dev_counter_send_color]= t2.[dev_counter_send_color] from device_counter t1   cross apply (select top 1 [dev_counter_send_color]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_send_color] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_send_color] is null
Update t1  Set  [dev_counter_send_mono]= t2.[dev_counter_send_mono] from device_counter t1   cross apply (select top 1 [dev_counter_send_mono]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_send_mono] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_send_mono] is null
Update t1  Set  [dev_counter_fax_color_twin]= t2.[dev_counter_fax_color_twin] from device_counter t1   cross apply (select top 1 [dev_counter_fax_color_twin]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_fax_color_twin] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_fax_color_twin] is null
Update t1  Set  [dev_counter_total]= t2.[dev_counter_total] from device_counter t1   cross apply (select top 1 [dev_counter_total]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_total] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_total] is null
Update t1  Set  [dev_counter_coverage_color]= t2.[dev_counter_coverage_color] from device_counter t1   cross apply (select top 1 [dev_counter_coverage_color]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_coverage_color] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_coverage_color] is null
Update t1  Set  [dev_counter_coverage_black]= t2.[dev_counter_coverage_black] from device_counter t1   cross apply (select top 1 [dev_counter_coverage_black]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_coverage_black] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_coverage_black] is null
Update t1  Set  [dev_counter_cov_color_prt_page]= t2.[dev_counter_cov_color_prt_page] from device_counter t1   cross apply (select top 1 [dev_counter_cov_color_prt_page]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_cov_color_prt_page] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_cov_color_prt_page] is null
Update t1  Set  [dev_counter_cov_black_prt_page]= t2.[dev_counter_cov_black_prt_page] from device_counter t1   cross apply (select top 1 [dev_counter_cov_black_prt_page]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_cov_black_prt_page] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_cov_black_prt_page] is null
Update t1  Set  [dev_counter_a2]= t2.[dev_counter_a2] from device_counter t1   cross apply (select top 1 [dev_counter_a2]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_a2] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_a2] is null
Update t1  Set  [dev_counter_scanner_send_color]= t2.[dev_counter_scanner_send_color] from device_counter t1   cross apply (select top 1 [dev_counter_scanner_send_color]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_scanner_send_color] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_scanner_send_color] is null
Update t1  Set  [dev_counter_scanner_send_black]= t2.[dev_counter_scanner_send_black] from device_counter t1   cross apply (select top 1 [dev_counter_scanner_send_black]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_scanner_send_black] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_scanner_send_black] is null
Update t1  Set  [dev_counter_fcolor_sheet_dom]= t2.[dev_counter_fcolor_sheet_dom] from device_counter t1   cross apply (select top 1 [dev_counter_fcolor_sheet_dom]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_fcolor_sheet_dom] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_fcolor_sheet_dom] is null
Update t1  Set  [dev_counter_mcolor_sheet_dom]= t2.[dev_counter_mcolor_sheet_dom] from device_counter t1   cross apply (select top 1 [dev_counter_mcolor_sheet_dom]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_mcolor_sheet_dom] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_mcolor_sheet_dom] is null
Update t1  Set  [dev_counter_fcolor_copy_charge]= t2.[dev_counter_fcolor_copy_charge] from device_counter t1   cross apply (select top 1 [dev_counter_fcolor_copy_charge]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_fcolor_copy_charge] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_fcolor_copy_charge] is null
Update t1  Set  [dev_counter_black_copy_charge]= t2.[dev_counter_black_copy_charge] from device_counter t1   cross apply (select top 1 [dev_counter_black_copy_charge]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_black_copy_charge] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_black_copy_charge] is null
Update t1  Set  [dev_counter_fcolor_prt_charge]= t2.[dev_counter_fcolor_prt_charge] from device_counter t1   cross apply (select top 1 [dev_counter_fcolor_prt_charge]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_fcolor_prt_charge] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_fcolor_prt_charge] is null
Update t1  Set  [dev_counter_black_print_charge]= t2.[dev_counter_black_print_charge] from device_counter t1   cross apply (select top 1 [dev_counter_black_print_charge]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_black_print_charge] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_black_print_charge] is null
Update t1  Set  [dev_counter_fcolor_tot_charge]= t2.[dev_counter_fcolor_tot_charge] from device_counter t1   cross apply (select top 1 [dev_counter_fcolor_tot_charge]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_fcolor_tot_charge] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_fcolor_tot_charge] is null
Update t1  Set  [dev_counter_black_total_charge]= t2.[dev_counter_black_total_charge] from device_counter t1   cross apply (select top 1 [dev_counter_black_total_charge]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_black_total_charge] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_black_total_charge] is null
Update t1  Set  [dev_counter_fcolor_economy_prt]= t2.[dev_counter_fcolor_economy_prt] from device_counter t1   cross apply (select top 1 [dev_counter_fcolor_economy_prt]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_fcolor_economy_prt] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_fcolor_economy_prt] is null
Update t1  Set  [dev_counter_black_economy_prt]= t2.[dev_counter_black_economy_prt] from device_counter t1   cross apply (select top 1 [dev_counter_black_economy_prt]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_black_economy_prt] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_black_economy_prt] is null
Update t1  Set  [dev_counter_fcolor_sheets_prt]= t2.[dev_counter_fcolor_sheets_prt] from device_counter t1   cross apply (select top 1 [dev_counter_fcolor_sheets_prt]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_fcolor_sheets_prt] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_fcolor_sheets_prt] is null
Update t1  Set  [dev_counter_mcolor_sheets_prt]= t2.[dev_counter_mcolor_sheets_prt] from device_counter t1   cross apply (select top 1 [dev_counter_mcolor_sheets_prt]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_mcolor_sheets_prt] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_mcolor_sheets_prt] is null
Update t1  Set  [dev_counter_fcolor_sheets_a3u]= t2.[dev_counter_fcolor_sheets_a3u] from device_counter t1   cross apply (select top 1 [dev_counter_fcolor_sheets_a3u]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_fcolor_sheets_a3u] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_fcolor_sheets_a3u] is null
Update t1  Set  [dev_counter_mcolor_sheets_a3d]= t2.[dev_counter_mcolor_sheets_a3d] from device_counter t1   cross apply (select top 1 [dev_counter_mcolor_sheets_a3d]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_mcolor_sheets_a3d] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_mcolor_sheets_a3d] is null
Update t1  Set  [dev_counter_color_coverage1]= t2.[dev_counter_color_coverage1] from device_counter t1   cross apply (select top 1 [dev_counter_color_coverage1]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_color_coverage1] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_color_coverage1] is null
Update t1  Set  [dev_counter_color_coverage2]= t2.[dev_counter_color_coverage2] from device_counter t1   cross apply (select top 1 [dev_counter_color_coverage2]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_color_coverage2] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_color_coverage2] is null
Update t1  Set  [dev_counter_color_coverage3]= t2.[dev_counter_color_coverage3] from device_counter t1   cross apply (select top 1 [dev_counter_color_coverage3]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_color_coverage3] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_color_coverage3] is null
Update t1  Set  [dev_counter_state_operation]= t2.[dev_counter_state_operation] from device_counter t1   cross apply (select top 1 [dev_counter_state_operation]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_state_operation] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_state_operation] is null
Update t1  Set  [dev_counter_state_waiting]= t2.[dev_counter_state_waiting] from device_counter t1   cross apply (select top 1 [dev_counter_state_waiting]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_state_waiting] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_state_waiting] is null
Update t1  Set  [dev_counter_state_preheat]= t2.[dev_counter_state_preheat] from device_counter t1   cross apply (select top 1 [dev_counter_state_preheat]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_state_preheat] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_state_preheat] is null
Update t1  Set  [dev_counter_state_sleep]= t2.[dev_counter_state_sleep] from device_counter t1   cross apply (select top 1 [dev_counter_state_sleep]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_state_sleep] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_state_sleep] is null
Update t1  Set  [dev_counter_state_offmode]= t2.[dev_counter_state_offmode] from device_counter t1   cross apply (select top 1 [dev_counter_state_offmode]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_state_offmode] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_state_offmode] is null
Update t1  Set  [dev_counter_state_down_sc]= t2.[dev_counter_state_down_sc] from device_counter t1   cross apply (select top 1 [dev_counter_state_down_sc]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_state_down_sc] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_state_down_sc] is null
Update t1  Set  [dev_counter_state_down_pj]= t2.[dev_counter_state_down_pj] from device_counter t1   cross apply (select top 1 [dev_counter_state_down_pj]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_state_down_pj] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_state_down_pj] is null
Update t1  Set  [dev_counter_state_down_sj]= t2.[dev_counter_state_down_sj] from device_counter t1   cross apply (select top 1 [dev_counter_state_down_sj]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_state_down_sj] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_state_down_sj] is null
Update t1  Set  [dev_counter_state_down_sup_pm]= t2.[dev_counter_state_down_sup_pm] from device_counter t1   cross apply (select top 1 [dev_counter_state_down_sup_pm]          from device_counter t2       where t1.dev_id = t2.dev_id       and t1.dev_counter_date > t2.dev_counter_date       and t2.[dev_counter_state_down_sup_pm] is not null      order by t2.dev_counter_date desc      ) as t2 where t1.[dev_counter_state_down_sup_pm] is null

Под New SQL way — normalized — приведение к более верной форме таблицы (когда все значения сведены к одной колонке и добавлена колонка «Тип значения»):

Код создания новой таблицы

if object_id('device_counter_norm') is not null drop table device_counter_norm
go
CREATE TABLE device_counter_norm
(
	[dev_id] [int] NOT NULL,
	[dev_counter_date] [datetime] NOT NULL,
	[dev_counter] nvarchar(255) NOT NULL,
	Value int
	CONSTRAINT PK_device_counter_norm PRIMARY KEY CLUSTERED (dev_counter_date, dev_id, [dev_counter])
);
go

insert into device_counter_norm (dev_id, dev_counter_date, dev_counter, Value)

SELECT [dev_id], [dev_counter_date], [dev_counter], nullif(Value, -1)
FROM 
   (SELECT dev_id, dev_counter_date, isnull(	[dev_counter_total_color]	, -1) as 	[dev_counter_total_color]	,
isnull(	[dev_counter_total_mono]	, -1) as 	[dev_counter_total_mono]	,
isnull(	[dev_counter_copy_black]	, -1) as 	[dev_counter_copy_black]	,
isnull(	[dev_counter_copy_color_full]	, -1) as 	[dev_counter_copy_color_full]	,
isnull(	[dev_counter_copy_color_mono]	, -1) as 	[dev_counter_copy_color_mono]	,
isnull(	[dev_counter_copy_color_twin]	, -1) as 	[dev_counter_copy_color_twin]	,
isnull(	[dev_counter_printer_black]	, -1) as 	[dev_counter_printer_black]	,
isnull(	[dev_counter_printer_color_full]	, -1) as 	[dev_counter_printer_color_full]	,
isnull(	[dev_counter_printer_color_mono]	, -1) as 	[dev_counter_printer_color_mono]	,
isnull(	[dev_counter_printer_color_twin]	, -1) as 	[dev_counter_printer_color_twin]	,
isnull(	[dev_counter_printer_color_levl]	, -1) as 	[dev_counter_printer_color_levl]	,
isnull(	[dev_counter_fax_black]	, -1) as 	[dev_counter_fax_black]	,
isnull(	[dev_counter_fax_color_full]	, -1) as 	[dev_counter_fax_color_full]	,
isnull(	[dev_counter_fax_color_mono]	, -1) as 	[dev_counter_fax_color_mono]	,
isnull(	[dev_counter_fax_send]	, -1) as 	[dev_counter_fax_send]	,
isnull(	[dev_counter_gpc]	, -1) as 	[dev_counter_gpc]	,
isnull(	[dev_counter_gpc_printer]	, -1) as 	[dev_counter_gpc_printer]	,
isnull(	[dev_counter_gpc_color_full]	, -1) as 	[dev_counter_gpc_color_full]	,
isnull(	[dev_counter_a3_dlt]	, -1) as 	[dev_counter_a3_dlt]	,
isnull(	[dev_counter_duplex]	, -1) as 	[dev_counter_duplex]	,
isnull(	[dev_counter_send_color]	, -1) as 	[dev_counter_send_color]	,
isnull(	[dev_counter_send_mono]	, -1) as 	[dev_counter_send_mono]	,
isnull(	[dev_counter_fax_color_twin]	, -1) as 	[dev_counter_fax_color_twin]	,
isnull(	[dev_counter_total]	, -1) as 	[dev_counter_total]	,
isnull(	[dev_counter_coverage_color]	, -1) as 	[dev_counter_coverage_color]	,
isnull(	[dev_counter_coverage_black]	, -1) as 	[dev_counter_coverage_black]	,
isnull(	[dev_counter_cov_color_prt_page]	, -1) as 	[dev_counter_cov_color_prt_page]	,
isnull(	[dev_counter_cov_black_prt_page]	, -1) as 	[dev_counter_cov_black_prt_page]	,
isnull(	[dev_counter_a2]	, -1) as 	[dev_counter_a2]	,
isnull(	[dev_counter_scanner_send_color]	, -1) as 	[dev_counter_scanner_send_color]	,
isnull(	[dev_counter_scanner_send_black]	, -1) as 	[dev_counter_scanner_send_black]	,
isnull(	[dev_counter_fcolor_sheet_dom]	, -1) as 	[dev_counter_fcolor_sheet_dom]	,
isnull(	[dev_counter_mcolor_sheet_dom]	, -1) as 	[dev_counter_mcolor_sheet_dom]	,
isnull(	[dev_counter_fcolor_copy_charge]	, -1) as 	[dev_counter_fcolor_copy_charge]	,
isnull(	[dev_counter_black_copy_charge]	, -1) as 	[dev_counter_black_copy_charge]	,
isnull(	[dev_counter_fcolor_prt_charge]	, -1) as 	[dev_counter_fcolor_prt_charge]	,
isnull(	[dev_counter_black_print_charge]	, -1) as 	[dev_counter_black_print_charge]	,
isnull(	[dev_counter_fcolor_tot_charge]	, -1) as 	[dev_counter_fcolor_tot_charge]	,
isnull(	[dev_counter_black_total_charge]	, -1) as 	[dev_counter_black_total_charge]	,
isnull(	[dev_counter_fcolor_economy_prt]	, -1) as 	[dev_counter_fcolor_economy_prt]	,
isnull(	[dev_counter_black_economy_prt]	, -1) as 	[dev_counter_black_economy_prt]	,
isnull(	[dev_counter_fcolor_sheets_prt]	, -1) as 	[dev_counter_fcolor_sheets_prt]	,
isnull(	[dev_counter_mcolor_sheets_prt]	, -1) as 	[dev_counter_mcolor_sheets_prt]	,
isnull(	[dev_counter_fcolor_sheets_a3u]	, -1) as 	[dev_counter_fcolor_sheets_a3u]	,
isnull(	[dev_counter_mcolor_sheets_a3d]	, -1) as 	[dev_counter_mcolor_sheets_a3d]	,
isnull(	[dev_counter_color_coverage1]	, -1) as 	[dev_counter_color_coverage1]	,
isnull(	[dev_counter_color_coverage2]	, -1) as 	[dev_counter_color_coverage2]	,
isnull(	[dev_counter_color_coverage3]	, -1) as 	[dev_counter_color_coverage3]	,
isnull(	[dev_counter_state_operation]	, -1) as 	[dev_counter_state_operation]	,
isnull(	[dev_counter_state_waiting]	, -1) as 	[dev_counter_state_waiting]	,
isnull(	[dev_counter_state_preheat]	, -1) as 	[dev_counter_state_preheat]	,
isnull(	[dev_counter_state_sleep]	, -1) as 	[dev_counter_state_sleep]	,
isnull(	[dev_counter_state_offmode]	, -1) as 	[dev_counter_state_offmode]	,
isnull(	[dev_counter_state_down_sc]	, -1) as 	[dev_counter_state_down_sc]	,
isnull(	[dev_counter_state_down_pj]	, -1) as 	[dev_counter_state_down_pj]	,
isnull(	[dev_counter_state_down_sj]	, -1) as 	[dev_counter_state_down_sj]	,
isnull(	[dev_counter_state_down_sup_pm]	, -1) as 	[dev_counter_state_down_sup_pm]	
   FROM device_counter) dc
UNPIVOT
   (Value FOR [dev_counter] IN 
      (dev_counter_total_color, dev_counter_total_mono, dev_counter_copy_black, dev_counter_copy_color_full, dev_counter_copy_color_mono, dev_counter_copy_color_twin, dev_counter_printer_black, dev_counter_printer_color_full, dev_counter_printer_color_mono, dev_counter_printer_color_twin, dev_counter_printer_color_levl, dev_counter_fax_black, dev_counter_fax_color_full, dev_counter_fax_color_mono, dev_counter_fax_send, dev_counter_gpc, dev_counter_gpc_printer, dev_counter_gpc_color_full, dev_counter_a3_dlt, dev_counter_duplex, dev_counter_send_color, dev_counter_send_mono, dev_counter_fax_color_twin, dev_counter_total, dev_counter_coverage_color, dev_counter_coverage_black, dev_counter_cov_color_prt_page, dev_counter_cov_black_prt_page, dev_counter_a2, dev_counter_scanner_send_color, dev_counter_scanner_send_black, dev_counter_fcolor_sheet_dom, dev_counter_mcolor_sheet_dom, dev_counter_fcolor_copy_charge, dev_counter_black_copy_charge, dev_counter_fcolor_prt_charge, dev_counter_black_print_charge, dev_counter_fcolor_tot_charge, dev_counter_black_total_charge, dev_counter_fcolor_economy_prt, dev_counter_black_economy_prt, dev_counter_fcolor_sheets_prt, dev_counter_mcolor_sheets_prt, dev_counter_fcolor_sheets_a3u, dev_counter_mcolor_sheets_a3d, dev_counter_color_coverage1, dev_counter_color_coverage2, dev_counter_color_coverage3, dev_counter_state_operation, dev_counter_state_waiting, dev_counter_state_preheat, dev_counter_state_sleep, dev_counter_state_offmode, dev_counter_state_down_sc, dev_counter_state_down_pj, dev_counter_state_down_sj, dev_counter_state_down_sup_pm)
)AS unpvt;

New SQL way - normalized

Update	t1  
Set		[Value]= t2.[Value] 
from	device_counter_norm t1   
		cross apply (select top 1 [Value]          
					 from	device_counter_norm t2       
					 where	t1.dev_id = t2.dev_id       
						and t1.dev_counter = t2.dev_counter 
						and t1.dev_counter_date > t2.dev_counter_date       
						and t2.[Value] is not null      
					 order by t2.dev_counter_date desc      
					) as t2 
where t1.[Value] is null

Возможно, и есть более эффективные способы произвести подобное обновление в MS SQL Server, но я их не знаю.

BalinTomsk 12 фев 2016 в 01:49

Причина — отсутствие подходящих аналитических функций — last_value(… ignore nulls ) в MS SQL Server.

Спасибо за подтверждение, я пришел к тому же выводу, которое базируется на том что данные не индексированы из-за такого количестве полей, а это значит полный скан и все 57 раз, вряд ли подобная аналитическая функция помогла.

Выйгрышь реляционого подхода на подобной таблице примерно до 20 полей, после курсор быстрее, тем более на реальной базе всего 20 тыш строк требуется проапгрейтить из 3 миллионов, что для сервера совсем немного.

xtender 12 фев 2016 в 02:27

ну есть же max() over(partition by… order by ...)

minamoto 12 фев 2016 в 08:37

Он выберет максимальное значение, а нужно последнее, разве нет?

xtender 12 фев 2016 в 14:06

В оракле например можно получить то же самое через max()over() только с двумя уровнями вложенности:

-- тестовая табличка
with t(a,b) as (
select 1,10    from dual union all
select 2,20    from dual union all
select 3,null  from dual union all
select 4,5     from dual union all
select 5,null  from dual union all
select 6,null  from dual union all
select 7,1     from dual
)
select 
    v.*
   ,max(b)over(order by a range between a-last_a preceding and a-last_a preceding) test_val
from
       (
       select
          a,b
         ,last_value(b ignore nulls)over(order by a) right_val
         ,max(case when b is not null then a end)over(order by a) last_a
       from t
       ) v

Вкратце пояснение: сначала получаем последний ключ (а) с NOT NULL значением, затем просто по полученному ключу берем нужное значение.
Еще я нагуглил такое решение: sqlmag.com/t-sql/last-non-null-puzzle

зы. А аналога model в MS SQL тоже нет?

minamoto 17 фев 2016 в 18:25

Вот второй нагугленный вариант можно подразумевать под "Включить голову". Жаль, что я сам до такого варианта не додумался.

xtender way

UPDATE device_counter
SET [dev_counter_total_mono] = t1.[dev_counter_total_mono]
    ,[dev_counter_copy_black] = t1.[dev_counter_copy_black]
    ,[dev_counter_copy_color_full] = t1.[dev_counter_copy_color_full]
    ,[dev_counter_copy_color_mono] = t1.[dev_counter_copy_color_mono]
    ,[dev_counter_copy_color_twin] = t1.[dev_counter_copy_color_twin]
    ,[dev_counter_printer_black] = t1.[dev_counter_printer_black]
    ,[dev_counter_printer_color_full] = t1.[dev_counter_printer_color_full]
    ,[dev_counter_printer_color_mono] = t1.[dev_counter_printer_color_mono]
    ,[dev_counter_printer_color_twin] = t1.[dev_counter_printer_color_twin]
    ,[dev_counter_printer_color_levl] = t1.[dev_counter_printer_color_levl]
    ,[dev_counter_fax_black] = t1.[dev_counter_fax_black]
    ,[dev_counter_fax_color_full] = t1.[dev_counter_fax_color_full]
    ,[dev_counter_fax_color_mono] = t1.[dev_counter_fax_color_mono]
    ,[dev_counter_fax_send] = t1.[dev_counter_fax_send]
    ,[dev_counter_gpc] = t1.[dev_counter_gpc]
    ,[dev_counter_gpc_printer] = t1.[dev_counter_gpc_printer]
    ,[dev_counter_gpc_color_full] = t1.[dev_counter_gpc_color_full]
    ,[dev_counter_a3_dlt] = t1.[dev_counter_a3_dlt]
    ,[dev_counter_duplex] = t1.[dev_counter_duplex]
    ,[dev_counter_send_color] = t1.[dev_counter_send_color]
    ,[dev_counter_send_mono] = t1.[dev_counter_send_mono]
    ,[dev_counter_fax_color_twin] = t1.[dev_counter_fax_color_twin]
    ,[dev_counter_total] = t1.[dev_counter_total]
    ,[dev_counter_coverage_color] = t1.[dev_counter_coverage_color]
    ,[dev_counter_coverage_black] = t1.[dev_counter_coverage_black]
    ,[dev_counter_cov_color_prt_page] = t1.[dev_counter_cov_color_prt_page]
    ,[dev_counter_cov_black_prt_page] = t1.[dev_counter_cov_black_prt_page]
    ,[dev_counter_a2] = t1.[dev_counter_a2]
    ,[dev_counter_scanner_send_color] = t1.[dev_counter_scanner_send_color]
    ,[dev_counter_scanner_send_black] = t1.[dev_counter_scanner_send_black]
    ,[dev_counter_fcolor_sheet_dom] = t1.[dev_counter_fcolor_sheet_dom]
    ,[dev_counter_mcolor_sheet_dom] = t1.[dev_counter_mcolor_sheet_dom]
    ,[dev_counter_fcolor_copy_charge] = t1.[dev_counter_fcolor_copy_charge]
    ,[dev_counter_black_copy_charge] = t1.[dev_counter_black_copy_charge]
    ,[dev_counter_fcolor_prt_charge] = t1.[dev_counter_fcolor_prt_charge]
    ,[dev_counter_black_print_charge] = t1.[dev_counter_black_print_charge]
    ,[dev_counter_fcolor_tot_charge] = t1.[dev_counter_fcolor_tot_charge]
    ,[dev_counter_black_total_charge] = t1.[dev_counter_black_total_charge]
    ,[dev_counter_fcolor_economy_prt] = t1.[dev_counter_fcolor_economy_prt]
    ,[dev_counter_black_economy_prt] = t1.[dev_counter_black_economy_prt]
    ,[dev_counter_fcolor_sheets_prt] = t1.[dev_counter_fcolor_sheets_prt]
    ,[dev_counter_mcolor_sheets_prt] = t1.[dev_counter_mcolor_sheets_prt]
    ,[dev_counter_fcolor_sheets_a3u] = t1.[dev_counter_fcolor_sheets_a3u]
    ,[dev_counter_mcolor_sheets_a3d] = t1.[dev_counter_mcolor_sheets_a3d]
    ,[dev_counter_color_coverage1] = t1.[dev_counter_color_coverage1]
    ,[dev_counter_color_coverage2] = t1.[dev_counter_color_coverage2]
    ,[dev_counter_color_coverage3] = t1.[dev_counter_color_coverage3]
    ,[dev_counter_state_operation] = t1.[dev_counter_state_operation]
    ,[dev_counter_state_waiting] = t1.[dev_counter_state_waiting]
    ,[dev_counter_state_preheat] = t1.[dev_counter_state_preheat]
    ,[dev_counter_state_sleep] = t1.[dev_counter_state_sleep]
    ,[dev_counter_state_offmode] = t1.[dev_counter_state_offmode]
    ,[dev_counter_state_down_sc] = t1.[dev_counter_state_down_sc]
    ,[dev_counter_state_down_pj] = t1.[dev_counter_state_down_pj]
    ,[dev_counter_state_down_sj] = t1.[dev_counter_state_down_sj]
    ,[dev_counter_state_down_sup_pm] = t1.[dev_counter_state_down_sup_pm]
FROM (
    SELECT [dev_counter_id]
        ,[dev_counter_total_color] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_total_color] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_total_mono] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_total_mono] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_copy_black] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_copy_black] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_copy_color_full] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_copy_color_full] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_copy_color_mono] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_copy_color_mono] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_copy_color_twin] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_copy_color_twin] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_printer_black] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_printer_black] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_printer_color_full] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_printer_color_full] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_printer_color_mono] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_printer_color_mono] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_printer_color_twin] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_printer_color_twin] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_printer_color_levl] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_printer_color_levl] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_fax_black] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_fax_black] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_fax_color_full] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_fax_color_full] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_fax_color_mono] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_fax_color_mono] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_fax_send] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_fax_send] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_gpc] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_gpc] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_gpc_printer] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_gpc_printer] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_gpc_color_full] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_gpc_color_full] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_a3_dlt] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_a3_dlt] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_duplex] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_duplex] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_send_color] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_send_color] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_send_mono] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_send_mono] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_fax_color_twin] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_fax_color_twin] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_total] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_total] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_coverage_color] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_coverage_color] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_coverage_black] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_coverage_black] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_cov_color_prt_page] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_cov_color_prt_page] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_cov_black_prt_page] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_cov_black_prt_page] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_a2] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_a2] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_scanner_send_color] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_scanner_send_color] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_scanner_send_black] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_scanner_send_black] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_fcolor_sheet_dom] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_fcolor_sheet_dom] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_mcolor_sheet_dom] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_mcolor_sheet_dom] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_fcolor_copy_charge] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_fcolor_copy_charge] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_black_copy_charge] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_black_copy_charge] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_fcolor_prt_charge] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_fcolor_prt_charge] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_black_print_charge] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_black_print_charge] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_fcolor_tot_charge] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_fcolor_tot_charge] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_black_total_charge] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_black_total_charge] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_fcolor_economy_prt] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_fcolor_economy_prt] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_black_economy_prt] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_black_economy_prt] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_fcolor_sheets_prt] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_fcolor_sheets_prt] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_mcolor_sheets_prt] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_mcolor_sheets_prt] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_fcolor_sheets_a3u] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_fcolor_sheets_a3u] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_mcolor_sheets_a3d] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_mcolor_sheets_a3d] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_color_coverage1] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_color_coverage1] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_color_coverage2] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_color_coverage2] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_color_coverage3] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_color_coverage3] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_state_operation] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_state_operation] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_state_waiting] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_state_waiting] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_state_preheat] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_state_preheat] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_state_sleep] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_state_sleep] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_state_offmode] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_state_offmode] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_state_down_sc] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_state_down_sc] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_state_down_pj] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_state_down_pj] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_state_down_sj] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_state_down_sj] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
        ,[dev_counter_state_down_sup_pm] = CAST(SUBSTRING(MAX(CAST([dev_counter_id] AS BINARY (4)) + CAST([dev_counter_state_down_sup_pm] AS BINARY (4))) OVER (
                    ORDER BY [dev_counter_id] ROWS UNBOUNDED PRECEDING
                    ), 5, 4) AS INT)
    FROM dbo.device_counter
    ) AS t1
INNER JOIN device_counter ON t1.[dev_counter_id] = device_counter.[dev_counter_id];

Cursor way: 673
New SQL way: 13286
xtender way: 210

У этого решения есть только один недостаток — требуется наличие оконных функций, и, как следствие, версия SQL Server'а не ниже 2012.

model я не знаю, поэтому не знаю, есть ли у него аналоги.

BalinTomsk 17 фев 2016 в 20:02

Спасибо хорошее решение, но к сожаления у мы все еше поддерживаем 2008.

xtender 18 фев 2016 в 02:00

Эти два работают и на 2008:
http://sqlfiddle.com/#!3/61592/2/0
http://sqlfiddle.com/#!3/61592/3

minamoto 21 фев 2016 в 13:03

Вот за это я не люблю sqlfiddle.com:
1) Проблемы с работой — я примерно с полчаса пытался открыть эти примеры, сайт стабильно показывал ошибку
2) Проблемы с фичами разных версий:
LAG совершенно точно был введен в SQL Server 2012 (я уже работал с SQL Server, когда эта версия появилась, но для уверенности и самопроверки — https://msdn.microsoft.com/en-us/library/hh231256.aspx)
Поэтому второй вариант все же на 2008 не будет работать. Реализую его просто для сравнения.
А вот третий — должен (точно проверить сейчас нет возможности).

репро (без создания таблиц) часть 1

set nocount on
declare @startdate datetime = getdate();
begin tran
begin
update  device_counter
set     [dev_counter_total_color] = t.[new_dev_counter_total_color]
       ,[dev_counter_total_mono] = t.[new_dev_counter_total_mono]
       ,[dev_counter_copy_black] = t.[new_dev_counter_copy_black]
       ,[dev_counter_copy_color_full] = t.[new_dev_counter_copy_color_full]
       ,[dev_counter_copy_color_mono] = t.[new_dev_counter_copy_color_mono]
       ,[dev_counter_copy_color_twin] = t.[new_dev_counter_copy_color_twin]
       ,[dev_counter_printer_black] = t.[new_dev_counter_printer_black]
       ,[dev_counter_printer_color_full] = t.[new_dev_counter_printer_color_full]
       ,[dev_counter_printer_color_mono] = t.[new_dev_counter_printer_color_mono]
       ,[dev_counter_printer_color_twin] = t.[new_dev_counter_printer_color_twin]
       ,[dev_counter_printer_color_levl] = t.[new_dev_counter_printer_color_levl]
       ,[dev_counter_fax_black] = t.[new_dev_counter_fax_black]
       ,[dev_counter_fax_color_full] = t.[new_dev_counter_fax_color_full]
       ,[dev_counter_fax_color_mono] = t.[new_dev_counter_fax_color_mono]
       ,[dev_counter_fax_send] = t.[new_dev_counter_fax_send]
       ,[dev_counter_gpc] = t.[new_dev_counter_gpc]
       ,[dev_counter_gpc_printer] = t.[new_dev_counter_gpc_printer]
       ,[dev_counter_gpc_color_full] = t.[new_dev_counter_gpc_color_full]
       ,[dev_counter_a3_dlt] = t.[new_dev_counter_a3_dlt]
       ,[dev_counter_duplex] = t.[new_dev_counter_duplex]
       ,[dev_counter_send_color] = t.[new_dev_counter_send_color]
       ,[dev_counter_send_mono] = t.[new_dev_counter_send_mono]
       ,[dev_counter_fax_color_twin] = t.[new_dev_counter_fax_color_twin]
       ,[dev_counter_total] = t.[new_dev_counter_total]
       ,[dev_counter_coverage_color] = t.[new_dev_counter_coverage_color]
       ,[dev_counter_coverage_black] = t.[new_dev_counter_coverage_black]
       ,[dev_counter_cov_color_prt_page] = t.[new_dev_counter_cov_color_prt_page]
       ,[dev_counter_cov_black_prt_page] = t.[new_dev_counter_cov_black_prt_page]
       ,[dev_counter_a2] = t.[new_dev_counter_a2]
       ,[dev_counter_scanner_send_color] = t.[new_dev_counter_scanner_send_color]
       ,[dev_counter_scanner_send_black] = t.[new_dev_counter_scanner_send_black]
       ,[dev_counter_fcolor_sheet_dom] = t.[new_dev_counter_fcolor_sheet_dom]
       ,[dev_counter_mcolor_sheet_dom] = t.[new_dev_counter_mcolor_sheet_dom]
       ,[dev_counter_fcolor_copy_charge] = t.[new_dev_counter_fcolor_copy_charge]
       ,[dev_counter_black_copy_charge] = t.[new_dev_counter_black_copy_charge]
       ,[dev_counter_fcolor_prt_charge] = t.[new_dev_counter_fcolor_prt_charge]
       ,[dev_counter_black_print_charge] = t.[new_dev_counter_black_print_charge]
       ,[dev_counter_fcolor_tot_charge] = t.[new_dev_counter_fcolor_tot_charge]
       ,[dev_counter_black_total_charge] = t.[new_dev_counter_black_total_charge]
       ,[dev_counter_fcolor_economy_prt] = t.[new_dev_counter_fcolor_economy_prt]
       ,[dev_counter_black_economy_prt] = t.[new_dev_counter_black_economy_prt]
       ,[dev_counter_fcolor_sheets_prt] = t.[new_dev_counter_fcolor_sheets_prt]
       ,[dev_counter_mcolor_sheets_prt] = t.[new_dev_counter_mcolor_sheets_prt]
       ,[dev_counter_fcolor_sheets_a3u] = t.[new_dev_counter_fcolor_sheets_a3u]
       ,[dev_counter_mcolor_sheets_a3d] = t.[new_dev_counter_mcolor_sheets_a3d]
       ,[dev_counter_color_coverage1] = t.[new_dev_counter_color_coverage1]
       ,[dev_counter_color_coverage2] = t.[new_dev_counter_color_coverage2]
       ,[dev_counter_color_coverage3] = t.[new_dev_counter_color_coverage3]
       ,[dev_counter_state_operation] = t.[new_dev_counter_state_operation]
       ,[dev_counter_state_waiting] = t.[new_dev_counter_state_waiting]
       ,[dev_counter_state_preheat] = t.[new_dev_counter_state_preheat]
       ,[dev_counter_state_sleep] = t.[new_dev_counter_state_sleep]
       ,[dev_counter_state_offmode] = t.[new_dev_counter_state_offmode]
       ,[dev_counter_state_down_sc] = t.[new_dev_counter_state_down_sc]
       ,[dev_counter_state_down_pj] = t.[new_dev_counter_state_down_pj]
       ,[dev_counter_state_down_sj] = t.[new_dev_counter_state_down_sj]
       ,[dev_counter_state_down_sup_pm] = t.[new_dev_counter_state_down_sup_pm]
from (  select  v.dev_counter_id
             ,  lag([dev_counter_total_color],[dev_counter_id]-[last_dev_counter_total_color_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_total_color]
             ,  lag([dev_counter_total_mono],[dev_counter_id]-[last_dev_counter_total_mono_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_total_mono]
             ,  lag([dev_counter_copy_black],[dev_counter_id]-[last_dev_counter_copy_black_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_copy_black]
             ,  lag([dev_counter_copy_color_full],[dev_counter_id]-[last_dev_counter_copy_color_full_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_copy_color_full]
             ,  lag([dev_counter_copy_color_mono],[dev_counter_id]-[last_dev_counter_copy_color_mono_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_copy_color_mono]
             ,  lag([dev_counter_copy_color_twin],[dev_counter_id]-[last_dev_counter_copy_color_twin_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_copy_color_twin]
             ,  lag([dev_counter_printer_black],[dev_counter_id]-[last_dev_counter_printer_black_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_printer_black]
             ,  lag([dev_counter_printer_color_full],[dev_counter_id]-[last_dev_counter_printer_color_full_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_printer_color_full]
             ,  lag([dev_counter_printer_color_mono],[dev_counter_id]-[last_dev_counter_printer_color_mono_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_printer_color_mono]
             ,  lag([dev_counter_printer_color_twin],[dev_counter_id]-[last_dev_counter_printer_color_twin_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_printer_color_twin]
             ,  lag([dev_counter_printer_color_levl],[dev_counter_id]-[last_dev_counter_printer_color_levl_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_printer_color_levl]
             ,  lag([dev_counter_fax_black],[dev_counter_id]-[last_dev_counter_fax_black_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_fax_black]
             ,  lag([dev_counter_fax_color_full],[dev_counter_id]-[last_dev_counter_fax_color_full_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_fax_color_full]
             ,  lag([dev_counter_fax_color_mono],[dev_counter_id]-[last_dev_counter_fax_color_mono_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_fax_color_mono]
             ,  lag([dev_counter_fax_send],[dev_counter_id]-[last_dev_counter_fax_send_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_fax_send]
             ,  lag([dev_counter_gpc],[dev_counter_id]-[last_dev_counter_gpc_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_gpc]
             ,  lag([dev_counter_gpc_printer],[dev_counter_id]-[last_dev_counter_gpc_printer_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_gpc_printer]
             ,  lag([dev_counter_gpc_color_full],[dev_counter_id]-[last_dev_counter_gpc_color_full_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_gpc_color_full]
             ,  lag([dev_counter_a3_dlt],[dev_counter_id]-[last_dev_counter_a3_dlt_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_a3_dlt]
             ,  lag([dev_counter_duplex],[dev_counter_id]-[last_dev_counter_duplex_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_duplex]
             ,  lag([dev_counter_send_color],[dev_counter_id]-[last_dev_counter_send_color_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_send_color]
             ,  lag([dev_counter_send_mono],[dev_counter_id]-[last_dev_counter_send_mono_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_send_mono]
             ,  lag([dev_counter_fax_color_twin],[dev_counter_id]-[last_dev_counter_fax_color_twin_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_fax_color_twin]
             ,  lag([dev_counter_total],[dev_counter_id]-[last_dev_counter_total_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_total]
             ,  lag([dev_counter_coverage_color],[dev_counter_id]-[last_dev_counter_coverage_color_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_coverage_color]
             ,  lag([dev_counter_coverage_black],[dev_counter_id]-[last_dev_counter_coverage_black_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_coverage_black]
             ,  lag([dev_counter_cov_color_prt_page],[dev_counter_id]-[last_dev_counter_cov_color_prt_page_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_cov_color_prt_page]
             ,  lag([dev_counter_cov_black_prt_page],[dev_counter_id]-[last_dev_counter_cov_black_prt_page_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_cov_black_prt_page]
             ,  lag([dev_counter_a2],[dev_counter_id]-[last_dev_counter_a2_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_a2]
             ,  lag([dev_counter_scanner_send_color],[dev_counter_id]-[last_dev_counter_scanner_send_color_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_scanner_send_color]
             ,  lag([dev_counter_scanner_send_black],[dev_counter_id]-[last_dev_counter_scanner_send_black_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_scanner_send_black]
             ,  lag([dev_counter_fcolor_sheet_dom],[dev_counter_id]-[last_dev_counter_fcolor_sheet_dom_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_fcolor_sheet_dom]
             ,  lag([dev_counter_mcolor_sheet_dom],[dev_counter_id]-[last_dev_counter_mcolor_sheet_dom_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_mcolor_sheet_dom]
             ,  lag([dev_counter_fcolor_copy_charge],[dev_counter_id]-[last_dev_counter_fcolor_copy_charge_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_fcolor_copy_charge]
             ,  lag([dev_counter_black_copy_charge],[dev_counter_id]-[last_dev_counter_black_copy_charge_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_black_copy_charge]
             ,  lag([dev_counter_fcolor_prt_charge],[dev_counter_id]-[last_dev_counter_fcolor_prt_charge_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_fcolor_prt_charge]
             ,  lag([dev_counter_black_print_charge],[dev_counter_id]-[last_dev_counter_black_print_charge_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_black_print_charge]
             ,  lag([dev_counter_fcolor_tot_charge],[dev_counter_id]-[last_dev_counter_fcolor_tot_charge_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_fcolor_tot_charge]
             ,  lag([dev_counter_black_total_charge],[dev_counter_id]-[last_dev_counter_black_total_charge_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_black_total_charge]
             ,  lag([dev_counter_fcolor_economy_prt],[dev_counter_id]-[last_dev_counter_fcolor_economy_prt_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_fcolor_economy_prt]
             ,  lag([dev_counter_black_economy_prt],[dev_counter_id]-[last_dev_counter_black_economy_prt_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_black_economy_prt]
             ,  lag([dev_counter_fcolor_sheets_prt],[dev_counter_id]-[last_dev_counter_fcolor_sheets_prt_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_fcolor_sheets_prt]
             ,  lag([dev_counter_mcolor_sheets_prt],[dev_counter_id]-[last_dev_counter_mcolor_sheets_prt_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_mcolor_sheets_prt]
             ,  lag([dev_counter_fcolor_sheets_a3u],[dev_counter_id]-[last_dev_counter_fcolor_sheets_a3u_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_fcolor_sheets_a3u]
             ,  lag([dev_counter_mcolor_sheets_a3d],[dev_counter_id]-[last_dev_counter_mcolor_sheets_a3d_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_mcolor_sheets_a3d]
             ,  lag([dev_counter_color_coverage1],[dev_counter_id]-[last_dev_counter_color_coverage1_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_color_coverage1]
             ,  lag([dev_counter_color_coverage2],[dev_counter_id]-[last_dev_counter_color_coverage2_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_color_coverage2]
             ,  lag([dev_counter_color_coverage3],[dev_counter_id]-[last_dev_counter_color_coverage3_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_color_coverage3]
             ,  lag([dev_counter_state_operation],[dev_counter_id]-[last_dev_counter_state_operation_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_state_operation]
             ,  lag([dev_counter_state_waiting],[dev_counter_id]-[last_dev_counter_state_waiting_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_state_waiting]
             ,  lag([dev_counter_state_preheat],[dev_counter_id]-[last_dev_counter_state_preheat_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_state_preheat]
             ,  lag([dev_counter_state_sleep],[dev_counter_id]-[last_dev_counter_state_sleep_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_state_sleep]
             ,  lag([dev_counter_state_offmode],[dev_counter_id]-[last_dev_counter_state_offmode_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_state_offmode]
             ,  lag([dev_counter_state_down_sc],[dev_counter_id]-[last_dev_counter_state_down_sc_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_state_down_sc]
             ,  lag([dev_counter_state_down_pj],[dev_counter_id]-[last_dev_counter_state_down_pj_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_state_down_pj]
             ,  lag([dev_counter_state_down_sj],[dev_counter_id]-[last_dev_counter_state_down_sj_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_state_down_sj]
             ,  lag([dev_counter_state_down_sup_pm],[dev_counter_id]-[last_dev_counter_state_down_sup_pm_id])over(partition by dev_id order by [dev_counter_id]) [new_dev_counter_state_down_sup_pm]
        from (  select  [dev_counter_id], dev_id
                      ,  [dev_counter_total_color] 
                      ,  max(case when [dev_counter_total_color] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_total_color_id]
                      ,  [dev_counter_total_mono] 
                      ,  max(case when [dev_counter_total_mono] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_total_mono_id]
                      ,  [dev_counter_copy_black] 
                      ,  max(case when [dev_counter_copy_black] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_copy_black_id]
                      ,  [dev_counter_copy_color_full] 
                      ,  max(case when [dev_counter_copy_color_full] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_copy_color_full_id]
                      ,  [dev_counter_copy_color_mono] 
                      ,  max(case when [dev_counter_copy_color_mono] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_copy_color_mono_id]
                      ,  [dev_counter_copy_color_twin] 
                      ,  max(case when [dev_counter_copy_color_twin] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_copy_color_twin_id]
                      ,  [dev_counter_printer_black] 
                      ,  max(case when [dev_counter_printer_black] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_printer_black_id]
                      ,  [dev_counter_printer_color_full] 
                      ,  max(case when [dev_counter_printer_color_full] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_printer_color_full_id]
                      ,  [dev_counter_printer_color_mono] 
                      ,  max(case when [dev_counter_printer_color_mono] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_printer_color_mono_id]
                      ,  [dev_counter_printer_color_twin] 
                      ,  max(case when [dev_counter_printer_color_twin] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_printer_color_twin_id]
                      ,  [dev_counter_printer_color_levl] 
                      ,  max(case when [dev_counter_printer_color_levl] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_printer_color_levl_id]
                      ,  [dev_counter_fax_black] 
                      ,  max(case when [dev_counter_fax_black] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_fax_black_id]
                      ,  [dev_counter_fax_color_full] 
                      ,  max(case when [dev_counter_fax_color_full] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_fax_color_full_id]
                      ,  [dev_counter_fax_color_mono] 
                      ,  max(case when [dev_counter_fax_color_mono] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_fax_color_mono_id]
                      ,  [dev_counter_fax_send] 
                      ,  max(case when [dev_counter_fax_send] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_fax_send_id]
                      ,  [dev_counter_gpc] 
                      ,  max(case when [dev_counter_gpc] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_gpc_id]
                      ,  [dev_counter_gpc_printer] 
                      ,  max(case when [dev_counter_gpc_printer] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_gpc_printer_id]
                      ,  [dev_counter_gpc_color_full] 
                      ,  max(case when [dev_counter_gpc_color_full] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_gpc_color_full_id]
                      ,  [dev_counter_a3_dlt] 
                      ,  max(case when [dev_counter_a3_dlt] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_a3_dlt_id]
                      ,  [dev_counter_duplex] 
                      ,  max(case when [dev_counter_duplex] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_duplex_id]
                      ,  [dev_counter_send_color] 
                      ,  max(case when [dev_counter_send_color] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_send_color_id]
                      ,  [dev_counter_send_mono] 
                      ,  max(case when [dev_counter_send_mono] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_send_mono_id]
                      ,  [dev_counter_fax_color_twin] 
                      ,  max(case when [dev_counter_fax_color_twin] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_fax_color_twin_id]
                      ,  [dev_counter_total] 
                      ,  max(case when [dev_counter_total] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_total_id]
                      ,  [dev_counter_coverage_color] 
                      ,  max(case when [dev_counter_coverage_color] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_coverage_color_id]
                      ,  [dev_counter_coverage_black] 
                      ,  max(case when [dev_counter_coverage_black] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_coverage_black_id]
                      ,  [dev_counter_cov_color_prt_page] 
                      ,  max(case when [dev_counter_cov_color_prt_page] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_cov_color_prt_page_id]
                      ,  [dev_counter_cov_black_prt_page] 
                      ,  max(case when [dev_counter_cov_black_prt_page] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_cov_black_prt_page_id]
                      ,  [dev_counter_a2] 
                      ,  max(case when [dev_counter_a2] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_a2_id]
                      ,  [dev_counter_scanner_send_color] 
                      ,  max(case when [dev_counter_scanner_send_color] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_scanner_send_color_id]
                      ,  [dev_counter_scanner_send_black] 
                      ,  max(case when [dev_counter_scanner_send_black] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_scanner_send_black_id]
                      ,  [dev_counter_fcolor_sheet_dom] 
                      ,  max(case when [dev_counter_fcolor_sheet_dom] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_sheet_dom_id]
                      ,  [dev_counter_mcolor_sheet_dom] 
                      ,  max(case when [dev_counter_mcolor_sheet_dom] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_mcolor_sheet_dom_id]
                      ,  [dev_counter_fcolor_copy_charge] 
                      ,  max(case when [dev_counter_fcolor_copy_charge] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_copy_charge_id]
                      ,  [dev_counter_black_copy_charge] 
                      ,  max(case when [dev_counter_black_copy_charge] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_black_copy_charge_id]
                      ,  [dev_counter_fcolor_prt_charge] 
                      ,  max(case when [dev_counter_fcolor_prt_charge] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_prt_charge_id]
                      ,  [dev_counter_black_print_charge] 
                      ,  max(case when [dev_counter_black_print_charge] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_black_print_charge_id]
                      ,  [dev_counter_fcolor_tot_charge] 
                      ,  max(case when [dev_counter_fcolor_tot_charge] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_tot_charge_id]
                      ,  [dev_counter_black_total_charge] 
                      ,  max(case when [dev_counter_black_total_charge] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_black_total_charge_id]
                      ,  [dev_counter_fcolor_economy_prt] 
                      ,  max(case when [dev_counter_fcolor_economy_prt] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_economy_prt_id]
                      ,  [dev_counter_black_economy_prt] 
                      ,  max(case when [dev_counter_black_economy_prt] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_black_economy_prt_id]
                      ,  [dev_counter_fcolor_sheets_prt] 
                      ,  max(case when [dev_counter_fcolor_sheets_prt] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_sheets_prt_id]
                      ,  [dev_counter_mcolor_sheets_prt] 
                      ,  max(case when [dev_counter_mcolor_sheets_prt] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_mcolor_sheets_prt_id]
                      ,  [dev_counter_fcolor_sheets_a3u] 
                      ,  max(case when [dev_counter_fcolor_sheets_a3u] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_sheets_a3u_id]
                      ,  [dev_counter_mcolor_sheets_a3d] 
                      ,  max(case when [dev_counter_mcolor_sheets_a3d] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_mcolor_sheets_a3d_id]
                      ,  [dev_counter_color_coverage1] 
                      ,  max(case when [dev_counter_color_coverage1] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_color_coverage1_id]
                      ,  [dev_counter_color_coverage2] 
                      ,  max(case when [dev_counter_color_coverage2] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_color_coverage2_id]
                      ,  [dev_counter_color_coverage3] 
                      ,  max(case when [dev_counter_color_coverage3] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_color_coverage3_id]
                      ,  [dev_counter_state_operation] 
                      ,  max(case when [dev_counter_state_operation] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_operation_id]
                      ,  [dev_counter_state_waiting] 
                      ,  max(case when [dev_counter_state_waiting] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_waiting_id]
                      ,  [dev_counter_state_preheat] 
                      ,  max(case when [dev_counter_state_preheat] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_preheat_id]
                      ,  [dev_counter_state_sleep] 
                      ,  max(case when [dev_counter_state_sleep] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_sleep_id]
                      ,  [dev_counter_state_offmode] 
                      ,  max(case when [dev_counter_state_offmode] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_offmode_id]
                      ,  [dev_counter_state_down_sc] 
                      ,  max(case when [dev_counter_state_down_sc] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_down_sc_id]
                      ,  [dev_counter_state_down_pj] 
                      ,  max(case when [dev_counter_state_down_pj] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_down_pj_id]
                      ,  [dev_counter_state_down_sj] 
                      ,  max(case when [dev_counter_state_down_sj] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_down_sj_id]
                      ,  [dev_counter_state_down_sup_pm] 
                      ,  max(case when [dev_counter_state_down_sup_pm] is not null then [dev_counter_id] end)over(partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_down_sup_pm_id]
                from device_counter
            ) v
      ) as t
inner join device_counter
        on t.dev_counter_id = device_counter.dev_counter_id
end
print 'xtender 2nd way: ' + cast(datediff(ms, @startdate, getdate()) as nvarchar(max));
rollback tran
go

minamoto 21 фев 2016 в 13:04

репро (без создания таблиц) часть 2

set nocount on
declare @startdate datetime = getdate();
begin tran
begin
update  device_counter
set     [dev_counter_total_color] = t.[new_dev_counter_total_color]
       ,[dev_counter_total_mono] = t.[new_dev_counter_total_mono]
       ,[dev_counter_copy_black] = t.[new_dev_counter_copy_black]
       ,[dev_counter_copy_color_full] = t.[new_dev_counter_copy_color_full]
       ,[dev_counter_copy_color_mono] = t.[new_dev_counter_copy_color_mono]
       ,[dev_counter_copy_color_twin] = t.[new_dev_counter_copy_color_twin]
       ,[dev_counter_printer_black] = t.[new_dev_counter_printer_black]
       ,[dev_counter_printer_color_full] = t.[new_dev_counter_printer_color_full]
       ,[dev_counter_printer_color_mono] = t.[new_dev_counter_printer_color_mono]
       ,[dev_counter_printer_color_twin] = t.[new_dev_counter_printer_color_twin]
       ,[dev_counter_printer_color_levl] = t.[new_dev_counter_printer_color_levl]
       ,[dev_counter_fax_black] = t.[new_dev_counter_fax_black]
       ,[dev_counter_fax_color_full] = t.[new_dev_counter_fax_color_full]
       ,[dev_counter_fax_color_mono] = t.[new_dev_counter_fax_color_mono]
       ,[dev_counter_fax_send] = t.[new_dev_counter_fax_send]
       ,[dev_counter_gpc] = t.[new_dev_counter_gpc]
       ,[dev_counter_gpc_printer] = t.[new_dev_counter_gpc_printer]
       ,[dev_counter_gpc_color_full] = t.[new_dev_counter_gpc_color_full]
       ,[dev_counter_a3_dlt] = t.[new_dev_counter_a3_dlt]
       ,[dev_counter_duplex] = t.[new_dev_counter_duplex]
       ,[dev_counter_send_color] = t.[new_dev_counter_send_color]
       ,[dev_counter_send_mono] = t.[new_dev_counter_send_mono]
       ,[dev_counter_fax_color_twin] = t.[new_dev_counter_fax_color_twin]
       ,[dev_counter_total] = t.[new_dev_counter_total]
       ,[dev_counter_coverage_color] = t.[new_dev_counter_coverage_color]
       ,[dev_counter_coverage_black] = t.[new_dev_counter_coverage_black]
       ,[dev_counter_cov_color_prt_page] = t.[new_dev_counter_cov_color_prt_page]
       ,[dev_counter_cov_black_prt_page] = t.[new_dev_counter_cov_black_prt_page]
       ,[dev_counter_a2] = t.[new_dev_counter_a2]
       ,[dev_counter_scanner_send_color] = t.[new_dev_counter_scanner_send_color]
       ,[dev_counter_scanner_send_black] = t.[new_dev_counter_scanner_send_black]
       ,[dev_counter_fcolor_sheet_dom] = t.[new_dev_counter_fcolor_sheet_dom]
       ,[dev_counter_mcolor_sheet_dom] = t.[new_dev_counter_mcolor_sheet_dom]
       ,[dev_counter_fcolor_copy_charge] = t.[new_dev_counter_fcolor_copy_charge]
       ,[dev_counter_black_copy_charge] = t.[new_dev_counter_black_copy_charge]
       ,[dev_counter_fcolor_prt_charge] = t.[new_dev_counter_fcolor_prt_charge]
       ,[dev_counter_black_print_charge] = t.[new_dev_counter_black_print_charge]
       ,[dev_counter_fcolor_tot_charge] = t.[new_dev_counter_fcolor_tot_charge]
       ,[dev_counter_black_total_charge] = t.[new_dev_counter_black_total_charge]
       ,[dev_counter_fcolor_economy_prt] = t.[new_dev_counter_fcolor_economy_prt]
       ,[dev_counter_black_economy_prt] = t.[new_dev_counter_black_economy_prt]
       ,[dev_counter_fcolor_sheets_prt] = t.[new_dev_counter_fcolor_sheets_prt]
       ,[dev_counter_mcolor_sheets_prt] = t.[new_dev_counter_mcolor_sheets_prt]
       ,[dev_counter_fcolor_sheets_a3u] = t.[new_dev_counter_fcolor_sheets_a3u]
       ,[dev_counter_mcolor_sheets_a3d] = t.[new_dev_counter_mcolor_sheets_a3d]
       ,[dev_counter_color_coverage1] = t.[new_dev_counter_color_coverage1]
       ,[dev_counter_color_coverage2] = t.[new_dev_counter_color_coverage2]
       ,[dev_counter_color_coverage3] = t.[new_dev_counter_color_coverage3]
       ,[dev_counter_state_operation] = t.[new_dev_counter_state_operation]
       ,[dev_counter_state_waiting] = t.[new_dev_counter_state_waiting]
       ,[dev_counter_state_preheat] = t.[new_dev_counter_state_preheat]
       ,[dev_counter_state_sleep] = t.[new_dev_counter_state_sleep]
       ,[dev_counter_state_offmode] = t.[new_dev_counter_state_offmode]
       ,[dev_counter_state_down_sc] = t.[new_dev_counter_state_down_sc]
       ,[dev_counter_state_down_pj] = t.[new_dev_counter_state_down_pj]
       ,[dev_counter_state_down_sj] = t.[new_dev_counter_state_down_sj]
       ,[dev_counter_state_down_sup_pm] = t.[new_dev_counter_state_down_sup_pm]
from (  select  v.dev_counter_id
            ,   max([dev_counter_total_color]) over(partition by dev_id, [last_dev_counter_total_color_id]) as [new_dev_counter_total_color]
            ,   max([dev_counter_total_mono]) over(partition by dev_id, [last_dev_counter_total_mono_id]) as [new_dev_counter_total_mono]
            ,   max([dev_counter_copy_black]) over(partition by dev_id, [last_dev_counter_copy_black_id]) as [new_dev_counter_copy_black]
            ,   max([dev_counter_copy_color_full]) over(partition by dev_id, [last_dev_counter_copy_color_full_id]) as [new_dev_counter_copy_color_full]
            ,   max([dev_counter_copy_color_mono]) over(partition by dev_id, [last_dev_counter_copy_color_mono_id]) as [new_dev_counter_copy_color_mono]
            ,   max([dev_counter_copy_color_twin]) over(partition by dev_id, [last_dev_counter_copy_color_twin_id]) as [new_dev_counter_copy_color_twin]
            ,   max([dev_counter_printer_black]) over(partition by dev_id, [last_dev_counter_printer_black_id]) as [new_dev_counter_printer_black]
            ,   max([dev_counter_printer_color_full]) over(partition by dev_id, [last_dev_counter_printer_color_full_id]) as [new_dev_counter_printer_color_full]
            ,   max([dev_counter_printer_color_mono]) over(partition by dev_id, [last_dev_counter_printer_color_mono_id]) as [new_dev_counter_printer_color_mono]
            ,   max([dev_counter_printer_color_twin]) over(partition by dev_id, [last_dev_counter_printer_color_twin_id]) as [new_dev_counter_printer_color_twin]
            ,   max([dev_counter_printer_color_levl]) over(partition by dev_id, [last_dev_counter_printer_color_levl_id]) as [new_dev_counter_printer_color_levl]
            ,   max([dev_counter_fax_black]) over(partition by dev_id, [last_dev_counter_fax_black_id]) as [new_dev_counter_fax_black]
            ,   max([dev_counter_fax_color_full]) over(partition by dev_id, [last_dev_counter_fax_color_full_id]) as [new_dev_counter_fax_color_full]
            ,   max([dev_counter_fax_color_mono]) over(partition by dev_id, [last_dev_counter_fax_color_mono_id]) as [new_dev_counter_fax_color_mono]
            ,   max([dev_counter_fax_send]) over(partition by dev_id, [last_dev_counter_fax_send_id]) as [new_dev_counter_fax_send]
            ,   max([dev_counter_gpc]) over(partition by dev_id, [last_dev_counter_gpc_id]) as [new_dev_counter_gpc]
            ,   max([dev_counter_gpc_printer]) over(partition by dev_id, [last_dev_counter_gpc_printer_id]) as [new_dev_counter_gpc_printer]
            ,   max([dev_counter_gpc_color_full]) over(partition by dev_id, [last_dev_counter_gpc_color_full_id]) as [new_dev_counter_gpc_color_full]
            ,   max([dev_counter_a3_dlt]) over(partition by dev_id, [last_dev_counter_a3_dlt_id]) as [new_dev_counter_a3_dlt]
            ,   max([dev_counter_duplex]) over(partition by dev_id, [last_dev_counter_duplex_id]) as [new_dev_counter_duplex]
            ,   max([dev_counter_send_color]) over(partition by dev_id, [last_dev_counter_send_color_id]) as [new_dev_counter_send_color]
            ,   max([dev_counter_send_mono]) over(partition by dev_id, [last_dev_counter_send_mono_id]) as [new_dev_counter_send_mono]
            ,   max([dev_counter_fax_color_twin]) over(partition by dev_id, [last_dev_counter_fax_color_twin_id]) as [new_dev_counter_fax_color_twin]
            ,   max([dev_counter_total]) over(partition by dev_id, [last_dev_counter_total_id]) as [new_dev_counter_total]
            ,   max([dev_counter_coverage_color]) over(partition by dev_id, [last_dev_counter_coverage_color_id]) as [new_dev_counter_coverage_color]
            ,   max([dev_counter_coverage_black]) over(partition by dev_id, [last_dev_counter_coverage_black_id]) as [new_dev_counter_coverage_black]
            ,   max([dev_counter_cov_color_prt_page]) over(partition by dev_id, [last_dev_counter_cov_color_prt_page_id]) as [new_dev_counter_cov_color_prt_page]
            ,   max([dev_counter_cov_black_prt_page]) over(partition by dev_id, [last_dev_counter_cov_black_prt_page_id]) as [new_dev_counter_cov_black_prt_page]
            ,   max([dev_counter_a2]) over(partition by dev_id, [last_dev_counter_a2_id]) as [new_dev_counter_a2]
            ,   max([dev_counter_scanner_send_color]) over(partition by dev_id, [last_dev_counter_scanner_send_color_id]) as [new_dev_counter_scanner_send_color]
            ,   max([dev_counter_scanner_send_black]) over(partition by dev_id, [last_dev_counter_scanner_send_black_id]) as [new_dev_counter_scanner_send_black]
            ,   max([dev_counter_fcolor_sheet_dom]) over(partition by dev_id, [last_dev_counter_fcolor_sheet_dom_id]) as [new_dev_counter_fcolor_sheet_dom]
            ,   max([dev_counter_mcolor_sheet_dom]) over(partition by dev_id, [last_dev_counter_mcolor_sheet_dom_id]) as [new_dev_counter_mcolor_sheet_dom]
            ,   max([dev_counter_fcolor_copy_charge]) over(partition by dev_id, [last_dev_counter_fcolor_copy_charge_id]) as [new_dev_counter_fcolor_copy_charge]
            ,   max([dev_counter_black_copy_charge]) over(partition by dev_id, [last_dev_counter_black_copy_charge_id]) as [new_dev_counter_black_copy_charge]
            ,   max([dev_counter_fcolor_prt_charge]) over(partition by dev_id, [last_dev_counter_fcolor_prt_charge_id]) as [new_dev_counter_fcolor_prt_charge]
            ,   max([dev_counter_black_print_charge]) over(partition by dev_id, [last_dev_counter_black_print_charge_id]) as [new_dev_counter_black_print_charge]
            ,   max([dev_counter_fcolor_tot_charge]) over(partition by dev_id, [last_dev_counter_fcolor_tot_charge_id]) as [new_dev_counter_fcolor_tot_charge]
            ,   max([dev_counter_black_total_charge]) over(partition by dev_id, [last_dev_counter_black_total_charge_id]) as [new_dev_counter_black_total_charge]
            ,   max([dev_counter_fcolor_economy_prt]) over(partition by dev_id, [last_dev_counter_fcolor_economy_prt_id]) as [new_dev_counter_fcolor_economy_prt]
            ,   max([dev_counter_black_economy_prt]) over(partition by dev_id, [last_dev_counter_black_economy_prt_id]) as [new_dev_counter_black_economy_prt]
            ,   max([dev_counter_fcolor_sheets_prt]) over(partition by dev_id, [last_dev_counter_fcolor_sheets_prt_id]) as [new_dev_counter_fcolor_sheets_prt]
            ,   max([dev_counter_mcolor_sheets_prt]) over(partition by dev_id, [last_dev_counter_mcolor_sheets_prt_id]) as [new_dev_counter_mcolor_sheets_prt]
            ,   max([dev_counter_fcolor_sheets_a3u]) over(partition by dev_id, [last_dev_counter_fcolor_sheets_a3u_id]) as [new_dev_counter_fcolor_sheets_a3u]
            ,   max([dev_counter_mcolor_sheets_a3d]) over(partition by dev_id, [last_dev_counter_mcolor_sheets_a3d_id]) as [new_dev_counter_mcolor_sheets_a3d]
            ,   max([dev_counter_color_coverage1]) over(partition by dev_id, [last_dev_counter_color_coverage1_id]) as [new_dev_counter_color_coverage1]
            ,   max([dev_counter_color_coverage2]) over(partition by dev_id, [last_dev_counter_color_coverage2_id]) as [new_dev_counter_color_coverage2]
            ,   max([dev_counter_color_coverage3]) over(partition by dev_id, [last_dev_counter_color_coverage3_id]) as [new_dev_counter_color_coverage3]
            ,   max([dev_counter_state_operation]) over(partition by dev_id, [last_dev_counter_state_operation_id]) as [new_dev_counter_state_operation]
            ,   max([dev_counter_state_waiting]) over(partition by dev_id, [last_dev_counter_state_waiting_id]) as [new_dev_counter_state_waiting]
            ,   max([dev_counter_state_preheat]) over(partition by dev_id, [last_dev_counter_state_preheat_id]) as [new_dev_counter_state_preheat]
            ,   max([dev_counter_state_sleep]) over(partition by dev_id, [last_dev_counter_state_sleep_id]) as [new_dev_counter_state_sleep]
            ,   max([dev_counter_state_offmode]) over(partition by dev_id, [last_dev_counter_state_offmode_id]) as [new_dev_counter_state_offmode]
            ,   max([dev_counter_state_down_sc]) over(partition by dev_id, [last_dev_counter_state_down_sc_id]) as [new_dev_counter_state_down_sc]
            ,   max([dev_counter_state_down_pj]) over(partition by dev_id, [last_dev_counter_state_down_pj_id]) as [new_dev_counter_state_down_pj]
            ,   max([dev_counter_state_down_sj]) over(partition by dev_id, [last_dev_counter_state_down_sj_id]) as [new_dev_counter_state_down_sj]
            ,   max([dev_counter_state_down_sup_pm]) over(partition by dev_id, [last_dev_counter_state_down_sup_pm_id]) as [new_dev_counter_state_down_sup_pm]
        from (  select  [dev_counter_id], dev_id
                    ,   [dev_counter_total_color]
                    ,   max(case when [dev_counter_total_color] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_total_color_id]
                    ,   [dev_counter_total_mono]
                    ,   max(case when [dev_counter_total_mono] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_total_mono_id]
                    ,   [dev_counter_copy_black]
                    ,   max(case when [dev_counter_copy_black] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_copy_black_id]
                    ,   [dev_counter_copy_color_full]
                    ,   max(case when [dev_counter_copy_color_full] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_copy_color_full_id]
                    ,   [dev_counter_copy_color_mono]
                    ,   max(case when [dev_counter_copy_color_mono] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_copy_color_mono_id]
                    ,   [dev_counter_copy_color_twin]
                    ,   max(case when [dev_counter_copy_color_twin] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_copy_color_twin_id]
                    ,   [dev_counter_printer_black]
                    ,   max(case when [dev_counter_printer_black] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_printer_black_id]
                    ,   [dev_counter_printer_color_full]
                    ,   max(case when [dev_counter_printer_color_full] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_printer_color_full_id]
                    ,   [dev_counter_printer_color_mono]
                    ,   max(case when [dev_counter_printer_color_mono] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_printer_color_mono_id]
                    ,   [dev_counter_printer_color_twin]
                    ,   max(case when [dev_counter_printer_color_twin] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_printer_color_twin_id]
                    ,   [dev_counter_printer_color_levl]
                    ,   max(case when [dev_counter_printer_color_levl] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_printer_color_levl_id]
                    ,   [dev_counter_fax_black]
                    ,   max(case when [dev_counter_fax_black] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_fax_black_id]
                    ,   [dev_counter_fax_color_full]
                    ,   max(case when [dev_counter_fax_color_full] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_fax_color_full_id]
                    ,   [dev_counter_fax_color_mono]
                    ,   max(case when [dev_counter_fax_color_mono] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_fax_color_mono_id]
                    ,   [dev_counter_fax_send]
                    ,   max(case when [dev_counter_fax_send] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_fax_send_id]
                    ,   [dev_counter_gpc]
                    ,   max(case when [dev_counter_gpc] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_gpc_id]
                    ,   [dev_counter_gpc_printer]
                    ,   max(case when [dev_counter_gpc_printer] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_gpc_printer_id]
                    ,   [dev_counter_gpc_color_full]
                    ,   max(case when [dev_counter_gpc_color_full] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_gpc_color_full_id]
                    ,   [dev_counter_a3_dlt]
                    ,   max(case when [dev_counter_a3_dlt] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_a3_dlt_id]
                    ,   [dev_counter_duplex]
                    ,   max(case when [dev_counter_duplex] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_duplex_id]
                    ,   [dev_counter_send_color]
                    ,   max(case when [dev_counter_send_color] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_send_color_id]
                    ,   [dev_counter_send_mono]
                    ,   max(case when [dev_counter_send_mono] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_send_mono_id]
                    ,   [dev_counter_fax_color_twin]
                    ,   max(case when [dev_counter_fax_color_twin] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_fax_color_twin_id]
                    ,   [dev_counter_total]
                    ,   max(case when [dev_counter_total] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_total_id]
                    ,   [dev_counter_coverage_color]
                    ,   max(case when [dev_counter_coverage_color] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_coverage_color_id]
                    ,   [dev_counter_coverage_black]
                    ,   max(case when [dev_counter_coverage_black] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_coverage_black_id]
                    ,   [dev_counter_cov_color_prt_page]
                    ,   max(case when [dev_counter_cov_color_prt_page] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_cov_color_prt_page_id]
                    ,   [dev_counter_cov_black_prt_page]
                    ,   max(case when [dev_counter_cov_black_prt_page] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_cov_black_prt_page_id]
                    ,   [dev_counter_a2]
                    ,   max(case when [dev_counter_a2] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_a2_id]
                    ,   [dev_counter_scanner_send_color]
                    ,   max(case when [dev_counter_scanner_send_color] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_scanner_send_color_id]
                    ,   [dev_counter_scanner_send_black]
                    ,   max(case when [dev_counter_scanner_send_black] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_scanner_send_black_id]
                    ,   [dev_counter_fcolor_sheet_dom]
                    ,   max(case when [dev_counter_fcolor_sheet_dom] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_sheet_dom_id]
                    ,   [dev_counter_mcolor_sheet_dom]
                    ,   max(case when [dev_counter_mcolor_sheet_dom] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_mcolor_sheet_dom_id]
                    ,   [dev_counter_fcolor_copy_charge]
                    ,   max(case when [dev_counter_fcolor_copy_charge] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_copy_charge_id]
                    ,   [dev_counter_black_copy_charge]
                    ,   max(case when [dev_counter_black_copy_charge] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_black_copy_charge_id]
                    ,   [dev_counter_fcolor_prt_charge]
                    ,   max(case when [dev_counter_fcolor_prt_charge] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_prt_charge_id]
                    ,   [dev_counter_black_print_charge]
                    ,   max(case when [dev_counter_black_print_charge] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_black_print_charge_id]
                    ,   [dev_counter_fcolor_tot_charge]
                    ,   max(case when [dev_counter_fcolor_tot_charge] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_tot_charge_id]
                    ,   [dev_counter_black_total_charge]
                    ,   max(case when [dev_counter_black_total_charge] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_black_total_charge_id]
                    ,   [dev_counter_fcolor_economy_prt]
                    ,   max(case when [dev_counter_fcolor_economy_prt] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_economy_prt_id]
                    ,   [dev_counter_black_economy_prt]
                    ,   max(case when [dev_counter_black_economy_prt] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_black_economy_prt_id]
                    ,   [dev_counter_fcolor_sheets_prt]
                    ,   max(case when [dev_counter_fcolor_sheets_prt] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_sheets_prt_id]
                    ,   [dev_counter_mcolor_sheets_prt]
                    ,   max(case when [dev_counter_mcolor_sheets_prt] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_mcolor_sheets_prt_id]
                    ,   [dev_counter_fcolor_sheets_a3u]
                    ,   max(case when [dev_counter_fcolor_sheets_a3u] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_fcolor_sheets_a3u_id]
                    ,   [dev_counter_mcolor_sheets_a3d]
                    ,   max(case when [dev_counter_mcolor_sheets_a3d] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_mcolor_sheets_a3d_id]
                    ,   [dev_counter_color_coverage1]
                    ,   max(case when [dev_counter_color_coverage1] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_color_coverage1_id]
                    ,   [dev_counter_color_coverage2]
                    ,   max(case when [dev_counter_color_coverage2] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_color_coverage2_id]
                    ,   [dev_counter_color_coverage3]
                    ,   max(case when [dev_counter_color_coverage3] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_color_coverage3_id]
                    ,   [dev_counter_state_operation]
                    ,   max(case when [dev_counter_state_operation] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_operation_id]
                    ,   [dev_counter_state_waiting]
                    ,   max(case when [dev_counter_state_waiting] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_waiting_id]
                    ,   [dev_counter_state_preheat]
                    ,   max(case when [dev_counter_state_preheat] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_preheat_id]
                    ,   [dev_counter_state_sleep]
                    ,   max(case when [dev_counter_state_sleep] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_sleep_id]
                    ,   [dev_counter_state_offmode]
                    ,   max(case when [dev_counter_state_offmode] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_offmode_id]
                    ,   [dev_counter_state_down_sc]
                    ,   max(case when [dev_counter_state_down_sc] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_down_sc_id]
                    ,   [dev_counter_state_down_pj]
                    ,   max(case when [dev_counter_state_down_pj] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_down_pj_id]
                    ,   [dev_counter_state_down_sj]
                    ,   max(case when [dev_counter_state_down_sj] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_down_sj_id]
                    ,   [dev_counter_state_down_sup_pm]
                    ,   max(case when [dev_counter_state_down_sup_pm] is not null then [dev_counter_id] end) over (partition by dev_id order by [dev_counter_id]) [last_dev_counter_state_down_sup_pm_id]
                from device_counter
            ) v
      ) as t
inner join device_counter
        on t.dev_counter_id = device_counter.dev_counter_id;
end
print 'xtender 3rd way: ' + cast(datediff(ms, @startdate, getdate()) as nvarchar(max));
rollback tran
go

Результаты:
Cursor way: 246
Warning: Null value is eliminated by an aggregate or other SET operation.
xtender 1st way: 76
Warning: Null value is eliminated by an aggregate or other SET operation.
xtender 2nd way: 10546
Warning: Null value is eliminated by an aggregate or other SET operation.
xtender 3rd way: 2766

Общий итог прежний — если нет вариантов использовать аггрегатные функции, введенные в SQL 2012, лучшим вариантом по-прежнему оказывается курсор.

xtender 18 фев 2016 в 01:40

ну а остальные варианты-то? целых три еще.
И потом оконные функции имхо в ms sql давным давно были

xtender 18 фев 2016 в 01:54

Второй мой вариант точно работает — проверил: http://sqlfiddle.com/#!3/61592/2/0

xtender 18 фев 2016 в 01:55

И третий тоже работает: http://sqlfiddle.com/#!3/61592/3

xtender 12 фев 2016 в 14:10

Или попроще — второй уровень считать через lag()over():

select 
    v.*
   ,lag(b,a-last_a)over(order by a) test_val
from
       (
       select
          a,b
         ,last_value(b ignore nulls)over(order by a) right_val
         ,max(case when b is not null then a end)over(order by a) last_a
       from t
       ) v

xtender 12 фев 2016 в 14:14

Третий вариант — просто брать максимум группы, где группа — это last_a:

select 
    v.*
   ,max(b) over(partition by last_a) test_val
from
       (
       select
          a,b
         ,last_value(b ignore nulls)over(order by a) right_val
         ,max(case when b is not null then a end)over(order by a) last_a
       from t
       ) v

minamoto 12 фев 2016 в 16:36

Так у вас во всех трех запросах встречается last_value(b ignore nulls), именно его в SQL Server'е и нет.

xtender 12 фев 2016 в 16:40

Он там не используется, он только для сверки результатов

xtender 12 фев 2016 в 02:39

Дайте готовую схему на sqlfiddle.com попробую на MS SQL адаптировать оракловый:

merge 
into DEVICE_COUNTER t 
using ( 
        select t.rid, t.new_cnt_value1,t.new_cnt_value2
        from (
           select 
                ROWID as rid
              , dev_counter_duplex1
              , dev_counter_duplex2
              , last_value( dev_counter_duplex1 ignore nulls ) 
                      over( partition by dev_id order by dev_counter_date, dev_counter_id) as new_cnt_value1
              , last_value (dev_counter_duplex2 ignore nulls ) 
                      over( partition by dev_id order by dev_counter_date, dev_counter_id) as new_cnt_value2
           from DEVICE_COUNTER
        ) t
        where t.dev_counter_duplex1 is null
           or t.dev_counter_duplex2 is null
      ) v
on (t.rowid = v.rid and (t.dev_counter_duplex1 is null or t.dev_counter_duplex2 is null))
   when matched then 
      update set dev_counter_duplex1 = new_cnt_value1
                ,dev_counter_duplex2 = new_cnt_value2

minamoto 12 фев 2016 в 08:40

sqlfiddle.com не тянет схемы таких размеров :) Ругается, что слишком большой объем данных ему передан.

xtender 12 фев 2016 в 14:08

Он тянет по отдельности по размеру: 1) создание первой таблицы и 2) создание и заполнения второй таблички, но вместе в одну схему это не скомпилить. Можно это сделать как без промежуточной первой таблицы?

minamoto 12 фев 2016 в 16:37

Там одна таблица. еще одна вьюха — она для заполнения нужна. Т.е. код создания таблицы и код заполнения таблицы вместе в код создания схемы не умещаются.

minamoto 11 фев 2016 в 15:50

1) У вас вообще этот код разные задачи выполняет. Один обновляет одно поле, другое — два других поля. Странно вообще сравнивать их производительность.

2) «Настоящий» SQL девелопер включает голову и пишет правильный оптимизированный запрос, а не лепит страшные курсоры. Бывают случаи, когда курсоры необходимы, но это явно не тот случай.

А вот и репро (тестовые данные намеренно урезал относительно озвученных, ибо ваш код на указанных цифрах выполняется нереально долго):

Код для генерации тестовых данных

if object_id('device_counter') is null
begin
create table device_counter (dev_id int, dev_counter_color int, dev_counter_date date);
create clustered index device_counter_clust on device_counter (dev_counter_date, dev_id);
end;
else
	truncate table device_counter;

with devices
as 
(
	select number
	from [master].dbo.spt_values
	WHERE [type] = 'P' and number < 57
),
numbers as 
(
	SELECT ROW_NUMBER() OVER (ORDER BY 1/0) as rn, t.number
	FROM (
		SELECT top (2000000000) case when val1.number < 22 then null else val2.number * 1000 + val1.number end as number
		FROM [master].dbo.spt_values val1
				inner join [master].dbo.spt_values val2
						on val2.type = 'P' and val2.number < 100
		WHERE val1.[type] = 'P' and val1.number < 100
		ORDER BY NEWID()
	) t
)
insert into device_counter (dev_id, dev_counter_color, dev_counter_date)
select devices.number, numbers.number, dateadd(minute, numbers.rn, '20100101')
from devices
cross join numbers

Код для тестирования производительности разных запросов

declare @startdate datetime = getdate();
set nocount on
begin tran
Update curr set curr.dev_counter_color=coalesce(curr.dev_counter_color, prev.dev_counter_color) from
device_counter  curr  left join device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_color is null
   and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub where sub.dev_counter_date < curr.dev_counter_date and dev_counter_color is not null);
print 'First method: ' + cast(datediff(ms, @startdate, getdate()) as nvarchar(max))
rollback tran
go
declare @startdate datetime = getdate();
set nocount on
begin tran
Update t1 
set		dev_counter_color= t2.dev_counter_color
from	device_counter t1
		cross apply (select top 1 dev_counter_color
				     from	device_counter t2
					 where	t1.dev_id = t2.dev_id
						and t1.dev_counter_date > t2.dev_counter_date
						and t2.dev_counter_color is not null
					order by t2.dev_counter_date desc
					) as t2
where	t1.dev_counter_color is null;
print 'Second method: ' + cast(datediff(ms, @startdate, getdate()) as nvarchar(max))
rollback tran
go

И результаты:
First method: 34950
Second method: 2703

Заставить работать ваш курсор на ограниченных тремя полями данных мне вообще не удалось.

xtender 11 фев 2016 в 23:18

Я в MS SQL не разбираюсь, но интересно почему не используется такой же MERGE в MS SQL? Это же крайне быстро было бы аналитикой рассчитать неверные значения и хэшджойном проапдейтить?

minamoto 11 фев 2016 в 23:25

Не проще сначала выбрать неверные значения и обновить их рассчитанными верными значениями?
Merge хорош для слияния наборов — когда нужно добавить недостающие данные и обновить несоответствующие.
А тут — только несоответствующие, update вполне достаточно, кмк.

xtender 11 фев 2016 в 23:30

Не проще сначала выбрать неверные значения и обновить их рассчитанными верными значениями?

Так это зависит от соотношения — кол-ва которое необходимо изменить/кол-во которое не нужно менять. Если менять нужно хотя бы, скажем, 30%, то merge будет гораздо лучше, т.к. будет всего 2 фулскана и один хэшджойн. Вообще с точки зрения гибкости даже — при необходимости оптимизатор может поменять на nested loops, если окажется что менять надо мало.

minamoto 11 фев 2016 в 23:33

Тут согласен — требуются дополнительные исследования — пока не готов дать ответ, какой вариант предпочтительнее.

xtender 11 фев 2016 в 23:34

Например, в первом апдейте от топик стартера — идет 3 обращения к одной и той же таблице, причем на каждую строку еще и подзапросом вычисляется max() — т.е. это уже точно как минимум один построчный nested loops перебор. К тому же left join, а те join — то есть несоответствующие данные то даже не отфильтрованы и апдейт значения будет происходит само на себя

skullodrom 17 фев 2016 в 01:47

Язык SQL (четвертого уровня) очень неуклюж и не эффективен для таких задач как пропуски и другие подобные ему, тут старый добрый язык третьего уровня будет куда эффективнее

Зарегистрируйтесь на Хабре, чтобы оставить комментарий

Показать лучшие за всё время

Работа над ошибками аппаратуры на стороне SQL сервера и пользе нагрузочных тестов

Комментарии 39

Публикации

Истории