# Thinking outside the box

Patron Saint of Lost Yaks

## September 2009 Blog Posts

##### Median and weighted median

Yesterday Joe Celko posted on microsoft.public.sqlserver.programming about how to write an elegant query for Weighted Median. He managed to get the correct results but always ended up with ugly code. Joe had a feeling an elegant query existed but was not seeing it. Anyway, since Celko's books have helped me in the past, I thought I should help him now. Consider this sample data DECLARE @Foo TABLE         (             x INT NOT NULL         ) INSERT  @Foo VALUES  (1),         (2),         (2),         (3),         (3),         (3) The most common approach to calculate the median value I have seen is SELECT  AVG(1.0E * x) FROM    (             SELECT  x,                     ROW_NUMBER() OVER (ORDER BY x DESC) AS a,                     ROW_NUMBER() OVER (ORDER BY x) AS...

posted @ Wednesday, September 16, 2009 9:56 PM | Feedback (7) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### Statistical SQLCLR for prediction analysis now in RC1

For more information about my SQLCLR, see http://www.developerworkshop.net/software.html The biggest difference from Beta2 stage is that I now only target SQL Server 2008 and later. //Peso

posted @ Thursday, September 03, 2009 9:45 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms ]

##### NOT IN not equal to <>

Hi! I am between session of 24 hour of PASS right now, so I just post this piece of code I helped out with on a forum. The original poster was amazed that Col1 <> 'Some value' didn't return same records as Col1 NOT IN ('Some value') See this example code to understand the implications of fiddling with SET ANSI_NULLS option setting. declare @sample table         (             d varchar(200)         ) Insert  @sample select  'cancelled' union all select  null -- Try 1 set ansi_nulls off select  d as [Try 1, <>, ANSI_NULLS off] from    @sample where   d <> 'cancelled' select  d as [Try 1, NOT IN, ANSI_NULLS off] from    @sample where   d not in ('cancelled') -- Try 2 set ansi_nulls on select  d as [Try...

posted @ Wednesday, September 02, 2009 4:07 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]