median value from the query

Question

I wrote this query

select CustomerNo, Bill
from table A p 
left join table B m
p.ID = m.CustomerID
where substring (CustomerNo, 11,9) = '09'   'This shows total CustomerNo for year 2009
and Bill <> ''                              ' like suppose 257 rows

How can I add RowNumber in this Query? and Ascending Bill value? How can I find median Bill value for Odd and even RowNumber?

Answer 1

See this related question: Function to Calculate Median in Sql Server

Here's syntax information on ROW_NUMBER(): http://msdn.microsoft.com/en-us/library/ms186734.aspx

For your query, the ROW_NUMBER column would look something like this:

ROW_NUMBER() OVER (ORDER BY Bill ASC) as 'RowNumber'

Answer 2

I hope I understand your question correct. It is a bit fuzzy along with the sql you have written. I have tried to repair your sql which will never run as written and the left join is dead. I wrote the sql exactly as understood your description. If you want something different, you should come up with more information like testdata and expected result.

declare @a table(id int, CustomerNo varchar(20))
declare @b table(bill varchar(20), customerid int, billvalue money)

insert @a values(1, 'abcdefghij09')

insert @b values('a', 1,3.5)
insert @b values('b', 1,42)
insert @b values('c', 1,5)
insert @b values('d', 1,6)
insert @b values('e', 1,2)

;with a as (
select CustomerNo, Bill,billvalue,ROW_NUMBER() OVER (ORDER BY Billvalue ASC) rownumber
from @A p 
left join @B m 
on p.ID = m.CustomerID
and m.Bill <> ''
where 
substring (p.CustomerNo, 11,2) = '09'
), b as 
(
select top 2 billvalue, isodd from (select top 50 percent billvalue, rownumber %2 isodd  from a order by billvalue) a order by billvalue desc
)
select * from a --- last codeline

This will solve your first question

To get the medians for odd and even lines, replace last code line with

select * from b

To make medians part of the whole sql, replace last code line with this:

select *, (select billvalue from b where isodd = 0) evenmedianbillvalue, 
(select billvalue from b where isodd = 1) oddmedianbillvalue
from a order by billvalue

Result:

CustomerNo,    Bill billvalue rownumber evenmedianbillvalue oddmedianbillvalue    
abcdefghij09   e    2         1         3.5                 5
abcdefghij09   a    3.5       2         3.5                 5
abcdefghij09   c    5         3         3.5                 5
abcdefghij09   d    6         4         3.5                 5
abcdefghij09   b    42        5         3.5                 5