Friday 4 October 2013

All Sci-Fi Spaceships Known to Man

In theory "All Sci-Fi Spaceships Known to Man", it is missing the ship from Space Balls though. I guess it wouldnt of fit on the scale.

All Sci-Fi Spaceships Known to Man
Explore more infographics like this one on the web's largest information design community - Visually.

Saturday 4 May 2013

How to run your best 5k

Two reasons for this one. Firstly I'm loving Data Visualisations at the moment, they are a great way to put across a message or visualise the data. This one is not about the data but to convey the message of how to run 5 kilometers.
The other reason is that I run and to actually see tips like this is very helpful. Maybe it can help me reduce the number of injuries I get.

Shooting Hoops: Making $$$

I must I like this one a bit more because of my love of Basketball/sports in general, but still the amount of information put into is great.
There is so much great data on Basketball I love it.
If only I could work with sports data. An excuse to do what I have been doing since I was a boy as job, I think that would be my dream job.


Thursday 25 April 2013

In 60 Seconds

I'm really impressed at how well people are visualizing data theses days.
Below is another great example, it makes you want to read each fact.
I have to find a way to improve how I do the same, if I can get anywhere near this level I'll be very happy.


Row Counts for each table on Database from Metadata

Im sure a lot of people do this already but for those of you who dont there is an easy way to find the row counts of all of your tables without having to wait for count(*) commands.

SQL Server saves this information for you.

Just run the below on each database you want to check OR if you want you can use the sp_foreachDB I mentioned in a previous post (Run SQL On Each Database) to run it on multiple databases. If I was to do that I would pump the data into a temp table each time and then return the results from it.
SELECT
 DB_NAME() as DatabaseName, 
        st.Name,
 sc.Name as ''Schema'',
        SUM(CASE WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows 
            ELSE 0 END) AS Rows,
 st.Modify_Date
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.tables st ON st.object_id = p.Object_ID
INNER JOIN sys.schemas sc on st.schema_id = sc.schema_id
GROUP BY st.Name, sc.Name, st.Modify_Date
ORDER BY rows desc, sc.Name, st.Name

Saturday 13 April 2013

Big Data analysis allows businesses and governments to mine your personal details

A good article on what some organisations are doing in the way of analytics. Whenever you provide any information to some one or interact in a way where they know who you are even if it is just with a credit card they can track you.

Big Data analysis allows businesses and governments to mine your personal details


Sunday 7 April 2013

SSMS Tools Pack

If you haven't already got SSMS Tools Pack I highly recommend it.

I use it a lot for the SQL Snippets which allows short cuts for example stf<ENTER> will return Select Top 100 FROM in the query window. All the Snippets are customisable and you create your own.

Running Custom Scripts from the Object Explorer is great too, I have scripts I want to run at a server level so I just click on the Server in Object Explorer and then just choose the script I want to run.

A lot of other really useful tools too, just makes life that little bit easier.

Sourcing Data from Active Directory



Something I've found useful in the past is being able to source user details from Active Directory.
Its nice being able to link the User Names from your systems up to actual Names of the users when providing data.
It is also handy when you want to find which users have certain security access. For example who has access to Database X.

/*
--Check if Ad Hoc Distributed Queries is Visible/Turned on
sp_configure

--Make Visible if it isnt
sp_configure 'show advanced options', 1
reconfigure

--Turn it on
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
*/

--Return All Active Directory Users
--Replace ABC.DEF with your active directory server name

select
*
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT Title, Department, Mail, DisplayName, Sn, GivenName, Cn
FROM ''LDAP://ABC.DEF'' where objectClass = ''User'' AND objectClass<>''computer'' '
)

--Find All Active Directory Groups
--Replace ABC.DEF with your active directory server name
select *, substring(AdsPath, charindex('CN=', adspath), 300) as GroupName , substring(AdsPath, 0, charindex('CN=', adspath) - 1) as Domain FROM OPENROWSET('ADSDSOObject', 'adsdatasource;', 'SELECT AdsPath, name FROM ''LDAP://ABC.DEF'' WHERE objectCategory=''Group'' ' ) --Find Users from X Active Directory Group
--Replace 'YourADGroup' with the name of the Group you are after

--Replace ABC.DEF with your active directory server name
--Also again in the DC=ABC,DC=DEF section
select * FROM OPENROWSET('ADSDSOObject', 'adsdatasource;', 'SELECT Cn FROM ''LDAP://ABC.DEF'' WHERE memberOf=''CN=YourADGroup,OU=Security,OU=Groups,DC=ABC,DC=DEF'' ' )

Monday 1 April 2013

The Global State of Mobile Marketing

I wonder how the surge of Android phones and the release of Windows 8 will impact these figures over the next 12 months.

The Global State of Mobile Marketing [INFOGRAPHIC] - ExactTarget Infographic
Embedded from ExactTarget

Friday 29 March 2013

Extended Soundex

I think Soundex is great to be able to compare two names and find possible matches. One of the limitations though that I have found is that it will only return 4 characters which can mean it only checks the start of the name. This can be good when you are comparing two names and one has been shortened but what about longer names?

Anyway so I created the below one day to help out someone when they wanted to compare names and the traditional soundex wasnt enough.

It uses the same principles as Soundex but it searches through all of the name.

Wikipedia has a good explanation of how the traditional Soundex works.

Of course this is just a basic example, Id suggest to use it for larger pieces you place it into a User Defined Table Function perhaps Or a Stored Procedure which accepts tables - I believe you can do that now but haven't tried it myself yet.


--Create my Name List
IF OBJECT_ID('tempdb..#NameList') IS NOT NULL 
DROP TABLE #NameList
CREATE TABLE #NameList (ID INT IDENTITY, Surname varchar(120), GivenNames varchar(120), GivenNameLength int)
INSERT INTO #NameList (Surname, GivenNames, GivenNameLength)
Select 'Dummy 'Surname, 'Robert William Francis' as GivenNames, 22 as GivenNameLength
Declare @NameID as int = 1
Declare @Letter as int = 1

Declare @GivenNameLength as int
Declare @LetterToCheck as varchar(1)
Declare @SoundexValue as varchar(50)
Declare @Names as bigint = 0
DECLARE @ReturnList TABLE (Surname varchar(120), GivenNames varchar(120), SoundexValue varchar(50))
--Determine the number of Names to check
Select @Names = COUNT(*) from #NameList
while @NameID <= @Names
 begin 
 --The first letter of the Name is put straight in, grab the length to know how many loops to perform
 select @GivenNameLength = GivenNameLength, @SoundexValue = LEFT(GivenNames, 1)  
 from #NameList
 where ID = @NameID
 Select @Letter = 2
 --For each letter in the Name assign a Soundex Value
 while @Letter <= @GivenNameLength 
  begin
  select @LetterToCheck = UPPER(SUBSTRING(GivenNames, @Letter, 1)) from #NameList
  where ID = @NameID
 
  --Assign Soundex Values
  select @SoundexValue = @SoundexValue + case when @LetterToCheck IN ('b', 'f', 'p', 'v') then '1'
     when @LetterToCheck IN ('c', 'g', 'j', 'k', 'q', 's', 'x', 'z') then '2'
     when @LetterToCheck IN ('d', 't') then '3'
     when @LetterToCheck IN ('l') then '4'
     when @LetterToCheck IN ('m', 'n') then '5'
     when @LetterToCheck IN ('r') then '6'
     --when @LetterToCheck IN ('h', 'w') then 'hw'
     when @LetterToCheck IN ('a', 'e', 'i', 'o', 'u') then 'v'
     else '' end
  Select @Letter = @Letter + 1
  end
  --Return a record
  INSERT INTO @ReturnList
  select Surname, GivenNames, 
  --If there are two letters with the same number next to each other return only 1
  --Unless there is a vowel in which case they remain
  REPLACE( 
  REPLACE(
  REPLACE(
  REPLACE(
  REPLACE(
  REPLACE(
  Replace(@SoundexValue
  , '11', '1') 
  , '22', '2') 
  , '33', '3') 
  , '44', '4') 
  , '55', '5') 
  , '66', '6') 
  , 'v', '') as NewSoundexValue
  from #NameList
  where ID = @NameID
 select @NameID = @NameID + 1
 end
SELECT * FROM @ReturnList

Sunday 24 March 2013

Hans Rosling Shows the best stats you've ever seen

I love this TED Presentation by Hans Rosling, it makes you firstly think about the facts he is showing but then from a person who works with data about the difference between simply providing data and providing insight to get a point across.

Hans Rosling Shows the best stats you've ever seen

Creating a Calendar View

I like to put commonly used scripts into Views/Stored Procedures etc. It means I dont have to re-write out the logic each time, I know that there has been a consistent use of the logic and if there is a change needed to the logic I make it once rather than many times across all of my scripts/reports etc. Yeah seems obvious to do but Ive seen a lot of developers who dont bother.

Now this is view is related of course to my previous post Creating A Calendar Table but I find that having an independent view that you can use in Reports or reused SQL Scripts very helpful.

What better way to set default date values than referring to a consistent view AND if for some reason there is a change in regards to the definition of say the last day of the Previous Month - maybe it is now the last Monday for example instead of the last actual day you can change it within the view once and all of your reports/scripts are instantly updated. No more having to go through them all updating the logic.

Depending on where you are located you may want to change around a few of these values for example the Financial Years which wont apply to all.



-- =============================================

-- Create View template
-- =============================================
USE
ABC
-- Adjust accordingly

GO
IF
object_id(N'dbo.vDateDetails', 'V') IS NOT NULL


DROP VIEW dbo.vDateDetails
GO
CREATE
VIEW dbo.vDateDetails AS
SELECT
getdate
() AS [Now]
,

DATEADD(d, DATEDIFF(d,0,getdate()), 0) as Today
,

DATEADD(d, DATEDIFF(d,0,getdate()), -1) as Yesterday
,

CONVERT (varchar, getdate(), 112) As IntDate
,

DAY(getdate()) As [DayOfMonth]
,

DATEPART(dayofyear, getdate()) as [DayOfYear]
,

Month(getdate()) as MonthOfYear
,

DATENAME(month, getdate()) AS [MonthName]
,

YEAR(getdate()) as [Year]
,

case when Month(getdate()) > 6 then
right(cast(YEAR(getdate()) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 1, getdate())) as varchar(4)), 2)
else
right(cast(YEAR(dateadd(yy, -1, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 0, getdate())) as varchar(4)), 2)
end as CurrentFinancialYear
,

case when Month(getdate()) > 6 then
right(cast(YEAR(dateadd(yy, -1, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 0, getdate())) as varchar(4)), 2)
else
right(cast(YEAR(dateadd(yy, -2, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, -1, getdate())) as varchar(4)), 2)
end as PreviousFinancialYear
,

case when Month(getdate()) > 6 then
right(cast(YEAR(dateadd(yy, 1, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 2, getdate())) as varchar(4)), 2)
else
right(cast(YEAR(dateadd(yy, 0, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 1, getdate())) as varchar(4)), 2)
end as NextFinancialYear
,

DATEPART(quarter, getdate()) as CalQuarter
,

DATEPART(week, getdate()) as WeekOfYear
,

DATEPART(weekday, getdate()) as [Weekday]
,

DATENAME(weekday, getdate()) AS [WeekDayName]
,

DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) as MondayOfWeek
,

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0)) as LastDayOfPreviousMonth
,

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) as LastDayOfCurrentMonth
,

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+2,0)) as LastDayOfNextMonth
,

DATEADD(mm, DATEDIFF(m,0,getdate()),0) as FirstDayOfCurrentMonth
,

DATEADD(mm, DATEDIFF(m,0,getdate())+1,0) as FirstDayOfNextMonth
,

DATEADD(mm, DATEDIFF(m,0,getdate())-1,0) as FirstDayOfPreviousMonth
,

DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) as FirstDayOfCurrentQuarter
,

DATEADD(s,-1,DATEADD(qq, DATEDIFF(q,0,getdate())+1,0)) as LastDayOfCurrentQuarter