Three different prices problem

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

General SQL Server Forums

New to SQL Server Programming

Three different prices problem

Author

Topic

Lebowski
Starting Member

17 Posts

Posted - 2014-03-08 : 01:31:33

All right, let me try to explain:

In Table one (Sales_Data) we have: 
Item_Id
Store_Id
Price
Sales_Date

In table two (Discount_Data) we have: 
Item_Id    
Store_Id
Price
From_Date
To_Date

A item can have three prices:

(1)The main price is in Sales_Data example: 99,-
(2)The second price is a discount for a period in a month example: 79,- for four days stored
(3)The third price is if its another store, then its set to unlimited example: 89,- (The way we have sat unlimited period in our table is just year 2099...)
Both 2 and 3 is stored in Discount_Data.
The problem is:

The unlimited price(3) should always overwrite the main price (1)
The discount price(2) should always overwrite the unlimited(3) and/or the main price(1) for the selected period.
Example:

Item001 has original price of 99,-(1) in most stores. But for Store009 it should have another price of 89,-(3) for unlimited period. Then for february 02 to february 10 the item had a discount for the same store for 79,-(2)

Question:

Do i need to re-design my database tables, or can i with a stored procedure use some kind of WHEN statement or something like that to solve this?

Example:
WHEN To_Date = 2099 then....

http://mnmt.no

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-03-08 : 03:29:06

Try this:

select s.item_id
      ,s.store_id
      ,min(case when d.price is null or s.price<d.price then s.price else d.price end) as price
  from sales_data as s
       left outer join discount_data as d
                    on d.item_id=s.item.id
                   and d.store_id=s.store_id
                   and d.from_date<=s.sales_date
                   and d.to_date>=s.sales_date
 group by s.item_id
         ,s.store_id

Lebowski
Starting Member

17 Posts

Posted - 2014-03-08 : 04:28:48

Sorry man that wont work.

Let me try to explain one more time:

First table consists of the original price on a item.
Table1 (Sales_Data):
Item_Id
Store_Id
Price
Sales_Date

Second table consists of two prices
**one is if a store has another price and this has a 0 value in To_Date because this price should last forever.(Lets call this forever price)
**one is if a store has another price for just a period (02.03.2014-10.03.2014) lets call this discount
**both prices is stored in Price in Table2, but the dates are the big difference.

Table2 (Discount_Data):
Item_Id
Store_Id
Price
From_Date
To_Date

Now to the big Question:

Forever price should always overwrite original price

Discount price should always overwrite original/or forever price for the exact period
Item_Id, and Store_Id has to be the same.

How can i go forward to solve this? Can anyone help me on the way?

http://mnmt.no

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-03-08 : 04:47:06

Then try this:

select s.item_id
      ,s.store_id
      ,ifnull(d.price,ifnull(u.price,s.price)) as price
  from sales_data as s
       left outer join discount_data as d
                    on d.item_id=s.item.id
                   and d.store_id=s.store_id
                   and d.from_date<=s.sales_date
                   and d.to_date>=s.sales_date
                   and u.to_date<>[what_ever_value_you_use_to_identify_unlimited]
       left outer join discount_data as u
                    on u.item_id=s.item.id
                   and u.store_id=s.store_id
                   and u.to_date=[what_ever_value_you_use_to_identify_unlimited]

Lebowski
Starting Member

17 Posts

Posted - 2014-03-08 : 04:56:59

Thanks very much for trying to help me. I really appreciate your effort. I tried your query, but im getting:

[Err] 42000 - [SQL Server]'ifnull' is not a recognized built-in function name.

Question: Can i use 0 to identify unlimited or is this wrong?

Here is my actual query, the first question was very simplified.. maybe you can see the mistakes here:

select 
                 s.IDENTIFIERS
		,s.store_id
               ,ifnull(d.SALES_PRICE,ifnull(u.SALES_PRICE,s.SALES_PRICE)) as SALES_PRICE
  from BOOK_SALES as s
           LEFT OUTER JOIN store AS BV 
                        ON bv.store_id = s.store_id 
       left outer join discount as d
                    on d.IDENTIFIERS=s.IDENTIFIERS
                   and d.from_date<=s.sales_date
                   and d.to_date>=s.sales_date
                   and u.to_date<0>
       left outer join discount as u
                    on u.IDENTIFIERS=s.IDENTIFIERS
                   and u.to_date=0

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-03-08 : 10:41:55

My mistake. Instead of "ifnull" use "isnull".

select s.IDENTIFIERS
      ,s.store_id
      ,ifnullisnull(d.SALES_PRICE,ifnullisnull(u.SALES_PRICE,s.SALES_PRICE)) as SALES_PRICE
  from BOOK_SALES as s
       LEFT OUTER JOIN store AS BV 
                    ON bv.store_id = s.store_id 
       left outer join discount as d
                    on d.IDENTIFIERS=s.IDENTIFIERS
                   and d.from_date<=s.sales_date
                   and d.to_date>=s.sales_date
                   and u.to_date<0><>0
       left outer join discount as u
                    on u.IDENTIFIERS=s.IDENTIFIERS
                   and u.to_date=0

Lebowski
Starting Member

17 Posts

Posted - 2014-03-10 : 00:42:35

Didnt work out, sorry

http://mnmt.no

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-10 : 01:06:17

quote:
Originally posted by Lebowski

Didnt work out, sorry

http://mnmt.no

What didn't work out ? You got error ? The result is not correct ?

You have to let us know what didn't work out.

Also it will be easier for others to help you if you can post some sample data and expected result

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources