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...
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
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...