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
The most common approach to calculate the median value I have seen is
SELECT AVG(1.0E * x)
ROW_NUMBER() OVER (ORDER BY x DESC) AS a,
ROW_NUMBER() OVER (ORDER BY x) AS...
For more information about my SQLCLR, see
The biggest difference from Beta2 stage is that I now only target SQL Server 2008 and later.
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
select 'cancelled' union all
-- Try 1
set ansi_nulls off
select d as [Try 1, <>, ANSI_NULLS off]
where d <> 'cancelled'
select d as [Try 1, NOT IN, ANSI_NULLS off]
where d not in ('cancelled')
-- Try 2
set ansi_nulls on
select d as [Try...