sql - Complex SP for monthly sales returning wrong data -

i try explain problem, , simplified 2 needed tables important in procedure:

**table 1: book_sales** identifiers sales_price sales_date store quantity  **table2: discount** identifiers sales_price (the changed / discounted price) from_date to_date 

• if to_date in discount table '2999-01-01' should overwrite sales price in book_sales (in example is: 99,-)
• if from_date , to_date in discount table period, lets from_date: 2014-02-03 , to_date 2014-02-05 should overwrite other prices period. (in example 69,-)

my stored procedure supposed return data this:

+------------+-----------+---------+----+-----+ | 2014-02-01 |  itemname |  item01 |  3 |  99 | | 2014-02-02 |  itemname |  item01 |  2 |  99 | | 2014-02-03 |  itemname |  item01 |  2 |  69 | | 2014-02-04 |  itemname |  item01 |  2 |  69 | | 2014-02-05 |  itemname |  item01 |  2 |  69 | | 2014-02-06 |  itemname |  item01 |  2 |  99 | +------------+-----------+---------+----+-----+ 

but showing both , returning data this:

+------------+-----------+---------+----+-----+ | 2014-02-01 |  itemname |  item01 |  3 |  99 | | 2014-02-01 |  itemname |  item01 |  3 |  69 | | 2014-02-02 |  itemname |  item01 |  2 |  99 | | 2014-02-02 |  itemname |  item01 |  2 |  69 | | 2014-02-03 |  itemname |  item01 |  2 |  99 | | 2014-02-03 |  itemname |  item01 |  2 |  69 | +------------+-----------+---------+----+-----+ 

etc... actual procedure, can guys see wrong?

alter procedure [dbo].[loid] @month         int,                               @year          int,                               @report_source nvarchar(255),                               @is_primary    int       select cast(isa.sales_date date)                                               date,             bv.name,             isa.identifiers,             isa.quantity,             isnull(id.sales_price, isnull(u.sales_price, isa.sales_price))             sales_price        book_sales isa             inner join store bv                     on bv.store_id = isa.store_id             left outer join discount id                          on id.identifiers = isa.identifiers                             , id.from_date <= isa.sales_date                             , id.to_date >= isa.sales_date                             , id.to_date < '2999-01-01'                             , bv.name = id.store             left outer join discount u                          on u.identifiers = isa.identifiers                             , u.to_date = '2999-01-01'             left outer join book_contributor bc                          on bc.book_id = isa.book_id       month(isa.sales_date) = @month             , year(isa.sales_date) = @year             , isa.report_source = @report_source             , bc.is_primary = @is_primary  

by looking @ result assume have set from_date value discount records has to_date '2999-01-01'

left outer join discount id  on id.identifiers = isa.identifiers  , id.from_date <= isa.sales_date  , id.to_date >= isa.sales_date  

if in join statement discount records joins creates duplicate records.

either can set from_date higher value '2999-01-01' discount records has to_date '2999-01-01' or change join statemnt below.

left outer join discount id  on id.identifiers = isa.identifiers  , id.from_date <= isa.sales_date  , id.to_date >= isa.sales_date  , id.to_date < '2999-01-01' 

edit

according chat following should work. new condition bv.name = u.store added second join discount table.

alter procedure [dbo].[loid] @month         int,                               @year          int,                               @report_source nvarchar(255),                               @is_primary    int       select cast(isa.sales_date date)                                               date,             bv.name,             isa.identifiers,             isa.quantity,             isnull(id.sales_price, isnull(u.sales_price, isa.sales_price))             sales_price        book_sales isa             inner join store bv                     on bv.store_id = isa.store_id             left outer join discount id                          on id.identifiers = isa.identifiers                             , id.from_date <= isa.sales_date                             , id.to_date >= isa.sales_date                             , id.to_date < '2999-01-01'                             , bv.name = id.store             left outer join discount u                          on u.identifiers = isa.identifiers                             , u.to_date = '2999-01-01'                            , bv.name = u.store              left outer join book_contributor bc                          on bc.book_id = isa.book_id       month(isa.sales_date) = @month             , year(isa.sales_date) = @year             , isa.report_source = @report_source             , bc.is_primary = @is_primary