Archive

Archive for the ‘SQL’ Category

SQL create a delimited string from a flat file

February 24th, 2015 No comments

I’ve recently written a post on how to split a delimited string in SQL, now I need to do the reverse and take the flat table and create a delimited string.

Fortunately creating a delimited string is far simpler than splitting a delimited string.

Assuming we have a flat table called CountryUsers with the following structure:

Country User
UK Bob
UK Fred
UK Bill
USA Mike
USA Lou
USA Carol
USA Sam

To convert this table into a 2 record table, one for each country, with a comma delimited string of users, run the following SQL: Read more…

Categories: SQL Tags:

SQL split string by a delimiter to create new rows

February 4th, 2015 No comments

I’ve just spent a reasonable amount of time digging through the internet to work out how to take a dataset containing a delimited string and then split the string to create new records. For example you could have a table called Users:

Country User
UK Bob|Fred|Bill
USA Mike|Lou|Carol|Sam

What you want to do is split the string by the delimiter and create new rows:

Country User
UK Bob
UK Fred
UK Bill
USA Mike
USA Lou
USA Carol
USA Sam

Apparently there are many different ways to create this but the neatest I came across was to use a combination of Common Table Expression (CTE), Table Function and Cross Apply. Read more…

Categories: SQL Tags: ,

Quickest way to remove the time from a datetime in SQL

November 10th, 2011 No comments

In an earlier post I showed some ways to remove the time portion from a date, but the obvious follow on from that is which is fastest. To test I ran both versions on a dataset of 1.9m rows of data.

The DATE datatype returned the records in 15m 35s = 935s

Getting the data using the DATEADD and DATEDIFF technique took 16m 50s = 1020s

In this example using the DATE datatype was 10% faster – which is significant. From this test the best way to remove the time from a datetime in SQL is by using the DATE datatype.

I’m also aware of techniques to remove the time using text functions. DO NOT USE THESE – they are not handled in an optimised way by the SQL engine and will be far slower than the 2 techniques I have documented.

Categories: SQL Tags: ,

Remove a line feed / carriage return from a string

August 23rd, 2011 3 comments

If you ever have to deal with long strings of freetext it’s likely you’ve come across random line feed and carriage returns in that text causing you headaches when it comes to manipulating the text.

Once you know how it’s quite a simple problem to deal with. This article describes how to remove the carriage returns and line feeds in SQL, Excel and Access. Read more…

Categories: Access, Excel, SQL Tags: , ,

Link Tableau to SSRS

I’ve had a few visitors coming to the site searching for how to link Tableau to SSRS so I thought I’d write a quick post on how I’ve done it.

You need to make use of the URL action in Tableau, which you use in a dashboard. To be wanting to know how to link from Tableau to SSRS I am asuming a basic familiarity with Tableau dashboards. You will need a dashboard to make the link work.

To begin you need to create a dashboard and put whatever graphs, etc, you want on it. Next we need something on the dashboard from which to create our URL Action to take us to our SSRS report. Unfortunately there aren’t any controls such as buttons in Tableau so we need to create a new worksheet to act as our ‘button’. Read more…

SQL First Day of Month

May 19th, 2011 1 comment

This post describes how to find the first day of a month in SQL.

The code is: DATEADD(mm,DATEDIFF(mm,0,DateToRemoveTime),0)

A full explanation of how this works is in a previous post describing how to remove the time portion of a datetimein SQL as the concept is exactly the same except where removing the time uses the day (dd) in the above date functions, to get the first day of the month you use the month (mm) instead.

Categories: SQL Tags: ,

SQL Calculating the row number

To count the row number in SQL there’s a useful little in built SQL function you need to use, row_number()

This enables you to number all rows and restart the row numbering part way a dataset. Note this is different to the SQL rank function.

This is a very useful thing is you want to select the top X rows by specified criteria. For example the top 10 selling products per country or the top 5 sales people per region.

The syntax of this is ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> ), which for me is not super helpful without some examples. (For more details you can see the row_number function on the Microsoft website here: http://msdn.microsoft.com/en-us/library/ms186734.aspx)

Here is some demo data to run through some common scenarios. You can run this code in SQL Server Management Studio (SSMS) to create the test data.

CREATE TABLE #Sales
    (
    Country varchar(10)
    ,Person varchar(2)
    ,Sales decimal (18,2)
    )

insert into #Sales values (‘UK’,’AW’,8329)
insert into #Sales values (‘UK’,’KS’,542)
insert into #Sales values (‘UK’,’NE’,942)
insert into #Sales values (‘UK’,’LT’,7358)
insert into #Sales values (‘UK’,’AP’,4262)
insert into #Sales values (‘France’,’ME’,952)
insert into #Sales values (‘France’,’DD’,8802)
insert into #Sales values (‘France’,’WQ’,2331)
insert into #Sales values (‘France’,’PD’,544)
insert into #Sales values (‘France’,’ML’,2418)
insert into #Sales values (‘Italy’,’MN’,5426)
insert into #Sales values (‘Italy’,’DN’,7982)
insert into #Sales values (‘Italy’,’IA’,499)
insert into #Sales values (‘Italy’,’EY’,1034)
insert into #Sales values (‘Italy’,’LA’,5429)

Example 1: Number each row with the highest selling person at the top

This is quite simple, you just need to order by sales in the Over part of the clause.

select Person
    ,Country
    ,Sales
    ,ROW_NUMBER() over (order by sales desc) as RowNumber
from #Sales

Example 2: Number each row with the highest selling person at the top doing this for each country

Slightly more complex, this is where we introduce the Partition By into to Over part of the clause

select Country
    ,Person
    ,Sales
    ,ROW_NUMBER() over (partition by country order by sales desc) as RowNumber
from #Sales

Example 3: Only select the top 3 sales people from each country in term of sales amount

Here we also need to use the same code as Example 2 and we just query that result set

select Country
    ,Person
    ,Sales
    ,RowNumber
from
    (
    select Country
        ,Person
        ,Sales
        ,ROW_NUMBER() over (partition by country order by sales desc) as RowNumber
    from #Sales
    )a
where RowNumber < 4
order by Country, RowNumber

Quite straightforward once you understand the syntax. Note the Row_number() function can’t be used in the WHERE clause or HAVING hence I had to query a query in Example 3.

Categories: SQL Tags:

Remove the time from a datetime in SQL

April 27th, 2011 No comments

There are a few ways to remove the time from a datetime in SQL. This article descibes a couple of them.

This is the code: DATEADD(dd,0,DATEDIFF(dd,0,DateToRemoveTime))

I’ll explain how this works as it was quite confusing initially.

Part 1: DATEDIFF(dd,0,DateToRemoveTime)

What this is doing is finding the number of whole days (without time) from the beginning of time (0) to the date you want to remove the time (DateToRemoveTime).

Part 2: DATEADD(dd,0,Part1)

This is taking the number of whole days since time began which was calculated in Part 1 and adding this to the beginning of time (0).

As an example let’s say it’s 10.03 on the 14 Jan in Year 0. In SQL date format this would appear as something like 0000:01:14 10:03:00

Part 1 would see how many whole days have passed since the beginning of time, 0, and the 14 Jan in Year 0. This is 14 days. Part 2 would add these 14 days to the beginning of time – which would give the result 0000:01:14 00:00:00 – which is the date without the time.

Edit

In SQL 2008 there’s another way to do this using the DATE datatype. The syntax is very simple: CAST(DateToRemoveTime as DATE)

The next obvious question is which is the best method to use to remove the time portion from a date.

Categories: SQL Tags: ,