Good afternoon,
I have been instructed by the powers that be to create either a stored procedure or view that will create ranks for our inventory that can be used at a later date for reporting purposes.
This is what I have so far, the data is pulled from three existing views that contain sales and cost of goods detail.
I need to add three more columns per these instructions:
Accumulated Hits: The cumulative total just includes hits for the first item, after the second item, the cumulative total would include the total hits for the first and second items, etc.
Accumulated Percentage: A percentage based on the above
Ranking: This I can do with case statements after I have the other two columns.
Expected output:
Thank you for any assistance.
Julie
CRXI CE10 / RS2005 Sql DB
I have been instructed by the powers that be to create either a stored procedure or view that will create ranks for our inventory that can be used at a later date for reporting purposes.
This is what I have so far, the data is pulled from three existing views that contain sales and cost of goods detail.
Code:
SELECT
vs.itemid
,vs.PARTNUMBER
,(count(distinct(vs.ordernum)))as AnnualTransactions
,Year(vs.CLOSED) AS YearSold
, Sum(vs.Qtyreqd) AS AnnualQty
,(Sum(Isnull(vd.CoG,0) + Isnull(vr.RCoG,0))) as AnnualCoGt -- To combine CoG from Repair and otc
, Sum(vs.Sale)as AnnualSale
FROM
(vSalePrice vs
LEFT OUTER JOIN
vDirectIssue vd
ON
(vs.reflineid=vd.lineid)
AND
(vs.reforderid=vd.orderid))
LEFT OUTER JOIN
vRepairCost vr
ON
(vs.reforderid=vr.orderid)
AND
(vs.reflineid=vr.lineid)
WHERE
Year(closed) = Year(getdate())
GROUP by
vs.partnumber
,vs.ITEMID
,year(vs.closed)
ORDER BY (count(distinct(vs.ordernum)))desc
Accumulated Hits: The cumulative total just includes hits for the first item, after the second item, the cumulative total would include the total hits for the first and second items, etc.
Accumulated Percentage: A percentage based on the above
Ranking: This I can do with case statements after I have the other two columns.
Expected output:
Code:
Item Partnumber AnnualT YearSold AccumTra Accum% Rank
123 546 523 2008 523 25.97% A
101 645 400 2008 923 45.83% A
105 792 389 2008 1312 65.14% A
120 303 198 2008 1510 74.98% A
40 62 103 2008 1613 80.09% B
Julie
CRXI CE10 / RS2005 Sql DB