Home » SQL & PL/SQL » SQL & PL/SQL » Complicated SQL to Calculate Prices (Oracle 10.1.0.2.0 - Windows XP)
icon5.gif  Complicated SQL to Calculate Prices [message #422485] Wed, 16 September 2009 15:04 Go to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

Firstly I'm not sure if this can be done with just SQL, but here goes...

Below is the sample data: -


CREATE TABLE articles
    (id                             NUMBER(9,0) NOT NULL,
    code                           VARCHAR2(20) NOT NULL,
    description                    VARCHAR2(40) NOT NULL,
    uom_group_id                   NUMBER(9,0))
/
-- Constraints for ARTICLES
ALTER TABLE articles ADD CONSTRAINT ck_articles_id CHECK (id > 0)
/
ALTER TABLE articles ADD CONSTRAINT pk_articles PRIMARY KEY (id)
/

CREATE TABLE uom_groups
    (id                             NUMBER(9,0) NOT NULL,
    code                           NUMBER(8,0) NOT NULL,
    description                    VARCHAR2(40))
/
ALTER TABLE uom_groups ADD CONSTRAINT unq_uom_groups UNIQUE (code)
/
ALTER TABLE uom_groups ADD CONSTRAINT ck_uomgroups_id CHECK (id > 0)
/
ALTER TABLE uom_groups ADD CONSTRAINT pk_uom_groups PRIMARY KEY (id)
/

CREATE TABLE uom_units
    (id                             NUMBER(9,0) NOT NULL,
    uom_group_id                   NUMBER(9,0) NOT NULL,
    factor                         NUMBER(5,2) DEFAULT 1 NOT NULL,
    description                    VARCHAR2(40),
    display_description            VARCHAR2(40),
    is_default                     NUMBER(1,0) DEFAULT 0 NOT NULL)
/
ALTER TABLE uom_units ADD CONSTRAINT ck_uomunits_id CHECK (id > 0)
/
ALTER TABLE uom_units ADD CONSTRAINT pos_uomunits_factor CHECK (factor >= 0)
/
ALTER TABLE uom_units ADD CONSTRAINT pk_uom_units PRIMARY KEY (id)
/
ALTER TABLE uom_units ADD CONSTRAINT fk_uomunits_unitgroups FOREIGN KEY (uom_group_id)
REFERENCES uom_groups (id) ON DELETE CASCADE
/

CREATE TABLE pricelevels
    (id                             NUMBER(9,0) NOT NULL,
    code                           NUMBER(8,0) NOT NULL,
    description                    VARCHAR2(40) NOT NULL,
    notes1                         VARCHAR2(40),
    calculated_price_percent       NUMBER(6,2))
/
ALTER TABLE pricelevels ADD CONSTRAINT ck_pricelevels_id CHECK (id > 0)
/
ALTER TABLE pricelevels ADD CONSTRAINT pk_pricelevels PRIMARY KEY (id)
/

CREATE TABLE prices
    (id                             NUMBER(9,0) NOT NULL,
    article_id                     NUMBER(9,0) NOT NULL,
    pricelevel_id                  NUMBER(9,0) NOT NULL,
    uom_unit_id                    NUMBER(9,0),
    price                          NUMBER(10,2) NOT NULL,
    formula                        VARCHAR2(10))
/
ALTER TABLE prices ADD CONSTRAINT ck_prices_id CHECK (id > 0)
/
ALTER TABLE prices ADD CONSTRAINT pk_prices PRIMARY KEY (id)
/

INSERT INTO articles VALUES (1,'040-00001','Mars Bar',NULL);
INSERT INTO articles VALUES (2,'010-00001','Peroni',NULL);
INSERT INTO articles VALUES (3,'040-00002','Bon Bons',NULL);
INSERT INTO articles VALUES (4,'040-00003','Pear Drops',NULL);
INSERT INTO articles VALUES (5,'040-00004','Fruit Pips',NULL);
INSERT INTO articles VALUES (6,'010-00002','Coke',2);
INSERT INTO articles VALUES (7,'010-00003','Fosters',1);
INSERT INTO articles VALUES (8,'010-00004','Juice',3);
INSERT INTO articles VALUES (9,'010-00005','Coke',1);
INSERT INTO articles VALUES (10,'010-00006','Juice',1);
INSERT INTO articles VALUES (11,'020-00001','Prawn Platter',NULL);
INSERT INTO articles VALUES (12,'020-00002','10oz Ribeye',NULL);
INSERT INTO articles VALUES (13,'020-00003','Peppercorn Sauce',NULL);
INSERT INTO articles VALUES (14,'020-00004','Half Roast Duck',NULL);
INSERT INTO articles VALUES (15,'020-00005','Red Wine Sauce',NULL);
INSERT INTO articles VALUES (16,'020-00006','Stilton Sauce',NULL);
INSERT INTO articles VALUES (17,'020-00007','BBQ Jack Daniels Sauce',NULL);
INSERT INTO articles VALUES (18,'020-00008','8oz Rump',NULL);
INSERT INTO articles VALUES (19,'020-00009','10oz Sirloin',NULL);
INSERT INTO articles VALUES (20,'000-00001','All Gold Milk 225g',NULL);
INSERT INTO articles  VALUES (21,'000-00002','Airwaves MenthEuca H/Pk',NULL);
INSERT INTO articles VALUES (22,'040-00005','Uncle Joe''s Mint Balls',NULL);
INSERT INTO articles VALUES (23,'040-00006','Snickers',NULL);
INSERT INTO articles VALUES (24,'010-00007','Diet Coke',4);
INSERT INTO articles VALUES (25,'010-00008','Lemonade',4);
INSERT INTO articles VALUES (26,'020-00010','Smoked Salmon Pate',NULL);
INSERT INTO articles VALUES (27,'020-00011','Chicken Liver Pate',NULL);
INSERT INTO articles VALUES (28,'020-00012','Roast Lamb Sheppard''s Pie',NULL);
INSERT INTO articles VALUES (29,'020-00013','Salmon Smoked Haddock Fish Cake',NULL);
INSERT INTO articles VALUES (30,'020-00014','Angus Steak Burger',NULL);
INSERT INTO articles VALUES (31,'020-00015','Vegetarian Pasta of the Moment',NULL);
INSERT INTO articles VALUES (32,'010-00009','Carling',1);
INSERT INTO articles VALUES (33,'010-00010','Guinness',1);
INSERT INTO articles VALUES (34,'010-00011','Hidden Quest',1);
INSERT INTO articles VALUES (35,'010-00012','Hidden Pleasure',1);
INSERT INTO articles VALUES (36,'010-00013','London Pride',1);
INSERT INTO articles VALUES (37,'010-00014','Strongbow',1);
INSERT INTO articles VALUES (38,'010-00015','Pils',NULL);
INSERT INTO articles VALUES (39,'010-00016','Becks',NULL);
INSERT INTO articles VALUES (40,'010-00017','Budweiser',NULL);
INSERT INTO articles VALUES (41,'010-00018','House White',5);
INSERT INTO articles VALUES (42,'010-00019','House Red',5);
INSERT INTO articles VALUES (43,'010-00020','Chardonnay',5);
INSERT INTO articles VALUES (44,'010-00021','Merlot',5);
INSERT INTO articles VALUES (45,'010-00022','Smirnoff',6);
INSERT INTO articles VALUES (46,'010-00023','Gin',6);
INSERT INTO articles VALUES (47,'010-00024','Bacardi',6);
INSERT INTO articles VALUES (48,'010-00025','Bells',6);
INSERT INTO articles VALUES (49,'010-00026','Jack Daniels',6);
INSERT INTO articles VALUES (50,'010-00027','Plymouth Gin',6);
INSERT INTO articles VALUES (51,'010-00028','Southern Comfort',6);
INSERT INTO articles VALUES (52,'010-00029','Sherry',6);
INSERT INTO articles VALUES (53,'010-00030','Baileys',6);
INSERT INTO articles VALUES (54,'010-00031','Martini',6);
INSERT INTO articles VALUES (55,'010-00032','Johnnie Walker',6);
INSERT INTO articles VALUES (56,'010-00033','Tonic',NULL);
INSERT INTO articles VALUES (57,'010-00034','Slimline Tonic',NULL);
INSERT INTO articles VALUES (58,'010-00035','Tomato Juice',NULL);
INSERT INTO articles VALUES (59,'010-00036','Orange Juice',NULL);
INSERT INTO articles VALUES (60,'010-00037','Pineapple Juice',NULL);
INSERT INTO articles VALUES (61,'010-00038','Grapefruit Juice',NULL);
INSERT INTO articles VALUES (62,'010-00039','Red Bull',NULL);
INSERT INTO articles VALUES (63,'020-00016','Bacon',NULL);
INSERT INTO articles VALUES (64,'020-00017','Cheese',NULL);
INSERT INTO articles VALUES (65,'020-00018','Mushrooms',NULL);
INSERT INTO articles VALUES (66,'010-00040','Diet Lemonade',4);
INSERT INTO articles VALUES (67,'010-00041','Soft Drink',4);
INSERT INTO articles VALUES (68,'030-00001','Matches',NULL);
INSERT INTO articles VALUES (69,'010-00042','Test Soft Drink',4);

INSERT INTO uom_groups VALUES (1,1,'Pints');
INSERT INTO uom_groups VALUES (2,2,'Postmix');
INSERT INTO uom_groups VALUES (3,3,'Fresh Juice');
INSERT INTO uom_groups
VALUES
(5,5,'Wines')
/
INSERT INTO uom_groups
VALUES
(6,6,'Spirits')
/
INSERT INTO uom_groups
VALUES
(4,4,'Soft Drinks')
/

INSERT INTO uom_units
VALUES
(1,1,1,'Pint','Pint',1)
/
INSERT INTO uom_units
VALUES
(2,1,0.5,'Half','Half',0)
/
INSERT INTO uom_units
VALUES
(3,2,1,'Pint','Pint',0)
/
INSERT INTO uom_units
VALUES
(4,2,0.5,'Half','Half',0)
/
INSERT INTO uom_units
VALUES
(5,2,0.3,'Splash','Splash',1)
/
INSERT INTO uom_units
VALUES
(6,3,1,'Pint','Pint',1)
/
INSERT INTO uom_units
VALUES
(7,3,0.5,'Half','Half',0)
/
INSERT INTO uom_units
VALUES
(12,5,1,'Standard (175ml)','175ml',1)
/
INSERT INTO uom_units
VALUES
(13,5,1.43,'Large (250ml)','250ml',0)
/
INSERT INTO uom_units
VALUES
(14,5,4.29,'Bottle','Bottle',0)
/
INSERT INTO uom_units
VALUES
(15,6,1,'Single','Single',1)
/
INSERT INTO uom_units
VALUES
(16,6,2,'Double','Double',0)
/
INSERT INTO uom_units
VALUES
(8,1,0.3,'Splash','Splash',0)
/
INSERT INTO uom_units
VALUES
(9,4,0.5,'Half Pint','Half Pint',1)
/
INSERT INTO uom_units
VALUES
(10,4,1,'Pint','Pint',0)
/
INSERT INTO uom_units
VALUES
(11,4,0.3,'Dash','Dash',0)
/

INSERT INTO pricelevels
VALUES
(2,2,'Happy Hour',NULL,-10)
/
INSERT INTO pricelevels
VALUES
(5,450,'Drinks No Service (40%)','40%',-40)
/
INSERT INTO pricelevels
VALUES
(6,451,'Drinks with Service (20%)','20%',-20)
/
INSERT INTO pricelevels
VALUES
(7,452,'Drinks No Service (20%)','20%',-20)
/
INSERT INTO pricelevels
VALUES
(8,453,'Drinks with Service (10%)','10%',-10)
/
INSERT INTO pricelevels
VALUES
(9,454,'Supply of Drinks',NULL,NULL)
/
INSERT INTO pricelevels
VALUES
(4,4,'Another Pricelevel',NULL,NULL)
/
INSERT INTO pricelevels
VALUES
(3,3,'Kitchen Transfer',NULL,NULL)
/
INSERT INTO pricelevels
VALUES
(1,1,'System Main Pricelevel',NULL,NULL)
/



INSERT INTO prices
VALUES
(1,1,1,NULL,0.55,NULL)
/
INSERT INTO prices
VALUES
(2,2,1,NULL,3.2,NULL)
/
INSERT INTO prices
VALUES
(3,3,1,NULL,6.96,NULL)
/
INSERT INTO prices
VALUES
(4,4,1,NULL,7.96,NULL)
/
INSERT INTO prices
VALUES
(5,5,1,NULL,7.96,NULL)
/
INSERT INTO prices
VALUES
(6,6,1,3,2.5,NULL)
/
INSERT INTO prices
VALUES
(7,6,1,4,1.5,NULL)
/
INSERT INTO prices
VALUES
(8,6,1,5,0.8,NULL)
/
INSERT INTO prices
VALUES
(12,7,1,1,3.5,NULL)
/
INSERT INTO prices
VALUES
(15,8,1,6,3.1,NULL)
/
INSERT INTO prices
VALUES
(19,9,1,1,2.5,NULL)
/
INSERT INTO prices
VALUES
(20,9,1,2,1.5,NULL)
/
INSERT INTO prices
VALUES
(21,9,1,8,0.8,NULL)
/
INSERT INTO prices
VALUES
(23,10,1,1,3.1,NULL)
/
INSERT INTO prices
VALUES
(24,10,1,8,0.8,NULL)
/
INSERT INTO prices
VALUES
(26,10,1,2,1.55,NULL)
/
INSERT INTO prices
VALUES
(27,11,1,NULL,5.95,NULL)
/
INSERT INTO prices
VALUES
(28,12,1,NULL,13.5,NULL)
/
INSERT INTO prices
VALUES
(29,13,1,NULL,2,NULL)
/
INSERT INTO prices
VALUES
(30,14,1,NULL,12.95,NULL)
/
INSERT INTO prices
VALUES
(31,15,1,NULL,2,NULL)
/
INSERT INTO prices
VALUES
(32,16,1,NULL,2,NULL)
/
INSERT INTO prices
VALUES
(33,17,1,NULL,2,NULL)
/
INSERT INTO prices
VALUES
(34,18,1,NULL,10.95,NULL)
/
INSERT INTO prices
VALUES
(35,19,1,NULL,13.5,NULL)
/
INSERT INTO prices
VALUES
(36,20,1,NULL,3.35,NULL)
/
INSERT INTO prices
VALUES
(37,21,1,NULL,0.92,NULL)
/
INSERT INTO prices
VALUES
(38,23,1,NULL,0.45,NULL)
/
INSERT INTO prices
VALUES
(39,24,1,9,0.55,NULL)
/
INSERT INTO prices
VALUES
(40,24,1,11,0.3,NULL)
/
INSERT INTO prices
VALUES
(41,25,1,9,0.75,NULL)
/
INSERT INTO prices
VALUES
(42,25,1,11,0.3,NULL)
/
INSERT INTO prices
VALUES
(43,25,4,9,0.55,NULL)
/
INSERT INTO prices
VALUES
(44,25,4,10,1.1,NULL)
/
INSERT INTO prices
VALUES
(45,26,1,NULL,4.9,NULL)
/
INSERT INTO prices
VALUES
(46,27,1,NULL,4.9,NULL)
/
INSERT INTO prices
VALUES
(47,28,1,NULL,8.9,NULL)
/
INSERT INTO prices
VALUES
(48,29,1,NULL,8.9,NULL)
/
INSERT INTO prices
VALUES
(49,30,1,NULL,8.7,NULL)
/
INSERT INTO prices
VALUES
(50,31,1,NULL,7.4,NULL)
/
INSERT INTO prices
VALUES
(51,32,1,1,2.95,NULL)
/
INSERT INTO prices
VALUES
(52,33,1,1,3.25,NULL)
/
INSERT INTO prices
VALUES
(53,34,1,1,2.8,NULL)
/
INSERT INTO prices
VALUES
(54,35,1,1,2.9,NULL)
/
INSERT INTO prices
VALUES
(55,36,1,1,2.95,NULL)
/
INSERT INTO prices
VALUES
(56,37,1,1,2.9,NULL)
/
INSERT INTO prices
VALUES
(58,38,1,NULL,2.95,NULL)
/
INSERT INTO prices
VALUES
(59,39,1,NULL,2.95,NULL)
/
INSERT INTO prices
VALUES
(60,40,1,NULL,2.95,NULL)
/
INSERT INTO prices
VALUES
(61,41,1,12,2.75,NULL)
/
INSERT INTO prices
VALUES
(62,41,1,14,11.5,NULL)
/
INSERT INTO prices
VALUES
(63,41,1,13,3.75,NULL)
/
INSERT INTO prices
VALUES
(64,42,1,12,2.75,NULL)
/
INSERT INTO prices
VALUES
(65,42,1,13,3.75,NULL)
/
INSERT INTO prices
VALUES
(66,42,1,14,11.5,NULL)
/
INSERT INTO prices
VALUES
(67,43,1,12,3.75,NULL)
/
INSERT INTO prices
VALUES
(68,43,1,14,15.75,NULL)
/
INSERT INTO prices
VALUES
(69,43,1,13,4.5,NULL)
/
INSERT INTO prices
VALUES
(70,44,1,12,3.75,NULL)
/
INSERT INTO prices
VALUES
(71,44,1,13,4.5,NULL)
/
INSERT INTO prices
VALUES
(72,44,1,14,15.75,NULL)
/
INSERT INTO prices
VALUES
(73,45,1,15,2.4,NULL)
/
INSERT INTO prices
VALUES
(74,46,1,15,2.4,NULL)
/
INSERT INTO prices
VALUES
(75,47,1,15,2.4,NULL)
/
INSERT INTO prices
VALUES
(76,48,1,15,2.4,NULL)
/
INSERT INTO prices
VALUES
(77,49,1,15,2.5,NULL)
/
INSERT INTO prices
VALUES
(78,50,1,15,2.5,NULL)
/
INSERT INTO prices
VALUES
(79,51,1,15,2.5,NULL)
/
INSERT INTO prices
VALUES
(80,52,1,15,2.5,NULL)
/
INSERT INTO prices
VALUES
(81,53,1,15,2.5,NULL)
/
INSERT INTO prices
VALUES
(82,54,1,15,2.5,NULL)
/
INSERT INTO prices
VALUES
(83,55,1,15,2.5,NULL)
/
INSERT INTO prices
VALUES
(84,56,1,11,0.3,NULL)
/
INSERT INTO prices
VALUES
(85,56,1,9,0.75,NULL)
/
INSERT INTO prices
VALUES
(86,56,4,10,1.1,NULL)
/
INSERT INTO prices
VALUES
(87,56,4,9,0.55,NULL)
/
INSERT INTO prices
VALUES
(88,56,1,NULL,1.1,NULL)
/
INSERT INTO prices
VALUES
(89,57,1,11,0.3,NULL)
/
INSERT INTO prices
VALUES
(90,57,1,NULL,1.1,NULL)
/
INSERT INTO prices
VALUES
(91,57,4,10,1.1,NULL)
/
INSERT INTO prices
VALUES
(92,58,1,NULL,1.1,NULL)
/
INSERT INTO prices
VALUES
(93,59,1,NULL,1.1,NULL)
/
INSERT INTO prices
VALUES
(94,60,1,NULL,1.1,NULL)
/
INSERT INTO prices
VALUES
(95,61,1,NULL,1.1,NULL)
/
INSERT INTO prices
VALUES
(96,62,1,NULL,1.5,NULL)
/
INSERT INTO prices
VALUES
(97,63,1,NULL,0.75,NULL)
/
INSERT INTO prices
VALUES
(98,64,1,NULL,0.75,NULL)
/
INSERT INTO prices
VALUES
(99,65,1,NULL,0.75,NULL)
/
INSERT INTO prices
VALUES
(100,66,1,9,0.8,NULL)
/
INSERT INTO prices
VALUES
(102,66,4,9,0.55,NULL)
/
INSERT INTO prices
VALUES
(103,66,4,11,0.3,NULL)
/
INSERT INTO prices
VALUES
(104,67,1,9,0.5,NULL)
/
INSERT INTO prices
VALUES
(105,67,4,9,0.55,NULL)
/
INSERT INTO prices
VALUES
(106,68,1,NULL,0.3,NULL)
/
INSERT INTO prices
VALUES
(107,69,1,9,0.5,NULL)
/
INSERT INTO prices
VALUES
(108,69,4,9,0.5,NULL)
/
INSERT INTO prices
VALUES
(109,69,1,10,1.1,NULL)
/



Although I've included additional articles I thought for this post I'd focus on just a couple to start; 010-00009 Carling & 010-00022 Smirnoff.

It will probably help if I explain how the prices for an article within the database are calculated?

Articles can be sold as an 'Each', these are relatively straight forward.

The problem I'm having is with articles where they are associated to a UOM (Unit of Measure). Taking the 'Smirnoff' this has a default UOM of 'Single' (Factor 1.00) for which I have entered a price of £2.40, the 'Double' (Factor 2.00) is calculated based on the factor giving £4.80.

On the 'PRICES' table the only prices that are held are the ones where I've physically entered a price and over written the calculated one. So for the 'Smirnoff' only £2.40 will be held as £4.80 is calculated.

There are also 'Price Levels', these again are associated to the price of each article and unless physically entered, the price for the article at this price level is calculated.

What I want to pull out is produce a 'Price List', but I've got no idea how to do this, particularly seeing as the prices will need to be calculated where there isn't a physical price for it in the 'PRICES' table.

Could someone please give me some ideas, pointers or examples of how I can achieve my Price List?

Ever hopeful & Regards

[Updated on: Thu, 17 September 2009 01:51] by Moderator

Report message to a moderator

Re: Complicated SQL to Calculate Prices [message #422508 is a reply to message #422485] Wed, 16 September 2009 22:55 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I am sure someone would like to help and it was great that you posted a sample case, but I have these issues:

1) your sample case is pretty big, pare it down to a very small amount of data

2) your explanation is not fully clear. You fail to show how calculations are actually done. Give us some examples for each type of situation that shows how a calculation is done. Indeed once you do this on paper you may see how to do it in SQL.

Good luck, Kevin
Re: Complicated SQL to Calculate Prices [message #422521 is a reply to message #422485] Thu, 17 September 2009 02:03 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Great Man ,you work in happy hours Wink

SELECT *
  FROM articles A,uom_groups UG,UOM_UNITS UU,PRICES P,pricelevels PL
 WHERE A.UOM_GROUP_ID = UG.ID
 AND UU.UOM_GROUP_ID = UG.ID
 AND P.ARTICLE_ID = A.ID
 AND A.description in ('Smirnoff','Carling')
 ORDER BY 1


for you too kickstart

Note I have done a cartisan join with Pricelevels as every spirit can be sold at any time.Change accordingly to suit your needs

[Updated on: Thu, 17 September 2009 02:05]

Report message to a moderator

Re: Complicated SQL to Calculate Prices [message #422948 is a reply to message #422508] Mon, 21 September 2009 07:00 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

Thank you for the replies, I've taken onboard the point regarding the example of what I was looking for and showing the calculation where there isn't a price in the PRICES table.

Below is an example: -

http://imgur.com/7C663.jpg

Here I'm taking 3 of the articles 'Carling, Guinness & Smirnoff', and showing their prices for the PRICELEVEL_ID 1, the calculation for the prices is as follows (Default UOM Price / Default UOM Factor * UOM Factor) so for the Carling: -

2.95 / 1 * 0.5 = Half Pint Carling Price (1.48)

2.40 / 1 * 2 = Double Smirnoff Price (4.80)

I hope this makes some more sense?

Regards
Re: Complicated SQL to Calculate Prices [message #423089 is a reply to message #422948] Tue, 22 September 2009 01:30 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Default UOM Price / Default UOM Factor * UOM Factor


where are they??
SELECT   a.code, a.description, a.uom_group_id, uu.ID, uu.factor,
         uu.display_description, p.price, pl.ID pricelevel_id,
         price * factor price --insert formula here for price 
    FROM articles a, uom_groups ug, uom_units uu, prices p, pricelevels pl
   WHERE a.uom_group_id = ug.ID
     AND uu.uom_group_id = ug.ID
     AND p.article_id = a.ID
     AND a.description IN ('Smirnoff', 'Carling')
     AND pl.ID IN (1)
ORDER BY 1



This seems very easy so try yourself please.
Re: Complicated SQL to Calculate Prices [message #423327 is a reply to message #423089] Wed, 23 September 2009 07:08 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

Thank you for the replies. I've had a look at what has been posted and have got so far: -

SELECT      a.code, a.description, a.uom_group_id, uu.ID, uu.factor,
            uu.display_description, pl.ID pricelevel_id,
            case when   pl.calculated_price_percent is null then
                case when   factor is null then
                            price else
                            round(price * factor, 2)
                end
            else
                case when   factor is null then
                            round(price - (price * abs(pl.calculated_price_percent / 100)), 2) else
                            round((price * factor) - (price * factor) * (abs(pl.calculated_price_percent) / 100), 2)
                end
            end price
FROM        articles a, uom_groups ug, uom_units uu, prices p, pricelevels pl
WHERE       a.uom_group_id = ug.ID (+)
AND         uu.uom_group_id (+) = ug.ID
AND         p.article_id = a.ID
AND         a.description IN ('Smirnoff', 'Peroni')
AND         pl.ID IN (1,2)
ORDER BY    1


Which looks like this: -

http://imgur.com/Zh1N8.jpg

Now... Is there a way to check if a price already exists for that article in the prices table and if so don't calculate it but use the one found?

For example at the moment a 'Double Smirnoff' is calculated from the 'Single' price for the PRICELEVEL_ID 1. But if I had a price of 5.00 in the prices table for that article at that pricelevel and uom I'd want to show this and not calculate it.

I hope that makes some sense?

Has anyone any ideas on how I can do this check?

Many Thanks & Regards
Re: Complicated SQL to Calculate Prices [message #423328 is a reply to message #423327] Wed, 23 September 2009 07:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Outer join to the prices table, and use the result from the current query to nvl the value from Prices - that way if there is no row in pries, the outer join will cause it to return 0, and the NVL will make the result into the value you've just calculated.
Re: Complicated SQL to Calculate Prices [message #423329 is a reply to message #423328] Wed, 23 September 2009 07:36 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi & Thanks for the reply.

I think I understand what you mean, but I have a question about it.

The Prices table has 6 columns: -

TABLE prices
    (id                             NUMBER(9,0) NOT NULL,
    article_id                     NUMBER(9,0) NOT NULL,
    pricelevel_id                  NUMBER(9,0) NOT NULL,
    uom_unit_id                    NUMBER(9,0),
    price                          NUMBER(10,2) NOT NULL,
    formula                        VARCHAR2(10))


Will I need to make 3 joins on the Prices table, one for the Article ID, another for the Pricelevel ID and the last one on the UOM Unit ID?

Do you have an example for the outer Prices join you're refering to?

Regards
Re: Complicated SQL to Calculate Prices [message #423330 is a reply to message #423329] Wed, 23 September 2009 07:44 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Will I need to make 3 joins on the Prices table, one for the Article ID, another for the Pricelevel ID and the last one on the UOM Unit ID?


Dude you need to improve you data model in that case..
because improper design can lead to costly workarounds in future

Quote:
For example at the moment a 'Double Smirnoff' is calculated from the 'Single' price for the PRICELEVEL_ID 1. But if I had a price of 5.00 in the prices table for that article at that pricelevel and uom I'd want to show this and not calculate it.


I dont see this where is this ,can you show by query

Quote:
Now... Is there a way to check if a price already exists for that article in the prices table and if so don't calculate it but use the one found?


Use decode/case..when..then ,IS NULL to get this

[Updated on: Wed, 23 September 2009 07:51]

Report message to a moderator

Re: Complicated SQL to Calculate Prices [message #423333 is a reply to message #423330] Wed, 23 September 2009 08:04 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

Thanks for your response.

Sorry the 'Smirnoff' was a bad example as this isn't the case for it on the prices table, but an example which is, is the Merlot.

If I run my query for Pricelevel ID 1 it returns 9 records. The Merlot is assoicated to 3 UOM Units (175ml, 250ml, Bottle) and 2 Pricelevels, so I should only get 6 records, but because the prices for the UOM's are in the Prices table I get an additional 3.

My Query: -

SELECT      a.code, a.description, a.uom_group_id, uu.ID, uu.factor,
            uu.display_description, pl.ID pricelevel_id,
            case when   pl.calculated_price_percent is null then
                case when   factor is null then
                            price else
                            round(price * factor, 2)
                end
            else
                case when   factor is null then
                            round(price - (price * abs(pl.calculated_price_percent / 100)), 2) else
                            round((price * factor) - (price * factor) * (abs(pl.calculated_price_percent) / 100), 2)
                end
            end price
FROM        articles a, uom_groups ug, uom_units uu, prices p, pricelevels pl
WHERE       a.uom_group_id = ug.ID (+)
AND         uu.uom_group_id (+) = ug.ID
AND         p.article_id = a.ID
AND         a.description IN ('Merlot')
AND         pl.ID IN (1)
ORDER BY    1


My Results: -

http://imgur.com/qnffy.jpg

The prices which have been entered in the prices table are: -

3.75 - 175ml
4.50 - 250ml
15.75 - Bottle

Although in the results they're not against the correct UOM.

Quote:
Dude you need to improve you data model in that case..
because improper design can lead to costly workarounds in future


I know, although not mine, I'm just being tasked with trying to get a query together which I can pull a Price List from.

Regards
Re: Complicated SQL to Calculate Prices [message #423339 is a reply to message #423333] Wed, 23 September 2009 08:27 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
thats because you are missing a join

select * 
FROM  articles a ,uom_groups ug,uom_units uu,prices p
where ug.ID= a.uom_group_id
and uu.UOM_GROUP_ID=ug.ID
and p.article_id = a.ID
and p.uom_unit_id=uu.ID
and a.description IN ('Merlot')


and p.uom_unit_id=uu.ID
Re: Complicated SQL to Calculate Prices [message #423341 is a reply to message #423339] Wed, 23 September 2009 08:43 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
ayush_anand wrote on Wed, 23 September 2009 13:27
thats because you are missing a join

select * 
FROM  articles a ,uom_groups ug,uom_units uu,prices p
where ug.ID= a.uom_group_id
and uu.UOM_GROUP_ID=ug.ID
and p.article_id = a.ID
and p.uom_unit_id=uu.ID
and a.description IN ('Merlot')


and p.uom_unit_id=uu.ID


Thanks, that has now thrown up another thing...

If I run the query for 'Merlot' & 'Peroni', it only returns the Merlot.

Query: -

SELECT      a.code, a.description, a.uom_group_id, uu.ID, uu.factor,
            uu.display_description, pl.ID pricelevel_id,
            case when   pl.calculated_price_percent is null then
                case when   factor is null then
                            price else
                            round(price * factor, 2)
                end
            else
                case when   factor is null then
                            round(price - (price * abs(pl.calculated_price_percent / 100)), 2) else
                            round((price * factor) - (price * factor) * (abs(pl.calculated_price_percent) / 100), 2)
                end
            end price
FROM        articles a, uom_groups ug, uom_units uu, prices p, pricelevels pl
WHERE       a.uom_group_id = ug.ID (+)
AND         uu.uom_group_id (+) = ug.ID
AND         p.article_id = a.ID
and         p.uom_unit_id (+) = uu.ID 
AND         a.description IN ('Merlot')
AND         pl.ID IN (1, 2)
ORDER BY    1


Also, the prices shown for the Merlot seem rather ambiguous, I'll have to look at the formula to see where these are coming from.

Regards

[Updated on: Wed, 23 September 2009 08:44]

Report message to a moderator

Re: Complicated SQL to Calculate Prices [message #423343 is a reply to message #423341] Wed, 23 September 2009 09:00 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Quote:
Also, the prices shown for the Merlot seem rather ambiguous, I'll have to look at the formula to see where these are coming from.


Hi,

I know where these are from, this is because I have a price already in the prices table for that UOM, so it doesn't need calculating, which leads me back to my earlier question...

I'm taking the entered price of 4.50 (250ml) Factor of 1.43 and multiplying it by the factor giving me 6.44 when I want the 4.50.

Would help if recap on how prices are saved to the prices table and when not?

Regards
Re: Complicated SQL to Calculate Prices [message #423346 is a reply to message #423343] Wed, 23 September 2009 09:09 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
You will only know your prices and formulas as sitting at my end its very hazy

Quote:
I'm taking the entered price of 4.50 (250ml) Factor of 1.43 and multiplying it by the factor giving me 6.44 when I want the 4.50.


1.why isnt the factor 1 in that case??
2.how will a person know when to multiply with factor when not??
Re: Complicated SQL to Calculate Prices [message #423354 is a reply to message #423346] Wed, 23 September 2009 09:45 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Quote:
1.why isnt the factor 1 in that case??
2.how will a person know when to multiply with factor when not??


Right, you'll have to bear with me on this...

In the database there are two types of articles; ones not assoicated to a UOM Group, and those that are.

Article Not Associated

My Peroni is just sold by the each, below is how the prices for it are displayed to a user: -

http://imgur.com/XmJWg.jpg

Article Associated to UOM Group

The Merlot is sold by 175ml, 250ml & Bottle and is associated to the UOM Group 'Wines' which in turn has 3 UOM Units with the following factors 1 (175ml, which is the default selling unit), 1.43 (250ml) & 4.29 (Bottle) and this is how the user sees the Merlot

http://imgur.com/P3wW3.jpg

Now the only price that is saved to the prices table is the one for the 'System Main Pricelevel' this is then used to calculate all the other prices for the pricelevels shown (In Orange). You can override a calculated price in the grid, if this is done then that price is saved to the prices table.

So for Peroni I only have one price, that for the 'System Main Pricelevel'.

The Merlot however is slightly different, the price of 3.75 is entered for the 'Default' UOM (175ml) and on the other UOM Tabs (250ml & Bottle) it is calculated based on this price against that UOM factor. So originally I would have had 5.36 for the 250ml (3.75 * 1.43) and 16.09 for the Bottle (3.75 * 4.29) against the System Main Pricelevel price.

I chose to override this price to 4.50 & 15.75 respectively, and as a result 3 prices are saved in the prices table for the Merlot.

I could change a price for one of the pricelevels e.g. Peroni Happy Hour to 2.85 instead of the calculated 2.88, in which case in the prices table there would be 2 prices for the Peroni.

I hope this makes sense when I read it back, Confused lol

I hope that answers your first question as to why the factor wasn't 1. Also, no person needs to know the factor once they have been configured, just me for when I pull the data out in the query.

My goal is to have a query that will enable me to pull out a 'Price List', which should calculate a price where it's not in the prices table and use the saved price if there is one.

There is always at least 1 price for any article in the prices table.

I didn't think it would be easy and with my limited SQL I'm finding a real struggle to say the least!!

I hope my babbling is of some help and clears somethings up, or maybe not?

Thanks & Regards
Re: Complicated SQL to Calculate Prices [message #423407 is a reply to message #423354] Thu, 24 September 2009 00:56 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
If I run the query for 'Merlot' & 'Peroni', it only returns the Merlot.

select *
FROM  articles a ,uom_groups ug,uom_units uu,prices p
where ug.ID(+)= a.uom_group_id 
and uu.UOM_GROUP_ID(+)=ug.ID
and p.article_id = a.ID
and nvl(p.uom_unit_id,123456789) =nvl(uu.ID,123456789)
and a.description IN ('Merlot','Peroni')


hope this helps..

SELECT a.*, ug.*, uu.*, p.*, pl.*, p.price p, factor f,
       CASE
          WHEN p.uom_unit_id IS NULL
             THEN p.price
          ELSE decode(is_default,1,price * factor,price)
       END unit_price_without_discount
  FROM articles a, uom_groups ug, uom_units uu, prices p, pricelevels pl
 WHERE ug.ID(+) = a.uom_group_id
   AND uu.uom_group_id(+) = ug.ID
   AND p.article_id = a.ID
   AND NVL (p.uom_unit_id, 123456789) = NVL (uu.ID, 123456789)
   AND a.description IN ('Merlot', 'Peroni')
   AND pl.ID IN (1, 2)

you can do something like this

But I will ask you to study Normalisation and joins first.
We can only give you clues or guidelines .We cant solve the problems for you

[Updated on: Thu, 24 September 2009 01:39]

Report message to a moderator

Previous Topic: storing images
Next Topic: calculate on calculated field
Goto Forum:
  


Current Time: Sat Dec 03 01:16:16 CST 2016

Total time taken to generate the page: 0.15878 seconds