blogs.conchango.com

Browse by Tags

• Analysing performance
• Analytic functions
• case statement
• cowbell
• Date
• Date functions
• Date range
• Formatting code
• join
• last_day
• List of dates
• LTRIM
• MTD
• next_day
• Openquery
• Oracle
• order by
• partition by
• PL/SQL
• PL-SQL
• Previous row
• Query performance
• Rownum
• RTRIM
• self join
• SQL Server
• SQL Server 2005
• TOAD
• Top N
• TRIM
• T-SQL
• Variables
• Week Ending
• where clause
• White space
• YTD

• SQL tutorials and learning resources

  Following a few requests, here are some sites you may find useful for supporting your SQL learning: General http://www.stickyminds.com/ http://www.thefreecountry.com/documentation/onlinesql.shtml (free online tutorials) T-SQL http://sqlcourse.com/ (free Read More... Posted 14 November 2007 16:26 by James.Pipe | 0 Comments Filed under: PL/SQL, PL-SQL, SQL, T-SQL, SQL Server

• SQL: Using the Trim function to remove white space from a field

  Today I was looking at a table with an apparantly empty column and discovered that the field is actually populated with blank spaces - Fortunately this field is of no interest to me and I noticed it purely by chance when I pasted an extract of the dat Read More... Posted 14 August 2007 16:19 by James.Pipe | 12 Comments Filed under: PL/SQL, SQL, T-SQL, Formatting code, LTRIM, TRIM, RTRIM, White space

• SQL Quick Tip: Using Top N to browse a database

  If you ever need to browse through a database, and dip in and out of tables to see what the data looks like, but you have very little data to go on, then you will be aware that simply running a select * against each table can be a time consuming and inefficient Read More... Posted 09 August 2007 23:23 by James.Pipe | 2 Comments Filed under: PL/SQL, SQL, T-SQL, Rownum, Top N

• Automatically format your SQL statements

  A colleague forwarded me this link today, which I thought would be beneficial to anyone who has to write a lot of SQL statements. The tool will automatically format the statements to 'beautify' them, so no more excuses for 'ugly' code! Read More... Posted 09 August 2007 19:26 by James.Pipe | 0 Comments Filed under: PL/SQL, SQL, T-SQL, Formatting code

• SQL Server 2005: Passing variables into an OPENQUERY argument

  Today I ran into a blocker when trying to do this. Having spent a reasonable amount of time taking all of my sql statements out of the OPENQUERY argument and putting them into a string variable I then found it didn't work. The reason (from BOL ) it Read More... Posted 28 June 2007 17:32 by James.Pipe | 18 Comments Filed under: SQL, T-SQL, SQL Server 2005, Variables, cowbell, Openquery, SQL Server

• PL/SQL Calendar table

  In addition to the T-SQL calendar table , which I have posted seperately, I went the whole hog and figured out the equivalent for Oracle. The only value I couldn't work out was the millisecond, which is not present on the standard date type in Oracle, Read More... Posted 24 April 2007 16:00 by James.Pipe | 4 Comments Filed under: PL/SQL, PL-SQL, SQL, Date functions, Date, Date range

• T-SQL Calendar table

  After experimentation with various datepart and datename methods following my most recent blog post , I was able to identify many if not all the necessary components for constructing a calendar table.To do this I have combined these functions with the Read More... Posted 24 April 2007 15:51 by James.Pipe | 3 Comments Filed under: SQL, T-SQL, Date functions, Date, Date range

• PL/SQL and T-SQL for Week Ending Date

  Recently my colleague challenged me to find a method for identifying the week ending date for the week which any given date belongs to; i.e. say for a gven date which happens to be a Tuesday, what is the date of the end of the week? In this example, week Read More... Posted 24 April 2007 15:01 by James.Pipe | 9 Comments Filed under: PL/SQL, List of dates, SQL, T-SQL, next_day, Date functions, Date, Date range, Week Ending

• T-SQL: Common date ranges; Daily, Weekly, Monthly, Yearly, Last 12 Months

  As promised in my original post on PL/SQL date ranges previously, below is a sql statement which can be used preceeding most queries in order to apply common date range lookups to the data the query returns. SQL Server Management Studio (SSMS) doesn't Read More... Posted 01 March 2007 00:34 by James.Pipe | 2 Comments Filed under: SQL, T-SQL, case statement, Date functions, Date, Date range, MTD, YTD

• SQl Server 2005 - more from Rowland-Jones

  For those that read my query performance addendum you will know that I was posting a contribution emailed to me by my colleague James Roland-Jones. Well JRJ has now started to post his own thoughts on SQL Server 2005 and SSIS, so if you would like to Read More... Posted 20 February 2007 18:46 by James.Pipe | 0 Comments Filed under: SQL, SQL Server 2005

• PL-SQL: Common date range lookups; Daily, Weekly; MTD; YTD; Last 12 Months

  As part of my current role as a tester I am often required to write the same basic expressions again and again in my queries, most noticeably to bring back data for specific date ranges, therefore for the benefit of anyone else out there who has to do Read More... Posted 10 February 2007 04:00 by James.Pipe | 8 Comments Filed under: Oracle, PL-SQL, SQL, next_day, last_day, Date functions, Date, Date range, MTD, YTD

• PL SQL: Passing variables in TOAD for Oracle

  I regularly need to pass variables into my queries, and when running multiple tests, it helps to have a way to change these variables easily, preferably, without having to go through the query each time and change the sql. Fortunately for me TOAD (Tool Read More... Posted 10 February 2007 03:30 by James.Pipe | 2 Comments Filed under: Oracle, PL-SQL, SQL, Date, TOAD

• SQL Server 2005 Query Performance addendum

  In response to my recent post about analyzing query performance in SQL server 2005, my colleague Mr Rowland Jones emailed me a couple of additional points around best practice, which will further aid query performance. Here are the points he makes (in Read More... Posted 09 February 2007 19:09 by James.Pipe | 2 Comments Filed under: SQL Server 2005, Analysing performance, Query performance, SQL, T-SQL

• Selecting the last weekday in a given month

  In one of my frequent trips through Google on the search for how to do something (I forget what it was I was looking for on this particular occasion), I found myself, as I often do, being directing to various contributions from posts on life after coffee.com Read More... Posted 03 February 2007 20:33 by James.Pipe | 1 Comments Filed under: Oracle, PL-SQL, SQL, case statement, next_day, last_day, Date functions

• Analysing Query Performance in SQL Server 2005

  Sometimes I will be writing a query and will be faced with the option of writing it in a number of different ways, each of which will give me the result I’m looking for, and the question I always ask is which is most efficient? Now ‘most efficient’ Read More... Posted 03 February 2007 01:29 by James.Pipe | 1 Comments Filed under: join, SQL, T-SQL, where clause, SQL Server 2005, Analysing performance, Query performance