Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SP returning multiple wrong data

Author  Topic 

Lebowski
Starting Member

17 Posts

Posted - 2014-03-10 : 02:23:06
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 - 2014-03-10 : 02:56:56
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
Aged Yak Warrior

545 Posts

Posted - 2014-03-10 : 02:58:53
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
Aged Yak Warrior

545 Posts

Posted - 2014-03-10 : 03:02:37
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 - 2014-03-10 : 03:22:48
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
Aged Yak Warrior

545 Posts

Posted - 2014-03-10 : 03:26:16
put some sample values for those tbls








sabinWeb MCP
Go to Top of Page
   

- Advertisement -