with ranked as (
select ownerId, ownerType, cdr_recordId, skuId, stockingId, row_number() over ( partition by ownerId, ownerType, cdr_recordId, skuId order by stockingId desc ) keepRank
from tbl_CDR_Stocking with (nolock)
where ownerId > 0
)
, accounts as (
select ownerId, ownerType, count(*) toPurgeCount
from ranked where keepRank > 1
group by ownerId, ownerType
)
select * from accounts
order by toPurgeCount desc
declare @ownerId int = ;
declare @ownerType varchar(3) = ;
with ranked as (
select ownerId, ownerType, cdr_recordId, skuId, stockingId, row_number() over ( partition by ownerId, ownerType, cdr_recordId, skuId order by stockingId desc ) keepRank
from tbl_CDR_Stocking with (nolock)
where ownerId = @ownerId
and ownerType = @ownerType
)
, purge as (
select * from ranked where keepRank > 1
)
delete s
from tbl_CDR_Stocking s
inner join purge p
on p.ownerId = s.ownerId
and p.ownerType = s.ownerType
and p.stockingId = s.stockingId
and p.cdr_recordId = s.cdr_recordId
and p.skuId = s.skuId
#ownerType##ownerId#: removed #lsnumberformat( _r.recordCount )# repeated records
select distinct ownerId, ownerType
from tbl_CDR_Stocking with (nolock)
where ownerId > 0
select name from sys.tables where name =
declare @ownerId int = ;
declare @ownerType varchar(3) = ;
with lastInvoice as (
select cdr_recordId, skuId, lastInvoiceDate, lastInvoiceCases, lastInvoiceLbs, lastInvoiceDollars, row_number() over( partition by cdr_recordId, skuId order by lastInvoiceDate desc ) as lastInvoiceRank
from tbl_DW_DistributorSales#_tableSuffix# with (nolock)
where lastInvoiceDate is not null
)
update s
set s.lastInvoiceDate = l.lastInvoiceDate,
s.lastInvoiceCases = l.lastInvoiceCases,
s.lastInvoiceDollars = l.lastInvoiceDollars,
s.lastInvoiceLbs = l.lastInvoiceLbs
from tbl_CDR_Stocking s
inner join lastInvoice l
on l.skuId = s.skuId
and l.cdr_RecordId = s.cdr_recordId
and l.lastInvoiceRank = 1
where s.ownerId = @ownerId
and s.ownerType = @ownerType
and coalesce( s.lastInvoiceDate, dateFromParts( 1966, 1, 1 )) != l.lastInvoiceDate
#ownerType##ownerId#: updated lastInvoice fields on #lsnumberformat( _r.recordCount )# stocking records