begin tran

declare @invoiceType varchar(max) = 'WNUW'
declare @invoiceMonth int = 201608

update i
set invoiceNR = newNR, invoiceFullNR = newFullNR
from document.invoice i
inner join
(
select invoiceID, invoiceFullNR, invoiceNR, invoiceIssueDate
, row_number() over (order by status desc, invoiceNR) newNr
, invoiceCategory+right('00000' + cast(row_number() over (order by status desc, invoiceNR) as varchar(max)), 3) + '/' + cast(invoiceMonth as varchar(max)) newFullNR
, [status]
from document.invoice where invoiceMonth = @invoiceMonth and invoiceCategory = @invoiceType
) src on src.invoiceID = i.invoiceID

select invoiceFullNR, invoiceNR, invoiceIssueDate
, row_number() over (order by status desc, invoiceNR) newNr
, invoiceCategory+right('00000' + cast(row_number() over (order by status desc, invoiceNR) as varchar(max)), 3) + '/' + cast(invoiceMonth as varchar(max)) newFullNR
, [status], *
from document.invoice where invoiceMonth = @invoiceMonth and invoiceCategory = @invoiceType order by invoice.invoiceNR

rollback tran
