| SQL |
select top 1 ownerId, ownerType, cdr_recordId, skuId,
datepart( year, invoiceDate ) year, 'Week' periodType, datepart( Week, invoiceDate ) period,
'Case' UOM,
sum( caseAmt ) totalUnits,
sum( dollarAmt ) totalDollars,
min( dollarAmt / caseAmt ) minPricePerUnit,
max( dollarAmt / caseAmt ) maxPricePerUnit,
sum( dollarAmt ) / sum( caseAmt ) avgPricePerUnit,
max( invoiceDate ) dateOfLastInvoice
from tbl_CDR_InvoiceHistory with ( nolock )
where caseAmt > 0
and dollarAmt > 0
and ownerId = ?
and ownerType = ?
and datepart( Week, invoiceDate ) <= datepart( Week, ? )
and datepart( year, invoiceDate ) = datepart( year, ? )
group by ownerId, ownerType, cdr_recordId, skuId, datepart( year, invoiceDate ), datepart( Week, invoiceDate )
order by year desc, period desc |