SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SP returning multiple wrong data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lebowski
Starting Member

17 Posts

Posted - 03/10/2014 :  02:23:06  Show Profile  Reply with Quote
I will try to explain this problem, and i simplified the two needed tables that is important in this 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 the `to_date` in `Discount` table is '2999-01-01' it should overwrite the `sales price` in `book_sales` (In this example it is: 99,-)
- If the `from_date` and `to_date` in `Discount` table is for just a period, lets say From_Date: 2014-02-03 and To_Date 2014-02-05 it should overwrite the other prices for exactly that period. (In this example it is 69,-)

My stored procedure is supposed to return data like 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 it is showing both and returning data like 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...
So this is the actual procedure, can you guys see anything wrong?

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


http://mnmt.no

Lebowski
Starting Member

17 Posts

Posted - 03/10/2014 :  02:56:56  Show Profile  Reply with Quote
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




First: Thanks for taking your time. Heres whats happend:

1. When i do this coalesce change, ALL of the prices for ALL of the stores changes to the one set to '2999-01-01'.

2. discount.to_date is set to date

http://mnmt.no
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 03/10/2014 :  02:58:53  Show Profile  Reply with Quote
you have a bad relation/s

comment the join with STORE
and the join with book_contributor

and the result will be ok

one of those relations make doubles your results


sabinWeb MCP
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 03/10/2014 :  03:02:37  Show Profile  Reply with Quote
here is my set
(i commented the join with STORE and BOOK_Contributor)

this mean, that you need to add ,in the join, more conditons.


;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*/


output


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



sabinWeb MCP
Go to Top of Page

Lebowski
Starting Member

17 Posts

Posted - 03/10/2014 :  03:22:48  Show Profile  Reply with Quote
I am still getting same results... It prints out both the small period price, and the '2999-01-01' price..

http://mnmt.no
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 03/10/2014 :  03:26:16  Show Profile  Reply with Quote
put some sample values for those tbls








sabinWeb MCP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000