dividing a list - 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 2012 Forums

Transact-SQL (2012)

dividing a list

Author

Topic

moramoga
Starting Member

34 Posts

Posted - 2013-09-03 : 10:29:45

Hello,

I have a simple task to do at work almost daily and I think ir can be done easily with some help. There is a table with a single column "PC name". I have to divide the list of PC's into waves.

Wave 1 : 2%
wave 2: 3%
wave 3: 25%
wave 4: 45%
wave 5: 25%

So what I usually do is to copy the list of PC's in excel and add a column named "wave assign". So for example if the list is 100pc's first 2 PC's will be assign to wave 1, 3 PCs to wave 2, 25 PCs to wave 3 and so on. I need a way to automate this since it takes me too long to do it manually. It doesn't matter if there is a small change in the % in order to round up the number of PCs in each wave.

thanks a lot

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-03 : 10:36:24

Change the order by clause in the row_Number to suit whatever ordering you want to use.

SELECT *,
	CASE
		WHEN rn*100.0/MAX(rn) OVER() <= 2.0 THEN 'Wave1'
		WHEN rn*100.0/MAX(rn) OVER() <= 5.0 THEN 'Wave2'
		WHEN rn*100.0/MAX(rn) OVER() <= 30.0 THEN 'Wave3'
		WHEN rn*100.0/MAX(rn) OVER() <= 75.0 THEN 'Wave4'
		ELSE 'Wave5'
	END	 AS [Wave Assign]
FROM
(
SELECT
	*,
	ROW_NUMBER() OVER (ORDER BY pc_name) AS RN
FROM
	YourTable
) s

moramoga
Starting Member

34 Posts

Posted - 2013-09-03 : 17:26:29

quote:

Originally posted by James K

Change the order by clause in the row_Number to suit whatever ordering you want to use.

SELECT *,
	CASE
		WHEN rn*100.0/MAX(rn) OVER() <= 2.0 THEN 'Wave1'
		WHEN rn*100.0/MAX(rn) OVER() <= 5.0 THEN 'Wave2'
		WHEN rn*100.0/MAX(rn) OVER() <= 30.0 THEN 'Wave3'
		WHEN rn*100.0/MAX(rn) OVER() <= 75.0 THEN 'Wave4'
		ELSE 'Wave5'
	END	 AS [Wave Assign]
FROM
(
SELECT
	*,
	ROW_NUMBER() OVER (ORDER BY pc_name) AS RN
FROM
	YourTable
) s

Hi! So far I have only Access installed so while I get cooporate permission for SQL this worked for me in the meanwhile in excel:

=IF(COUNTA(A$1:A1)<2*COUNTA(A:A)/100,1,IF(COUNTA(A$1:A1)<5*COUNTA(A:A)/100,2,IF(??COUNTA(A$1:A1)<30*COUNTA(A:A)/100,3,IF(COUNTA(A$1:A1)<75*COUNTA(A:A)/100,4,5))))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources