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

Accessing DOM elements without id

javascript dom

i have a page around 500 div as below.

Program freeze after input?

java loops input while-loop freeze

After the user inputs their choices, the program seems to offer the same choices again, followed by it completely freezing. It's only set on there to give them the option once (I believe) and then ...