SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 SQL Server 6.5 \ SQL Server 7.0
 How to create select statement and Split Data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kjubnoi
Starting Member

Thailand
3 Posts

Posted - 06/26/2013 :  01:23:39  Show Profile  Reply with Quote
I have output data xx rows from 1 Statement. I want to Split output Data from 1 Statement to 20%, 30%, 50% of xx rows

i have data :
pd_id values
1001 115
1002 65
1003 55
1008 43
1010 33
1011 21
1013 20
1025 12
1033 11
1035 10

i want to show rank and seq follow below :
pd_id values rank seq
1001 115 A 1
1002 65 A 2
1003 55 B 1
1008 43 B 2
1010 33 B 3
1011 21 C 1
1013 20 C 2
1025 12 C 3
1033 11 C 4
1035 10 C 5

pls, help me

khtan
In (Som, Ni, Yak)

Singapore
17627 Posts

Posted - 06/26/2013 :  01:52:32  Show Profile  Reply with Quote
Are you really using SQL Server 6.5 / 7 ? It is much easier if you are using SQL 2005 or later


KH
Time is always against us

Go to Top of Page

kjubnoi
Starting Member

Thailand
3 Posts

Posted - 06/26/2013 :  03:19:58  Show Profile  Reply with Quote
thank for read my forums, I use sql server 7 because this place use only sql server 7
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17627 Posts

Posted - 06/26/2013 :  04:13:03  Show Profile  Reply with Quote
create a temp table with column rank char(1), output the result of your existing query to a temp table


-- update the rank in temp table
update	t
set	rank	= case 	when 	rn * 100.0 / (select count(*) from #temp) <= 20
			then	'A'
			when	rn * 100.0 / (select count(*) from #temp) <= 50 -- 20 + 30 = 50
			then	'B'
			else	'C'
			end
from
(
	select	*, rn = (select count(*) from #temp x where x.pd_id <= t.pd_id)
	from	#temp t
) t		

-- the final query
select	*,
	seq	= (select count(*) from #temp x where x.rank = t.rank and x.pd_id <= t.pd_id)
from	#temp t



KH
Time is always against us

Go to Top of Page

kjubnoi
Starting Member

Thailand
3 Posts

Posted - 06/27/2013 :  03:03:01  Show Profile  Reply with Quote
thank very much, khtan ^^ and i have a question ... Can we create select statement by use only 1 statement ??? (not use temp table or update)
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17627 Posts

Posted - 06/27/2013 :  20:48:01  Show Profile  Reply with Quote
don't think it is possible with SQL Server 7


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000