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

Некоторые наши пользователи стали сообшать что репорты иногда возврашают значение превышаюшее 100% для показа роста данных.

При этом выяснилось что сервис, доставляюший данные от аппаратуры иногда делает пропуски в значениях.
Там где сервер читал аппаратуру напрямую, вместо значений вставлял NULL, а там где читал с использованием SNMP, там вставлял 0.

То есть ряд значений счетчика был как: 4, 10, 20, NULL, NULL, 31, 0, 0, 0, 50 а теперь надо 4, 10, 20, 20, 20, 31, 31, 31, 31, 50

Наверное можно было бы апроксимировать данные, но стэхолдеров это устраивает, а наше дело удовлетворять клиентов.
Что делать стало ясно, вопрос только на какой стороне что исправлять.



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

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

Первое решение был очевидным и простым.

для MSSQL 2008:

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);


для Oracle:
merge into DEVICE_COUNTER t using ( select ROWID as rid, last_value(dev_counter_duplex ignore nulls ) 
  over (partition by dev_id order by dev_counter_date, dev_counter_id) as new_cnt_value from DEVICE_COUNTER t ) v
  on (t.rowid = v.rid) when matched then update set dev_counter_duplex = new_cnt_value


И не прошло и полгода, как появился пользователь с базой на десяток тысяч девайсов с данными за несколько лет (около пяти миллионов записей) и стал жаловатся что не смог дождатся конца апгрейта базы.

Одновремено пришло письмо из техсапорта решивших предложить починить базу со всеми 57 счетчиками.

Казалось бы — чинишь запросом колонку за колонкой да и дело с концом.

Когда мы все таки стянули базу у клиента, оказалось запрос работает на одной колонке почти две минуты на средненьком виртуальном сервере, а слабенький лаптоп клиента, жаловавшегося на скорость апдейта, по нашим подсчетам делал все все 57 sql запросов за ~52 часа!..

По обыкновению мы рекомендуем нашим клиентам работать с техникой, параметры которой указаны в technical requirements, но неужели все так плохо в реляционных языках?

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

Если бы пропуски данных были во всех колонках — то простой проход с апдейтом всех строк занимает примерно тоже время что и первоначальный вариант — 38 минут против 92 при реляционном подходе…

И только тот факт, что пропуски редки — сократило апгрейд до 2! минут — это для 22,000 строк из 3 миллионнов.

Финальный код TSQL. Kоличество полей уменьшил до 2, чтобы код таким громозким не выглядел:
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;

DECLARE @next_dev_id int = NULL, @next_id int = NULL
DECLARE @next_counter_total_color int = NULL, @next_counter_total_mono int = NULL;

DECLARE UPDCURSOR CURSOR for select dev_id, dev_counter_id, 
dev_counter_total_color,
dev_counter_total_mono 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;

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM UPDCURSOR INTO @next_dev_id, @next_id
		, @next_counter_total_color, @next_counter_total_mono

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 ))) )
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);

    SET @updated = @updated + 1
    UPDATE device_counter SET 
		dev_counter_total_color = @cur_counter_total_color,
		dev_counter_total_mono = @cur_counter_total_mono
	WHERE CURRENT OF UPDCURSOR

END ELSE
	BEGIN
		SELECT
			@cur_counter_total_color = @next_counter_total_color,
			@cur_counter_total_mono = @next_counter_total_mono;
	END
	SET @cur_dev_id = @next_dev_id
END
CLOSE UPDCURSOR
DEALLOCATE UPDCURSOR
END


Ничего особенно в этой статье не изобрел, просто практика настояшего SQL девелопера требует избегать использование курсора, но как показал данный пример если есть некрасивое альтернативное решение и оно работает лучше, то надо его использовать…

И особенно важно делать не только юнит тесты но нагрузочные тесты на максимально возможных обьемах.
Ads
AdBlock has stolen the banner, but banners are not teeth — they will be back

More

Comments 39

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

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

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

      В этом и суть статью — не все решается парадигмами с нужной скоростью, иначе бы курсоры и while в язык не добавляли, у меня была задача, которая красиво ложилась на TSQL, но из-за низкой производительности была имплементирована на ESP.
        0
        если 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 за самими данными, чтобы исключить эту операцию
          +1
          Нет, вру — нужно задетектировать не все колонки NULL, а хотя бы одну. Тогда в вычислимой колонке нужно просто сложить логически их значения (можно и арифметически, но 57 колонок могут дать переполнение):

          CASE WHEN col1 | col2 |… | col57 IS NULL THEN 1 ELSE 0 END
      +2
      А что с Oracle запросом? Он остался в прежнем виде, с ним не было проблем?
        +3
        Я бы для 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 вообще не учитываются почему-то, несмотря на озвученное условие в начале.
          0
          Я рад что вы умеете включать голову, но вы не учли что колонок не 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 минуты.
            0
            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
            
            
              +1
              Я не разбираюсь в MS SQL/TSQL, но этот код выглядит адски страшным. И, впредь, смотрите куда комментарии пишете.
                0
                Собственно налицо еще и неверно спроектированная база данных.

                Ваша заливка рассчитана на Oracle, если будет время — попробую перевести ее на SQL и привести свой запрос к варианту, учитывающему множество колонок, чтобы сравнить производительность.
                  0
                  Это не оракл :) у нас такого нет :)
                  0
                  Я использовал ваши тестовые данные для проверки пары гипотез и, несмотря на то, что мне удалось в разы улучшить результат по сравнению с вашим изначальным 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, но я их не знаю.
                    0
                    Причина — отсутствие подходящих аналитических функций — last_value(… ignore nulls ) в MS SQL Server.

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

                    Выйгрышь реляционого подхода на подобной таблице примерно до 20 полей, после курсор быстрее, тем более на реальной базе всего 20 тыш строк требуется проапгрейтить из 3 миллионов, что для сервера совсем немного.
                      0
                      ну есть же max() over(partition by… order by ...)
                        0
                        Он выберет максимальное значение, а нужно последнее, разве нет?
                          0
                          В оракле например можно получить то же самое через 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 тоже нет?
                            +1
                            Вот второй нагугленный вариант можно подразумевать под "Включить голову". Жаль, что я сам до такого варианта не додумался.

                            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 я не знаю, поэтому не знаю, есть ли у него аналоги.
                              0
                              Спасибо хорошее решение, но к сожаления у мы все еше поддерживаем 2008.
                                0
                                Эти два работают и на 2008:
                                http://sqlfiddle.com/#!3/61592/2/0
                                http://sqlfiddle.com/#!3/61592/3
                                  +1
                                  Вот за это я не люблю 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

                                    0
                                    репро (без создания таблиц) часть 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, лучшим вариантом по-прежнему оказывается курсор.
                                  0
                                  ну а остальные варианты-то? целых три еще.
                                  И потом оконные функции имхо в ms sql давным давно были
                                    0
                                    Второй мой вариант точно работает — проверил: http://sqlfiddle.com/#!3/61592/2/0
                                      0
                                      И третий тоже работает: http://sqlfiddle.com/#!3/61592/3
                                    0
                                    Или попроще — второй уровень считать через 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
                                    
                                      0
                                      Третий вариант — просто брать максимум группы, где группа — это 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
                                      
                                        0
                                        Так у вас во всех трех запросах встречается last_value(b ignore nulls), именно его в SQL Server'е и нет.
                                          0
                                          Он там не используется, он только для сверки результатов
                                    0
                                    Дайте готовую схему на 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
                                    
                                      0
                                      sqlfiddle.com не тянет схемы таких размеров :) Ругается, что слишком большой объем данных ему передан.
                                        0
                                        Он тянет по отдельности по размеру: 1) создание первой таблицы и 2) создание и заполнения второй таблички, но вместе в одну схему это не скомпилить. Можно это сделать как без промежуточной первой таблицы?
                                          0
                                          Там одна таблица. еще одна вьюха — она для заполнения нужна. Т.е. код создания таблицы и код заполнения таблицы вместе в код создания схемы не умещаются.
                              +2
                              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

                              Заставить работать ваш курсор на ограниченных тремя полями данных мне вообще не удалось.
                                0
                                Я в MS SQL не разбираюсь, но интересно почему не используется такой же MERGE в MS SQL? Это же крайне быстро было бы аналитикой рассчитать неверные значения и хэшджойном проапдейтить?
                                  0
                                  Не проще сначала выбрать неверные значения и обновить их рассчитанными верными значениями?
                                  Merge хорош для слияния наборов — когда нужно добавить недостающие данные и обновить несоответствующие.
                                  А тут — только несоответствующие, update вполне достаточно, кмк.
                                    +1
                                    Не проще сначала выбрать неверные значения и обновить их рассчитанными верными значениями?
                                    Так это зависит от соотношения — кол-ва которое необходимо изменить/кол-во которое не нужно менять. Если менять нужно хотя бы, скажем, 30%, то merge будет гораздо лучше, т.к. будет всего 2 фулскана и один хэшджойн. Вообще с точки зрения гибкости даже — при необходимости оптимизатор может поменять на nested loops, если окажется что менять надо мало.
                                      0
                                      Тут согласен — требуются дополнительные исследования — пока не готов дать ответ, какой вариант предпочтительнее.
                                      0
                                      Например, в первом апдейте от топик стартера — идет 3 обращения к одной и той же таблице, причем на каждую строку еще и подзапросом вычисляется max() — т.е. это уже точно как минимум один построчный nested loops перебор. К тому же left join, а те join — то есть несоответствующие данные то даже не отфильтрованы и апдейт значения будет происходит само на себя
                                  0
                                  Язык SQL (четвертого уровня) очень неуклюж и не эффективен для таких задач как пропуски и другие подобные ему, тут старый добрый язык третьего уровня будет куда эффективнее

                                  Only users with full accounts can post comments. Log in, please.