Calculate 3 percents of total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculate 3 percents of total

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2006-12-13 : 09:17:24

I have a table with two values and I want to calculate the and store a summed percent of the total in three additional columns. I have the records ordered by Qty and I want to know the summed quantity that gives me 90+, 80+ and 70+ percent of total. Can I do this without using a cursor? Thanks, vmon

Rec Qty
1 20
2 20
3 15
4 13
5 10
6 10
7 7
8 5

90+% 80+% 70+%
95 80 78

nr
SQLTeam MVY

12543 Posts

Posted - 2006-12-13 : 09:46:08

total is select tot = sum(QTY) from tbl
percentage for each row is
select Rec, pct = 100.0 * (select sum(QTY) from tbl t2 where t2.Rec <= t.Rec)/(sum(QTY) from tbl)
from tbl t1

From that you can get the min values that give the percentages you want.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 09:49:57

Something like this

-- prepare test data
declare @t table (Rec tinyint, Qty tinyint)

insert	@t
select	1, 20 union all
select	2, 20 union all
select	3, 15 union all
select	4, 13 union all
select	5, 10 union all
select	6, 10 union all
select	7, 7 union all
select	8, 5

-- do the work
select		cat [Category],
		s [Sum],
		case when right(t, 1) = ',' then left(t, len(t) - 1) else t end [Numbers]
from		(
			select distinct	b0.q + b1.q + b2.q + b3.q + b4.q + b5.q + b6.q + b7.q s,
					case
						when b0.q + b1.q + b2.q + b3.q + b4.q + b5.q + b6.q + b7.q >= .9 * i.e then '+90%'
						when b0.q + b1.q + b2.q + b3.q + b4.q + b5.q + b6.q + b7.q >= .8 * i.e then '+80%'
						when b0.q + b1.q + b2.q + b3.q + b4.q + b5.q + b6.q + b7.q >= .7 * i.e then '+70%'
						else ''
					end cat,					
					case when b0.q > 0 then cast(b0.q as varchar) + ',' else '' end +
					case when b1.q > 0 then cast(b1.q as varchar) + ',' else '' end +
					case when b2.q > 0 then cast(b2.q as varchar) + ',' else '' end +
					case when b3.q > 0 then cast(b3.q as varchar) + ',' else '' end +
					case when b4.q > 0 then cast(b4.q as varchar) + ',' else '' end +
					case when b5.q > 0 then cast(b5.q as varchar) + ',' else '' end +
					case when b6.q > 0 then cast(b6.q as varchar) + ',' else '' end +
					case when b7.q > 0 then cast(b7.q as varchar) else '' end t
			from		(select 0 q union all select qty from @t where rec = 1) b0
			cross join	(select 0 q union all select qty from @t where rec = 2) b1
			cross join	(select 0 q union all select qty from @t where rec = 3) b2
			cross join	(select 0 q union all select qty from @t where rec = 4) b3
			cross join	(select 0 q union all select qty from @t where rec = 5) b4
			cross join	(select 0 q union all select qty from @t where rec = 6) b5
			cross join	(select 0 q union all select qty from @t where rec = 7) b6
			cross join	(select 0 q union all select qty from @t where rec = 8) b7
			cross join	(select sum(qty) e from @t) i
		) p
where		cat <> ''
order by	s desc,
		t desc

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 09:52:30

And the output

Category	Sum	Numbers
+90%		100	20,20,15,13,10,10,7,5
+90%		95	20,20,15,13,10,10,7
+90%		93	20,20,15,13,10,10,5
+90%		90	20,20,15,13,10,7,5
+80%		88	20,20,15,13,10,10
+80%		87	20,20,15,10,10,7,5
+80%		85	20,20,15,13,10,7
+80%		85	20,20,13,10,10,7,5
+80%		83	20,20,15,13,10,5
+80%		82	20,20,15,10,10,7
+80%		80	20,20,15,13,7,5
+80%		80	20,20,15,10,10,5
+80%		80	20,20,13,10,10,7
+80%		80	20,15,13,10,10,7,5
+70%		78	20,20,15,13,10
+70%		78	20,20,13,10,10,5
+70%		77	20,20,15,10,7,5
+70%		75	20,20,15,13,7
+70%		75	20,20,15,10,10
+70%		75	20,20,13,10,7,5
+70%		75	20,15,13,10,10,7
+70%		73	20,20,15,13,5
+70%		73	20,20,13,10,10
+70%		73	20,15,13,10,10,5
+70%		72	20,20,15,10,7
+70%		72	20,20,10,10,7,5
+70%		70	20,20,15,10,5
+70%		70	20,20,13,10,7
+70%		70	20,15,13,10,7,5

Peter Larsson
Helsingborg, Sweden

vmon
Yak Posting Veteran

63 Posts

Posted - 2006-12-13 : 10:18:28

Wow. That mostly went way over my head. I am trying to work some of it out. Problem I am having is I falied to mention that I am doing this by ItemId. I have the Qty by ItemId and need the percentages to be within an items records.

Thanks, vmon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 10:25:53

Why do you waste our time?
Post your ENTIRE problem at once. No fuzz.

Do you know how hard it can be to rewrite complete suggestion, because of you "forgot" to mention something!?

Post your sample data here. Also post your expected output BASED on the provided sample data.
Also, if possible, post some relvant DDL too.

Peter Larsson
Helsingborg, Sweden

vmon
Yak Posting Veteran

63 Posts

Posted - 2006-12-13 : 12:08:42

I am sorry for leaving out some information. It is not very well defined for me. Here is the input and output.

ItemId Qty
------------------------ ----------------------
1003V102 187.0000000000
1003V102 170.0000000000
1003V102 17.0000000000
1003V108 180.0000000000
1003V108 180.0000000000
1003V108 180.0000000000
1003V120 200.0000000000
1003V120 200.0000000000
1003V36 60.0000000000
1003V36 60.0000000000
1003V42 70.0000000000
1003V72 120.0000000000
1003V75 125.0000000000
1003V75 125.0000000000

ItemId Qty Tot 90 80 70
------------- ---------------------- --- ----- ---- ----
1003V102 187.0000000000 374 357 299 261
1003V102 170.0000000000 374 357 299 261
1003V102 17.0000000000 374 357 299 261
1003V108 180.0000000000 540 486 432 378
1003V108 180.0000000000 540 486 432 378
1003V108 180.0000000000 540 486 432 378
1003V120 200.0000000000 400 360 320 280
1003V120 200.0000000000 400 360 320 280

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 13:23:56

This is strange.
How do you get the values {486,432,299,261}?


    1003V102   (0-999) * 187
+   1003V102   (0-999) * 170
+   1003V102   (0-999) *  17
----------------------------
=   1003V102     (0 - sum of table)

Is this right?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources