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


KH

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]


KH

Go to Top of Page

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]


KH

Go to Top of Page

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]


KH

Go to Top of Page

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
*/



KH




Do you really need to rub it in?????
Go to Top of Page

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
*/



KH




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



Very sorry. It's not my intention


KH

Go to Top of Page

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]


KH

Go to Top of Page

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/
Go to Top of Page

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
Go to Top of Page

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


KH

Go to Top of Page

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
Go to Top of Page

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 ?


KH

Go to Top of Page

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
Go to Top of Page

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
*/



KH





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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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



KH

Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-19 : 05:12:21
i'm in that list too?? amazing...
ahh.. the good old days



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-10-19 : 05:58:44
mmm.... looking at the trends - madhi seems to be slowing down - Old Age maybe?



Duane.
Go to Top of Page
    Next Page

- Advertisement -