Cumalitive Totals and Percentages for Ranking Inventory 1

[Jcfx]

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.

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

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:

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

Thank you for any assistance.

Julie
CRXI CE10 / RS2005 Sql DB

 

[gmmastros]

Running totals is one of the VERY few places where you should use a cursor.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[k01]

Hi Julie,

Could you give me the sample data?
Then I can give you the answer quickly.

Thanks


Koichi

http://aboutabi.blogspot.com/

(SQLServer & Cognos Tips)

 

[Jcfx]

Here is a portion. I've dropped the year column for now.

itemid	PARTN  AnlTrans	AnnualQty  AnnualCoGt	AnnualSale
4	12345	1725	2391	   $1,471.57	$2,184.56
5	14582	1685	2191	   $487.59	$727.78
4369	145712	1430	6649	   $642.21	$960.32
5318	100012	1287	1626	   $2,715.65	$3,997.18
1487	78450	1176	33359	   $306.37	$472.03
1358	122	1060	2369	   $147.44	$221.51
5297	13215	1034	38270.8	   $2,394.30	$3,426.09
5309	1235468	960	1832	   $2,231.76	$3,166.16
5366	58425	929	2298	   $4,196.23	$5,967.50
6064	154789	736	75869	   $26.24	$41.04
3897	12302	694	1873	   $5,243.77	$7,406.39
5337	1235796	684	1538	   $1,364.82	$1,971.42
6310	7894	620	978	   $4,436.16	$6,322.17
3878	144	597	618	   $10,683.82	$15,141.46
3107	982	595	10055	   $50.39	$75.91
4722	1458	589	6647	   $60.49	$91.54
3046	2354	544	989	   $625.21	$1,024.69
3741	78125	524	525	   $7,860.19	$11,196.00
1096	9641	513	1973	   $30,172.22	$38,214.26
4720	36141	501	5286	   $34.45	$52.50
4396	985412	499	2031	   $81.61	$120.81

Julie
CRXI CE10 / RS2005 Sql DB

 

[RyanEK]

Hi,

Is this what you're looking for? I'm not sure how you calculate the running percentage. Wouldn't the last record always be 100%?

declare @tmp table (itemid int, value1 int)
insert into @tmp values (1, 523)
insert into @tmp values (2, 400)
insert into @tmp values (3, 389)
insert into @tmp values (4, 198)
insert into @tmp values (5, 103)

select t1.*, 
(cast(t1.rollsum as float) / (select sum(value1) from @tmp)) * 100 as RollPerc
from (
  select t2.*,
  (select sum(value1) from @tmp t3 where t3.itemid <= t2.itemid) as Rollsum
  from @tmp t2
) as t1

Ryan

 

[Jcfx]

Thank you Ryan, I will try this.

Julie
CRXI CE10 / RS2005 Sql DB