10 minute TweetWasters Clone with Tweet-SQL

Today I came across Tweetwasters which i basically a little bit of fun showing you approximately how much time you've spent on Twitter.

Here's my profile...



Here's a quick 10 minute script on how to clone this using Tweet-SQL. Just modify the value set in @username and you're good to go

DECLARE @username VARCHAR(20),
  @xml XML,
  @handle INT,
  @statuses_count INT,
  @seconds INT,
  @minutes INT,
  @hours FLOAT,
  @days FLOAT;

-- Set username here!
SET @username = 'rhyscampbell';

-- Turn off resultsets from Tweet-SQL
EXEC dbo.tweet_cfg_resultset_send 0;

-- Get the Twitter users' info
EXEC dbo.tweet_usr_show @username, null, @xml OUTPUT;

-- Create an xml document
EXEC sp_xml_preparedocument @handle OUTPUT, @xml;

-- Get the statuses count
SELECT @statuses_count = statuses_count
FROM OPENXML (@handle, '/user', 2)
WITH
(
 statuses_count INT
);

-- Remove the xml doc from memory
EXEC sp_xml_removedocument @handle;

-- Turn on resultsets as appropriate
EXEC dbo.tweet_cfg_resultset_send 1;

-- Do our calculations
SET @seconds = (@statuses_count * 30);
SET @minutes = ROUND((@seconds / 60), 0);
SET @hours = ROUND((@minutes / 60.00), 2);
SET @days = ROUND((@hours / 24.00), 2);

-- Show the results
SELECT @username + ' has ' + CONVERT(VARCHAR, @statuses_count) + ' total tweets ' AS TweetWaster UNION ALL
SELECT 'and assuming they spent an average of 30 seconds per tweet they''ve spent ' UNION ALL
SELECT CONVERT(VARCHAR, @seconds) + ' seconds or ' UNION ALL
SELECT CONVERT(VARCHAR, @minutes) + ' minutes or ' UNION ALL
SELECT CONVERT(VARCHAR, @hours) + ' hours or ' UNION ALL
SELECT CONVERT(VARCHAR, @days) + ' days using Twitter!';

and SSMS will return something like the following...



Get your own Twitter app up and running fast by downloading Tweet-SQL now!