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
 General SQL Server Forums
 New to SQL Server Programming
 Three different prices problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lebowski
Starting Member

17 Posts

Posted - 03/08/2014 :  01:31:33  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

374 Posts

Posted - 03/08/2014 :  03:29:06  Show Profile  Reply with Quote
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
Go to Top of Page

Lebowski
Starting Member

17 Posts

Posted - 03/08/2014 :  04:28:48  Show Profile  Reply with Quote
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
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

374 Posts

Posted - 03/08/2014 :  04:47:06  Show Profile  Reply with Quote
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]
Go to Top of Page

Lebowski
Starting Member

17 Posts

Posted - 03/08/2014 :  04:56:59  Show Profile  Reply with Quote
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

Edited by - Lebowski on 03/08/2014 05:07:39
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

374 Posts

Posted - 03/08/2014 :  10:41:55  Show Profile  Reply with Quote
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
Go to Top of Page

Lebowski
Starting Member

17 Posts

Posted - 03/10/2014 :  00:42:35  Show Profile  Reply with Quote
Didnt work out, sorry

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

khtan
In (Som, Ni, Yak)

Singapore
17611 Posts

Posted - 03/10/2014 :  01:06:17  Show Profile  Reply with Quote
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
Time is always against us

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.08 seconds. Powered By: Snitz Forums 2000