**Table 1: book_sales**
    identifiers
    sales_price
    sales_date
    store
    quantity

     
    **Table2: discount**
    identifiers
    sales_price (the changed / discounted price)
    from_date
    to_date

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

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

  ALTER PROCEDURE [dbo].[Loid] @month         INT, 
                                 @year          INT, 
                                 @report_source NVARCHAR(255), 
                                 @is_primary    INT 
    AS 
        SELECT Cast(isa.sales_date AS DATE)                                   AS 
               DATE, 
               BV.name, 
               isa.identifiers, 
               isa.quantity, 
               Isnull(id.sales_price, Isnull(u.sales_price, isa.sales_price)) AS 
               SALES_PRICE 
        FROM   book_sales AS isa 
               LEFT OUTER JOIN store AS BV 
                            ON bv.store_id = isa.store_id 
               LEFT OUTER JOIN discount AS id 
                            ON id.identifiers = isa.identifiers 
                               AND id.from_date <= isa.sales_date 
                               AND id.to_date >= isa.sales_date 
               LEFT OUTER JOIN discount AS u 
                            ON u.identifiers = isa.identifiers 
                               AND u.to_date = '2999-01-01' 
               LEFT OUTER JOIN book_contributor AS BC 
                            ON BC.book_id = isa.book_id 
        WHERE  Month(isa.sales_date) = @month 
               AND Year(isa.sales_date) = @year 
               AND isa.report_source = @report_source 
               AND bc.is_primary = @is_primary

quote:

---

Originally posted by stepson
discount.to_date what type is ? (datetime has also time in field and maybe not getting the u.to_date = '2999-01-01' )

with this:

coalesce(u.sales_price,id.sales_price,isa.sales_price) AS SALES_PRICE 

---

;with book_sales
AS (
	select 'item01' as identifiers,99 as sales_price,'2014-02-01' as sales_date,1 as store_id,3 as quantity union all
	select 'item01', 99,'2014-02-02',1,2 union all
	select 'item01', 99,'2014-02-03',1,2 union all
	select 'item01', 99,'2014-02-04',1,2 union all
	select 'item01', 99,'2014-02-05',1,2 union all
	select 'item01', 99,'2014-02-06',1,2 
)
,discount
AS(
	select 'item01' as identifiers ,69 as sales_price , '2014-02-03'  from_date ,'2014-02-05' as to_date
)

SELECT Cast(isa.sales_date AS DATE)  AS  DATE, 
               --BV.name, 
               isa.identifiers, 
               isa.quantity, 
               Isnull(id.sales_price, Isnull(u.sales_price, isa.sales_price)) AS 
               SALES_PRICE 
			   ,COALESCE(u.sales_price,id.sales_price,isa.sales_price) AS SALESPRICE 
        FROM   book_sales AS isa 
              /* LEFT OUTER JOIN store AS BV 
                            ON bv.store_id = isa.store_id */
               LEFT OUTER JOIN discount AS id 
                            ON id.identifiers = isa.identifiers 
                               AND id.from_date <= isa.sales_date 
                               AND id.to_date >= isa.sales_date 							   
               LEFT OUTER JOIN discount AS u 
                            ON u.identifiers = isa.identifiers 
                               AND u.to_date = '2999-01-01' 
               /*LEFT OUTER JOIN book_contributor AS BC 
                            ON BC.book_id = isa.book_id */
       /* WHERE  
			Month(isa.sales_date) = @month 
               AND Year(isa.sales_date) = @year 
               AND isa.report_source = @report_source 
               AND bc.is_primary = @is_primary*/

DATE	identifiers	quantity	SALES_PRICE	SALESPRICE
2014-02-01	item01	3	99	99
2014-02-02	item01	2	99	99
2014-02-03	item01	2	69	69
2014-02-04	item01	2	69	69
2014-02-05	item01	2	69	69
2014-02-06	item01	2	99	99