http://www.sqlfrenzy.com The Smart Guide to SQL Mon, 30 Aug 2010 04:00:00 +0000 http://backend.userland.com/rss092 en Nothing earth-shattering here, I was just helping out a colleague with this so I thought I'd post up the example I gave him. -- sample table: create table People (     Person varchar(1) primary key,     City varchar(10),     Age int ) go -- with some sample data: insert into People select 'A','Boston',23 union all  -- odd # select 'B','Boston',43 union ... http://www.sqlfrenzy.com/2010/08/how-to-calculate-median-in-sql-server-2005-or-sql-server-2008/ Another great post SQL-related post over at TheDailyWTF regarding a, umm, "creative" use of "Stored Procedures": http://thedailywtf.com/Articles/For-the-Ease-of-Maintenance.aspx http://www.sqlfrenzy.com/2009/11/an-interesting-take-on-stored-procedures-link/ This is from March, so it's a little old, but I just stumbled upon it and found it a bit interesting. I never thought of programming this way before, but it does make sense: Programming is like a dream http://www.sqlfrenzy.com/2009/11/programming-is-like-dreaming/ ... or is about time I got back to some blogging? http://www.sqlfrenzy.com/2009/10/is-it-just-me/ Here's a simple SQL Server stored procedure that you can call to process an OLAP cube using T-SQL.  The parameters should be self-explanatory.  To me, this is a little easier and more flexible than processing using DTS packages. create procedure ProcessCube     @Database varchar(100),     @Cube varchar(100),     @Partition varchar(100)  = null, -- If ... http://www.sqlfrenzy.com/2008/10/processing-an-olap-cube-with-a-t-sql-stored-procedure/ I apologize for not posting any new content in quite some time, but now I am back and will soon start posting on a (hopefully!) regular basis once again. The reason for my hiatus was a pretty good one, though: I recently became a father with the birth of my son ... http://www.sqlfrenzy.com/2008/10/im-back-with-some-news/ Most people know about cascading updates and deletes, but did you know there are two other foreign key constraint options you can use to maintain referential integrity? Read all about them in my latest article over at SQLTeam.com. These features, introduced with SQL Server 2005, haven't got a lot of publicity, but ... http://www.sqlfrenzy.com/2008/08/foreign-key-constraints-set-null-and-set-default/ Always remember that the column names returned in a result set do not have to be the same as what you eventually output at your presentation layer.   For example, suppose you have stored procedure that accepts a @CurrentYear parameter and returns a sales variance between the current year and the ... http://www.sqlfrenzy.com/2008/08/database-column-names-report-headings/ If the start date and end date are both week days, then the total number of week days in between is simply: (total difference in days) - (total difference in weeks) * 2 or  DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2 ... since the DateDiff() function with weeks returns the number of week "boundaries" ... http://www.sqlfrenzy.com/2008/07/how-to-calculate-the-number-of-week-days-between-two-dates/ A common mistake beginners make when working with SQL is trying to format their output at the database layer, as opposed to simply doing this at the presentation layer (i.e., client application, reporting tool, web page, etc).  I've covered that quite a bit in various blog posts, but I've only ... http://www.sqlfrenzy.com/2008/07/convert-input-explicitly-at-your-client-dont-rely-on-the-database-to-figure-it-out/