Coalescing rows based on field value in row

I have a table with tax rates where NULL entity type rows represent the default tax rates.

Year End | EntityType | RateType | TaxRate
------------------------------------------
2009 |       NULL | Interest |      13
2009 |       NULL |    Other |       8
2009 |       NULL | Interest |      13
2010 |       NULL |    Other |       9 
2009 |    Company | Interest |      15
2010 | Individual |    Other |       6  

I want to create a stored procedure for this table with Entitytype as a parameter. Such that for each year, it returns the tax rate for that entity type if it exists, otherwise return the NULL row for that year and ratetype.

i.e.: querying the above with entitytype individual should return

Year End | EntityType | RateType | TaxRate
------------------------------------------
2009     |       NULL | Interest |      13
2009     |       NULL |    Other |       8
2009     |       NULL | Interest |      13
2010     | Individual |    Other |       6

Can anybody suggest a way I could achieve this?

Thanks in advance,

Yong

Answers


SELECT 
    t1.year_end,
    t1.entity_type,
    t1.ratetype,
    COALESCE(t2.taxrate, t1.taxrate)
  FROM
    Rates AS t1
LEFT JOIN
    Rates AS t2 ON
        t1.year_end = t2.year_end
        AND t2.entitytype IS NULL
WHERE t1.year_end = @year
    AND t1.entitytype = @entitytype

Need Your Help

Ember Data hasMany foreach within controller

ember.js controller ember-data

I currently have the following models setup using Ember Data and the Local storage adaptor.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.