Hall of Yaks - 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

Site Related Forums

The Yak Corral

Hall of Yaks

Author

Topic

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-17 : 21:57:45

With reference to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56183

Here is the Updated Data


DECLARE @posts TABLE
(
       member_name     varchar(30),
       [date]          date,
       posts           int
)
SELECT	'AjarnMark',                  	'20010711',	     0	UNION ALL
SELECT	'AjarnMark',                  	'20020109',	  1000	UNION ALL
SELECT	'AjarnMark',                  	'20040106',	  2000	UNION ALL
SELECT	'AjarnMark',                  	'20051010',	  3000	UNION ALL
SELECT	'AndrewMurphy',               	'20001128',	     0	UNION ALL
SELECT	'AndrewMurphy',               	'20040702',	  1000	UNION ALL
SELECT	'AndrewMurphy',               	'20070115',	  2000	UNION ALL
SELECT	'Arnold Fribble',             	'20011111',	     0	UNION ALL
SELECT	'Arnold Fribble',             	'20030714',	  1000	UNION ALL
SELECT	'DonAtWork',                  	'20050408',	     0	UNION ALL
SELECT	'DonAtWork',                  	'20080116',	  1000	UNION ALL
SELECT	'JimL',                       	'20030221',	     0	UNION ALL
SELECT	'JimL',                       	'20050518',	  1000	UNION ALL
SELECT	'JustinBigelow',              	'20010418',	     0	UNION ALL
SELECT	'JustinBigelow',              	'20030603',	  1000	UNION ALL
SELECT	'Kristen',                    	'20040412',	     0	UNION ALL
SELECT	'Kristen',                    	'20040723',	  1000	UNION ALL
SELECT	'Kristen',                    	'20050317',	  3000	UNION ALL
SELECT	'Kristen',                    	'20050805',	  4000	UNION ALL
SELECT	'Kristen',                    	'20051006',	  5000	UNION ALL
SELECT	'Kristen',                    	'20051208',	  6000	UNION ALL
SELECT	'Kristen',                    	'20060223',	  7000	UNION ALL
SELECT	'Kristen',                    	'20060518',	  8000	UNION ALL
SELECT	'Kristen',                    	'20060916',	  9000	UNION ALL
SELECT	'Kristen',                    	'20061121',	 10000	UNION ALL
SELECT	'Kristen',                    	'20070211',	 11000	UNION ALL
SELECT	'Kristen',                    	'20070517',	 12000	UNION ALL
SELECT	'Kristen',                    	'20070829',	 13000	UNION ALL
SELECT	'Kristen',                    	'20070928',	 14000	UNION ALL
SELECT	'Kristen',                    	'20071018',	 15000	UNION ALL
SELECT	'Lamprey',                    	'20060118',	     0	UNION ALL
SELECT	'Lamprey',                    	'20081122',	  1000	UNION ALL
SELECT	'Lumbago',                    	'20040209',	     0	UNION ALL
SELECT	'Lumbago',                    	'20080414',	  1000	UNION ALL
SELECT	'Merkin',                     	'20000802',	     0	UNION ALL
SELECT	'Merkin',                     	'20011012',	  1000	UNION ALL
SELECT	'Merkin',                     	'20020707',	  2000	UNION ALL
SELECT	'Merkin',                     	'20050208',	  4000	UNION ALL
SELECT	'Michael Valentine Jones',    	'20050107',	     0	UNION ALL
SELECT	'Michael Valentine Jones',    	'20051129',	  1000	UNION ALL
SELECT	'Michael Valentine Jones',    	'20060619',	  2000	UNION ALL
SELECT	'Michael Valentine Jones',    	'20061018',	  3000	UNION ALL
SELECT	'Michael Valentine Jones',    	'20070608',	  4000	UNION ALL
SELECT	'Michael Valentine Jones',    	'20080602',	  5000	UNION ALL
SELECT	'MichaelP',                   	'20011213',	     0	UNION ALL
SELECT	'MichaelP',                   	'20031215',	  1000	UNION ALL
SELECT	'MichaelP',                   	'20050603',	  2000	UNION ALL
SELECT	'Nazim',                      	'20010519',	     0	UNION ALL
SELECT	'Page47',                     	'20020409',	     0	UNION ALL
SELECT	'Page47',                     	'20030414',	  2000	UNION ALL
SELECT	'Peso',                       	'20060512',	     0	UNION ALL
SELECT	'Peso',                       	'20060818',	  1000	UNION ALL
SELECT	'Peso',                       	'20060919',	  2000	UNION ALL
SELECT	'Peso',                       	'20061030',	  3000	UNION ALL
SELECT	'Peso',                       	'20061127',	  4000	UNION ALL
SELECT	'Peso',                       	'20061222',	  5000	UNION ALL
SELECT	'Peso',                       	'20070131',	  6000	UNION ALL
SELECT	'Peso',                       	'20070314',	  7000	UNION ALL
SELECT	'Peso',                       	'20070412',	  8000	UNION ALL
SELECT	'Peso',                       	'20070713',	  9000	UNION ALL
SELECT	'Peso',                       	'20070823',	 10000	UNION ALL
SELECT	'Peso',                       	'20070918',	 11000	UNION ALL
SELECT	'Peso',                       	'20071023',	 12000	UNION ALL
SELECT	'Peso',                       	'20071212',	 13000	UNION ALL
SELECT	'Peso',                       	'20080213',	 14000	UNION ALL
SELECT	'Peso',                       	'20080409',	 15000	UNION ALL
SELECT	'Peso',                       	'20080512',	 16000	UNION ALL
SELECT	'Peso',                       	'20080621',	 17000	UNION ALL
SELECT	'Peso',                       	'20080813',	 18000	UNION ALL
SELECT	'Peso',                       	'20080926',	 19000	UNION ALL
SELECT	'Peso',                       	'20081114',	 20000	UNION ALL
SELECT	'Peso',                       	'20090105',	 21000	UNION ALL
SELECT	'Peso',                       	'20090205',	 22000	UNION ALL
SELECT	'Peso',                       	'20090416',	 23000	UNION ALL
SELECT	'Peso',                       	'20090529',	 24000	UNION ALL
SELECT	'Peso',                       	'20090629',	 25000	UNION ALL
SELECT	'RickD',                      	'20010606',	     0	UNION ALL
SELECT	'RickD',                      	'20060210',	  1000	UNION ALL
SELECT	'RickD',                      	'20080616',	  2000	UNION ALL
SELECT	'SamC',                       	'20020524',	     0	UNION ALL
SELECT	'SamC',                       	'20030808',	  1000	UNION ALL
SELECT	'SamC',                       	'20040720',	  2000	UNION ALL
SELECT	'SamC',                       	'20051207',	  3000	UNION ALL
SELECT	'Seventhnight',               	'20040524',	     0	UNION ALL
SELECT	'Seventhnight',               	'20041016',	  1000	UNION ALL
SELECT	'Seventhnight',               	'20050823',	  2000	UNION ALL
SELECT	'Srinika',                    	'20051205',	     0	UNION ALL
SELECT	'Srinika',                    	'20060713',	  1000	UNION ALL
SELECT	'TG',                         	'20041229',	     0	UNION ALL
SELECT	'TG',                         	'20050822',	  1000	UNION ALL
SELECT	'TG',                         	'20060731',	  2000	UNION ALL
SELECT	'TG',                         	'20080806',	  3000	UNION ALL
SELECT	'Transact Charlie',           	'20070706',	     0	UNION ALL
SELECT	'Transact Charlie',           	'20090618',	  1000	UNION ALL
SELECT	'ValterBorges',               	'20010327',	     0	UNION ALL
SELECT	'ValterBorges',               	'20030429',	  1000	UNION ALL
SELECT	'Wanderer',                   	'20020121',	     0	UNION ALL
SELECT	'Wanderer',                   	'20060814',	  1000	UNION ALL
SELECT	'X002548',                    	'20030102',	     0	UNION ALL
SELECT	'X002548',                    	'20030529',	  1000	UNION ALL
SELECT	'X002548',                    	'20030728',	  2000	UNION ALL
SELECT	'X002548',                    	'20031112',	  3000	UNION ALL
SELECT	'X002548',                    	'20040324',	  4000	UNION ALL
SELECT	'X002548',                    	'20040716',	  5000	UNION ALL
SELECT	'X002548',                    	'20041213',	  6000	UNION ALL
SELECT	'X002548',                    	'20051108',	  8000	UNION ALL
SELECT	'X002548',                    	'20060927',	  9000	UNION ALL
SELECT	'X002548',                    	'20070413',	 10000	UNION ALL
SELECT	'X002548',                    	'20080502',	 11000	UNION ALL
SELECT	'afrika',                     	'20041119',	     0	UNION ALL
SELECT	'afrika',                     	'20060215',	  1000	UNION ALL
SELECT	'bklr',                       	'20081115',	     0	UNION ALL
SELECT	'blindman',                   	'20051014',	     0	UNION ALL
SELECT	'blindman',                   	'20070412',	  1000	UNION ALL
SELECT	'byrmol',                     	'20010925',	     0	UNION ALL
SELECT	'byrmol',                     	'20040714',	  1000	UNION ALL
SELECT	'chadmat',                    	'20011217',	     0	UNION ALL
SELECT	'chadmat',                    	'20030730',	  1000	UNION ALL
SELECT	'chiragkhabaria',             	'20050426',	     0	UNION ALL
SELECT	'chiragkhabaria',             	'20060820',	  1000	UNION ALL
SELECT	'derrickleggett',             	'20020618',	     0	UNION ALL
SELECT	'derrickleggett',             	'20050407',	  3000	UNION ALL
SELECT	'derrickleggett',             	'20060118',	  4000	UNION ALL
SELECT	'dinakar',                    	'20070326',	     0	UNION ALL
SELECT	'dinakar',                    	'20070525',	  1000	UNION ALL
SELECT	'ditch',                      	'20040202',	     0	UNION ALL
SELECT	'ditch',                      	'20051007',	  1000	UNION ALL
SELECT	'ehorn',                      	'20020906',	     0	UNION ALL
SELECT	'ehorn',                      	'20040718',	  1000	UNION ALL
SELECT	'elancaster',                 	'20041001',	     0	UNION ALL
SELECT	'elwoos',                     	'20030204',	     0	UNION ALL
SELECT	'elwoos',                     	'20051130',	  1000	UNION ALL
SELECT	'eyechart',                   	'20030530',	     0	UNION ALL
SELECT	'eyechart',                   	'20050321',	  1000	UNION ALL
SELECT	'eyechart',                   	'20060223',	  2000	UNION ALL
SELECT	'eyechart',                   	'20070225',	  3000	UNION ALL
SELECT	'graz',                       	'20000624',	     0	UNION ALL
SELECT	'graz',                       	'20020611',	  1000	UNION ALL
SELECT	'graz',                       	'20051118',	  3000	UNION ALL
SELECT	'harsh_athalye',              	'20060627',	     0	UNION ALL
SELECT	'harsh_athalye',              	'20061119',	  1000	UNION ALL
SELECT	'harsh_athalye',              	'20070213',	  2000	UNION ALL
SELECT	'harsh_athalye',              	'20070510',	  3000	UNION ALL
SELECT	'harsh_athalye',              	'20071204',	  4000	UNION ALL
SELECT	'harsh_athalye',              	'20080508',	  5000	UNION ALL
SELECT	'izaltsman',                  	'20010306',	     0	UNION ALL
SELECT	'izaltsman',                  	'20020624',	  1000	UNION ALL
SELECT	'jen',                        	'20040723',	     0	UNION ALL
SELECT	'jen',                        	'20041203',	  1000	UNION ALL
SELECT	'jen',                        	'20051005',	  2000	UNION ALL
SELECT	'jen',                        	'20060525',	  3000	UNION ALL
SELECT	'jezemine',                   	'20060920',	     0	UNION ALL
SELECT	'jezemine',                   	'20070528',	  1000	UNION ALL
SELECT	'jezemine',                   	'20080526',	  2000	UNION ALL
SELECT	'jhermiz',                    	'20030102',	     0	UNION ALL
SELECT	'jhermiz',                    	'20040921',	  1000	UNION ALL
SELECT	'jhermiz',                    	'20050428',	  2000	UNION ALL
SELECT	'jhermiz',                    	'20060322',	  3000	UNION ALL
SELECT	'jhermiz',                    	'20060330',	  3088	UNION ALL
SELECT	'jsmith8858',                 	'20021107',	     0	UNION ALL
SELECT	'jsmith8858',                 	'20030511',	  1000	UNION ALL
SELECT	'jsmith8858',                 	'20040119',	  2000	UNION ALL
SELECT	'jsmith8858',                 	'20041110',	  3000	UNION ALL
SELECT	'jsmith8858',                 	'20050516',	  4000	UNION ALL
SELECT	'jsmith8858',                 	'20060719',	  5000	UNION ALL
SELECT	'jsmith8858',                 	'20070510',	  6000	UNION ALL
SELECT	'jsmith8858',                 	'20080417',	  7000	UNION ALL
SELECT	'khtan',                      	'20051030',	     0	UNION ALL
SELECT	'khtan',                      	'20060220',	  1000	UNION ALL
SELECT	'khtan',                      	'20060508',	  2000	UNION ALL
SELECT	'khtan',                      	'20060731',	  3000	UNION ALL
SELECT	'khtan',                      	'20061101',	  4000	UNION ALL
SELECT	'khtan',                      	'20070228',	  5000	UNION ALL
SELECT	'khtan',                      	'20070424',	  6000	UNION ALL
SELECT	'khtan',                      	'20070712',	  7000	UNION ALL
SELECT	'khtan',                      	'20071120',	  8000	UNION ALL
SELECT	'khtan',                      	'20080919',	 10000	UNION ALL
SELECT	'khtan',                      	'20090630',	 11000	UNION ALL
SELECT	'madhivanan',                 	'20041120',	     0	UNION ALL
SELECT	'madhivanan',                 	'20050719',	  1000	UNION ALL
SELECT	'madhivanan',                 	'20050910',	  2000	UNION ALL
SELECT	'madhivanan',                 	'20051026',	  3000	UNION ALL
SELECT	'madhivanan',                 	'20051214',	  4000	UNION ALL
SELECT	'madhivanan',                 	'20060210',	  5000	UNION ALL
SELECT	'madhivanan',                 	'20060417',	  6000	UNION ALL
SELECT	'madhivanan',                 	'20060621',	  7000	UNION ALL
SELECT	'madhivanan',                 	'20061002',	  8000	UNION ALL
SELECT	'madhivanan',                 	'20061220',	  9000	UNION ALL
SELECT	'madhivanan',                 	'20070613',	 10000	UNION ALL
SELECT	'madhivanan',                 	'20070920',	 11000	UNION ALL
SELECT	'madhivanan',                 	'20071211',	 12000	UNION ALL
SELECT	'madhivanan',                 	'20080305',	 13000	UNION ALL
SELECT	'madhivanan',                 	'20080715',	 14000	UNION ALL
SELECT	'mfemenel',                   	'20010215',	     0	UNION ALL
SELECT	'mfemenel',                   	'20061113',	  1000	UNION ALL
SELECT	'mike123',                    	'20011214',	     0	UNION ALL
SELECT	'mike123',                    	'20080429',	  1000	UNION ALL
SELECT	'mohdowais',                  	'20021117',	     0	UNION ALL
SELECT	'mohdowais',                  	'20040317',	  1000	UNION ALL
SELECT	'mr_mist',                    	'20020730',	     0	UNION ALL
SELECT	'mr_mist',                    	'20040831',	  1000	UNION ALL
SELECT	'nr',                         	'20010219',	     0	UNION ALL
SELECT	'nr',                         	'20011112',	  2000	UNION ALL
SELECT	'nr',                         	'20030922',	  5000	UNION ALL
SELECT	'nr',                         	'20040511',	  6000	UNION ALL
SELECT	'nr',                         	'20041109',	  7000	UNION ALL
SELECT	'nr',                         	'20050215',	  8000	UNION ALL
SELECT	'nr',                         	'20060328',	  9000	UNION ALL
SELECT	'nr',                         	'20060725',	 10000	UNION ALL
SELECT	'nr',                         	'20070327',	 11000	UNION ALL
SELECT	'nr',                         	'20080408',	 12000	UNION ALL
SELECT	'pootle_flump',               	'20060115',	     0	UNION ALL
SELECT	'ravilobo',                   	'20020420',	     0	UNION ALL
SELECT	'rmiao',                      	'20070409',	     0	UNION ALL
SELECT	'rmiao',                      	'20070621',	  1000	UNION ALL
SELECT	'rmiao',                      	'20070729',	  2000	UNION ALL
SELECT	'rmiao',                      	'20070829',	  2000	UNION ALL
SELECT	'rmiao',                      	'20071107',	  3000	UNION ALL
SELECT	'rmiao',                      	'20080114',	  4000	UNION ALL
SELECT	'rmiao',                      	'20080317',	  5000	UNION ALL
SELECT	'rmiao',                      	'20080521',	  6000	UNION ALL
SELECT	'rmiao',                      	'20080803',	  7000	UNION ALL
SELECT	'robvolk',                    	'20001115',	     0	UNION ALL
SELECT	'robvolk',                    	'20010514',	  1000	UNION ALL
SELECT	'robvolk',                    	'20011025',	  2000	UNION ALL
SELECT	'robvolk',                    	'20020522',	  4000	UNION ALL
SELECT	'robvolk',                    	'20030102',	  6000	UNION ALL
SELECT	'robvolk',                    	'20030714',	  7000	UNION ALL
SELECT	'robvolk',                    	'20040305',	  8000	UNION ALL
SELECT	'robvolk',                    	'20050113',	  9000	UNION ALL
SELECT	'robvolk',                    	'20050614',	 10000	UNION ALL
SELECT	'robvolk',                    	'20061011',	 11000	UNION ALL
SELECT	'rockmoose',                  	'20040704',	     0	UNION ALL
SELECT	'rockmoose',                  	'20050811',	  2000	UNION ALL
SELECT	'rockmoose',                  	'20061116',	  3000	UNION ALL
SELECT	'rrb',                        	'20011018',	     0	UNION ALL
SELECT	'rrb',                        	'20040210',	  1000	UNION ALL
SELECT	'sakets_2000',                	'20071222',	     0	UNION ALL
SELECT	'snSQL',                      	'20060209',	     0	UNION ALL
SELECT	'snSQL',                      	'20061229',	  1000	UNION ALL
SELECT	'sodeep',                     	'20070913',	     0	UNION ALL
SELECT	'sodeep',                     	'20080410',	  1000	UNION ALL
SELECT	'sodeep',                     	'20080627',	  2000	UNION ALL
SELECT	'sodeep',                     	'20080918',	  3000	UNION ALL
SELECT	'sodeep',                     	'20081128',	  4000	UNION ALL
SELECT	'sodeep',                     	'20090224',	  6000	UNION ALL
SELECT	'spirit1',                    	'20040506',	     0	UNION ALL
SELECT	'spirit1',                    	'20040924',	  1000	UNION ALL
SELECT	'spirit1',                    	'20050119',	  2000	UNION ALL
SELECT	'spirit1',                    	'20050321',	  3000	UNION ALL
SELECT	'spirit1',                    	'20050910',	  4000	UNION ALL
SELECT	'spirit1',                    	'20060330',	  5000	UNION ALL
SELECT	'spirit1',                    	'20061031',	  6000	UNION ALL
SELECT	'spirit1',                    	'20070503',	  7000	UNION ALL
SELECT	'spirit1',                    	'20070803',	  8000	UNION ALL
SELECT	'spirit1',                    	'20071112',	  9000	UNION ALL
SELECT	'spirit1',                    	'20080425',	 10000	UNION ALL
SELECT	'spirit1',                    	'20081114',	 11000	UNION ALL
SELECT	'sunsanvin',                  	'20060826',	     0	UNION ALL
SELECT	'timmy',                      	'20010910',	     0	UNION ALL
SELECT	'timmy',                      	'20060828',	  1000	UNION ALL
SELECT	'tkizer',                     	'20021009',	     0	UNION ALL
SELECT	'tkizer',                     	'20030501',	  1000	UNION ALL
SELECT	'tkizer',                     	'20030805',	  2000	UNION ALL
SELECT	'tkizer',                     	'20031015',	  3000	UNION ALL
SELECT	'tkizer',                     	'20040106',	  4000	UNION ALL
SELECT	'tkizer',                     	'20040311',	  5000	UNION ALL
SELECT	'tkizer',                     	'20041130',	  9000	UNION ALL
SELECT	'tkizer',                     	'20050312',	 10000	UNION ALL
SELECT	'tkizer',                     	'20050721',	 11000	UNION ALL
SELECT	'tkizer',                     	'20051201',	 12000	UNION ALL
SELECT	'tkizer',                     	'20060419',	 13000	UNION ALL
SELECT	'tkizer',                     	'20060817',	 14000	UNION ALL
SELECT	'tkizer',                     	'20070105',	 15000	UNION ALL
SELECT	'tkizer',                     	'20070420',	 16000	UNION ALL
SELECT	'tkizer',                     	'20070915',	 17000	UNION ALL
SELECT	'tkizer',                     	'20071129',	 18000	UNION ALL
SELECT	'tkizer',                     	'20080410',	 19000	UNION ALL
SELECT	'tkizer',                     	'20080509',	 20000	UNION ALL
SELECT	'tkizer',                     	'20080722',	 21000	UNION ALL
SELECT	'tkizer',                     	'20080917',	 22000	UNION ALL
SELECT	'tkizer',                     	'20090105',	 23000	UNION ALL
SELECT	'tkizer',                     	'20090312',	 24000	UNION ALL
SELECT	'visakh16',                   	'20071116',	     0	UNION ALL
SELECT	'visakh16',                   	'20080204',	  1000	UNION ALL
SELECT	'visakh16',                   	'20080327',	  2000	UNION ALL
SELECT	'visakh16',                   	'20080508',	  3000	UNION ALL
SELECT	'visakh16',                   	'20080604',	  4000	UNION ALL
SELECT	'visakh16',                   	'20080620',	  5000	UNION ALL
SELECT	'visakh16',                   	'20080717',	  6000	UNION ALL
SELECT	'visakh16',                   	'20080811',	  7000	UNION ALL
SELECT	'visakh16',                   	'20080902',	  8000	UNION ALL
SELECT	'visakh16',                   	'20080925',	  9000	UNION ALL
SELECT	'visakh16',                   	'20081009',	 10000	UNION ALL
SELECT	'visakh16',                   	'20081027',	 11000	UNION ALL
SELECT	'visakh16',                   	'20081110',	 12000	UNION ALL
SELECT	'visakh16',                   	'20081125',	 13000	UNION ALL
SELECT	'visakh16',                   	'20081213',	 14000	UNION ALL
SELECT	'visakh16',                   	'20090108',	 15000	UNION ALL
SELECT	'visakh16',                   	'20090123',	 16000	UNION ALL
SELECT	'visakh16',                   	'20090210',	 17000	UNION ALL
SELECT	'visakh16',                   	'20090310',	 18000	UNION ALL
SELECT	'visakh16',                   	'20090511',	 19000	UNION ALL
SELECT	'visakh16',                   	'20090617',	 20000	UNION ALL
SELECT	'webfred',                    	'20080619',	     0	UNION ALL
SELECT	'webfred',                    	'20090608',	  1000

20061104 : Changed to universal date format, posts to int
20090621 : Changed to SQL 2008 data type

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-17 : 21:58:30

[code]
-- Fastest 0 to 1K
SELECT TOP 5
[Fastest 0 to 1K Poster] = s.member_name,
[Days] = DATEDIFF(DAY, s.date, t.date),
[Rate (Posts/DAY)] = CONVERT(decimal(10,4), (t.posts - s.posts) * 1.0 / DATEDIFF(DAY, s.date, t.date))
FROM @posts s INNER JOIN @posts t
ON s.member_name = t.member_name
WHERE s.posts = 0
AND t.posts = 1000
ORDER BY Days

/*
Fastest 0 to 1K Poster Days Rate (Posts/DAY)
------------------------------ ----------- ----------------
dinakar 60 16.6667
rmiao 73 13.6986
visakh16 80 12.5000
Peso 98 10.2041
Kristen 102 9.8039

(5 row(s) affected)
*/
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-17 : 21:59:05

[code]
-- Slowest 0 to 1K
SELECT TOP 3
[Slowest 0 to 1K Poster] = s.member_name,
[Days] = DATEDIFF(DAY, s.date, t.date),
[Rate (Posts/DAY)] = CONVERT(decimal(10,4), (t.posts - s.posts) * 1.0 / DATEDIFF(DAY, s.date, t.date))
FROM @posts s INNER JOIN @posts t
ON s.member_name = t.member_name
WHERE s.posts = 0
AND t.posts = 1000
ORDER BY Days DESC

/*
Slowest 0 to 1K Poster Days Rate (Posts/DAY)
------------------------------ ----------- ----------------
mike123 2328 .4296
mfemenel 2097 .4769
timmy 1813 .5516

(3 row(s) affected)
*/
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-17 : 21:59:44

[code]
-- Fastest 1K leap
SELECT TOP 20
[Fastest 1K Poster] = s.member_name,
[1K Leap] = CONVERT(varchar(5), CONVERT(int, s.posts)) + ' - ' + CONVERT(varchar(5), CONVERT(int, t.posts )),
Days = DATEDIFF(DAY, s.date, t.date),
[Rate (Posts/DAY)] = CONVERT(decimal(10,4), (t.posts - s.posts) * 1.0 / DATEDIFF(DAY, s.date, t.date))
FROM @posts s INNER JOIN @posts t
ON s.member_name = t.member_name
WHERE (t.posts - s.posts) = 1000
ORDER BY Days

/*
Fastest 1K Poster 1K Leap Days Rate (Posts/DAY)
------------------------------ ------------- ----------- ----------------
visakh16 9000 - 10000 14 71.4286
visakh16 11000 - 12000 14 71.4286
visakh16 12000 - 13000 15 66.6667
visakh16 15000 - 16000 15 66.6667
visakh16 4000 - 5000 16 62.5000
visakh16 10000 - 11000 18 55.5556
visakh16 13000 - 14000 18 55.5556
visakh16 16000 - 17000 18 55.5556
Kristen 14000 - 15000 20 50.0000
visakh16 7000 - 8000 22 45.4545
visakh16 8000 - 9000 23 43.4783
Peso 4000 - 5000 25 40.0000
visakh16 6000 - 7000 25 40.0000
Peso 10000 - 11000 26 38.4615
visakh16 14000 - 15000 26 38.4615
visakh16 3000 - 4000 27 37.0370
visakh16 5000 - 6000 27 37.0370
Peso 3000 - 4000 28 35.7143
visakh16 17000 - 18000 28 35.7143
Peso 7000 - 8000 29 34.4828

(20 row(s) affected)
*/
[/code]

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-10-17 : 22:01:42

quote:

Originally posted by khtan



/*
Slowest 0 to 1K Poster         Days        Rate (Posts/Day) 
------------------------------ ----------- ---------------- 
timmy                          1813        .5516
RickD                          1710        .5848
Wanderer                       1666        .6002
*/

Do you really need to rub it in?????

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-17 : 22:03:36

quote:

Originally posted by timmy

quote:

Originally posted by khtan



/*
Slowest 0 to 1K Poster         Days        Rate (Posts/Day) 
------------------------------ ----------- ---------------- 
timmy                          1813        .5516
RickD                          1710        .5848
Wanderer                       1666        .6002
*/

Do you really need to rub it in?????

Very sorry. It's not my intention

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-17 : 22:19:06

[code]
SELECT TOP 5
[Highest Rate member_name] = s.member_name,
[Rate (Posts/DAY)] = CONVERT(decimal(10,4), (t.posts - s.posts) * 1.0 / DATEDIFF(DAY, s.date, t.date))
FROM @posts s INNER JOIN @posts t
ON s.member_name = t.member_name
WHERE s.posts = 0
AND t.posts <> 0
AND t.posts = (SELECT MAX(posts) FROM @posts x WHERE x.member_name = s.member_name)
ORDER BY [Rate (Posts/DAY)] DESC

/*
Highest Rate member_name Rate (Posts/DAY)
------------------------------ ----------------
visakh16 34.5423
Peso 21.5633
dinakar 16.6667
rmiao 14.5228
Kristen 11.6822

(5 row(s) affected)
*/
[/code]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-18 : 04:03:14

thatz an interesting stats.. are you competiting with Kristen.. :-)

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-18 : 09:55:44

Tan, you are real competitor to Kristen

Thanks for your Analysis

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-18 : 11:13:16

quote:
Originally posted by madhivanan

Tan, you are real competitor to Kristen

Thanks for your Analysis

Madhivanan

Failing to plan is Planning to fail

Not competitor-lah

quote:

khtan is the next dyslexic Kristen
dyslexic khtan is the next Kristen

Whatever that means

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-18 : 11:38:06

>>Not competitor-lah

Yes Lah

I know that in Singapore Lah is used mostly frequently

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-18 : 11:39:53

quote:
Originally posted by madhivanan

>>Not competitor-lah

Yes Lah

I know that in Singapore Lah is used mostly frequently

Madhivanan

Failing to plan is Planning to fail

You have been to Singapore ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-18 : 11:46:15

>>You have been to Singapore ?

Yes Lah

I am visiting again soon

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-18 : 12:57:00

quote:

Originally posted by khtan


-- Fastest 1K leap
select	top 3
	[Fastest 1K Poster] 	= s.poster, 
	[1K Leap]		= convert(varchar(5), convert(int, s.posts * 1000))  + 'K - '  + convert(varchar(5), convert(int, t.posts * 1000)) + 'K',
	Days			= datediff(day, s.date, t.date),
	[Rate (Posts/Day)]	= convert(decimal(10,4), (t.posts - s.posts) * 1000000 / datediff(day, s.date, t.date))
from	@posts s inner join @posts t
	on	s.poster	= t.poster
where	(t.posts - s.posts)	= 0.001
order by Days
/*
Fastest 1K Poster              1K Leap         Days        Rate (Posts/Day) 
------------------------------ --------------- ----------- ---------------- 
Peso                           1K - 2K         32          31.2500
Madhi                          2K - 3K         46          21.7391
Madhi                          3K - 4K         49          20.4082
*/

Peter, you have to start taking it easy.

You're really making the rest of us look bad.

Pretty impressive, though, almost a thousand posts in one month.

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-10-18 : 13:41:10

quote:
Originally posted by Michael Valentine Jones

Peter, you have to start taking it easy.

You're really making the rest of us look bad.

Pretty impressive, though, almost a thousand posts in one month.

CODO ERGO SUM

Peter, I hope your performance at your day job is not suffering because of your sqlteam "duties"

EDIT:
I'm not worried about Madhi becuase how much time does it take to say:
"refer this", "refer that", "format it in the front end"

Just kiddin', M.

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-18 : 14:09:27

Not at all!
Have been running pre-written scripts for 4 monts now. For conversion issues. It is only when the scripts fail, I have to do some actual work.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-18 : 15:08:25

quote:
Originally posted by Peso

Not at all!
Have been running pre-written scripts for 4 monts now. For conversion issues. It is only when the scripts fail, I have to do some actual work.

Peter Larsson
Helsingborg, Sweden

Do they need any help? I'm very good at clicking the QA Run button.

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-10-18 : 16:35:50

Me too! Except sometimes I hit the "play" button more than once. btw, anyone know how I avoid the PK constraint violation when that happens?

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-18 : 23:01:53

Just curious, i change to see the TOP 10 fastest poster. Half are Madhi. Amazing !


/* 
Fastest 1K Poster              1K Leap         Days        Rate (Posts/Day) 
------------------------------ --------------- ----------- ---------------- 
Peso                           1K - 2K         32          31.2500
Madhi                          2K - 3K         46          21.7391
Madhi                          3K - 4K         49          20.4082
Madhi                          1K - 2K         53          18.8679
Madhi                          4K - 5K         58          17.2414
Brett                          1K - 2K         60          16.6667
Spirit1                        2K - 3K         61          16.3934
Kristen                        4K - 5K         62          16.1290
Kristen                        5K - 6K         63          15.8730
Madhi                          6K - 7K         65          15.3846
*/

/*
Fastest 1K Poster 1K Leap Days Rate (Posts/Day)
------------------------------ --------------- ----------- ----------------
Peso 4000K - 5000K 25 40.0000
Peso 3000K - 4000K 28 35.7143
Peso 1000K - 2000K 32 31.2500
Peso 5000K - 6000K 40 25.0000
Peso 2000K - 3000K 41 24.3902
Madhi 2000K - 3000K 46 21.7391
Madhi 3000K - 4000K 49 20.4082
Madhi 1000K - 2000K 53 18.8679
Madhi 4000K - 5000K 58 17.2414
Brett 1000K - 2000K 60 16.6667
*/


Fastest 1K Poster              1K Leap         Days        Rate (Posts/Day) 
------------------------------ --------------- ----------- ---------------- 
Peso                           4000K - 5000K   25          40.0000
Peso                           3000K - 4000K   28          35.7143
Peso                           1000K - 2000K   32          31.2500
Peso                           5000K - 6000K   40          25.0000
Peso                           2000K - 3000K   41          24.3902
Peso                           6000K - 7000K   42          23.8095
Madhi                          2000K - 3000K   46          21.7391
Madhi                          3000K - 4000K   49          20.4082
Madhi                          1000K - 2000K   53          18.8679
Madhi                          4000K - 5000K   58          17.2414