none
Advanced Join Query

    Question

  • Greeting guys  i have  4 tables uploaded under ,

    im looking to get the product cost based on the order Date on the table SalesOrderheader ,
    The Problem there is some product doesnt Exist on the productCosthistory since their cost didn't change or ended !

    is there a way where i can make a condition if the productID doesnt Exist on the table ProdcutCostHistory than take the ProductCost that exist on the product table

    and thanks !

    table ProductCostHistory

    table SalesOrderHeader

    table SalesOrderDetail 

    table product :

    Monday, April 22, 2019 5:27 PM

Answers

  • Hi Ramyzo92,

     

    Would you like this one ?

    If Object_ID('Product','U') Is Not Null Drop Table Product
    If Object_ID('PRODUCT_COST_HISTORY','U') Is Not Null Drop Table PRODUCT_COST_HISTORY
    If Object_ID('Sales','U') Is Not Null Drop Table Sales
    go
    create table Product
    (
    [PRODUCT ID] int, 
    PRODUCT_NAME varchar(20),
    cost int ,
    price int 
    )
    insert into Product values 
    (1,'Car',60,70),
    (2,'Pen',3,5),
    (3,'Computer',10,13),
    (4,'House',120,150)
    create table PRODUCT_COST_HISTORY  
    (
    [PRODUCT ID] varchar(10) ,
    Cost int ,
    START_DATE  date ,
    END_DATE date
    )
    insert into PRODUCT_COST_HISTORY values 
    (1,50,'1/1/2010','1/25/2010'),
    (1,60,'1/26/2010',NULL),
    (2,3,'1/3/2010',NULL)
    create table Sales 
    (
    SALES_ID   int, 
    PRODUCT_ID  int ,
    ORDER_DATE  varchar(10) 
    )
    insert into Sales values 
    (1,2,'1/4/2010'),
    (2,1,'1/14/2010'), 
    (3,1,'1/27/2010'),
    (4,3,'1/1/2010')
    
    
    
    select a.[PRODUCT ID],a.PRODUCT_NAME,
    case when c.cost is not null then c.Cost else a.cost end as cost,
    a.price,b.ORDER_DATE
    from Product a
    left join Sales b on a.[PRODUCT ID]=b.PRODUCT_ID
    left join PRODUCT_COST_HISTORY c on a.[PRODUCT ID]=c.[PRODUCT ID]and b.ORDER_DATE between c.START_DATE and (case when c.END_DATE is null then cast('21991231'as date) else c.END_DATE end) 
    /*
    PRODUCT ID  PRODUCT_NAME         cost        price       ORDER_DATE
    ----------- -------------------- ----------- ----------- ----------
    1           Car                  50          70          1/14/2010
    1           Car                  60          70          1/27/2010
    2           Pen                  3           5           1/4/2010
    3           Computer             10          13          1/1/2010
    4           House                120         150         NULL
    */
    


    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Ramyzo92 Wednesday, April 24, 2019 1:57 PM
    Wednesday, April 24, 2019 8:03 AM

All replies

  • Hi Ramyzo92,

     

    Could you  please share us your table structure and some sample data(the script of CREATE TABLE AND INSERT INTO) along with your expected result? So that we’ll get a right direction and make some test.

     

    When you post your issue, please refer POSTING TIPS - Code, Images, Hyperlinks, Details.

     

    In your four tables , is there any relationship among them ? For example, the column 'ProductID' in table product  is equal to  the column 'ProductID' in table ProductCostHistory. Could you please share us more ?

     

    As you said that 'is there a way where i can make a condition if the productID doesnt Exist on the table ProdcutCostHistory than take the ProductCost that exist on the product table', did you mean that when the column 'productID' is not existed in table ProductCostHistory and the cost will be shown 0 ?

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 23, 2019 1:54 AM
  • Greeting Rachel , thanks for your Explanation

    I'm working with the "Adventureworks2016"  database of Microsoft
    here a link :  https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-2017

    Or you want the table script ? it's too long to post the 4 of them i guess !

    yes , There is a relation between the 4 tables ! Product ID is the same as ProductID in ProductCostHistory !
    In the Product table there is a column named StandardCost , if any product that his cost changed it will automatically go to the ProductCostHistory ! which mean some Product wont Exist in the ProductCostHistory !
    only product that their standard cost change they will be added to the ProductCostHistory 
    for the other 2 tables it's where u can link each sale with a (Product and it's order date )
    i hope u get my point !  


    Tuesday, April 23, 2019 8:43 AM
  • Hi Ramyzo92,

     

    Thank you for your posting .

     

    I tried to work with the "Adventureworks2016"  database of Microsoft and I also found  the same table with yours. And  Could you please share us your expected result?

     

    And it might be difficult to describe your requirement or expected result clearly, could you please make a simple example to make your expected result more simple?

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 23, 2019 9:55 AM
  • Okay  Clear :

    So let's Say i have a table Called Product every product have it own standard cost and it's price

    PRODUCT ID | PRODUCT_NAME | COST | PRICE

    1                      Car                      60          70

    2                      Pen                      3            5

    3                      Computer            10           13

    4                      House                  120         150

    and we have another table Called PRODUCT_COST_HISTORY  if a product his cost changed it will appear on this table

    PRODUCT ID | COST | START_DATE | END_DATE

    1                     50         1/1/2010        1/25/2010

    1                     60          1/26/2010       NULL

    2                     3           1/3/2010         NULL

    Another table called Sales contain :

    SALES_ID | PRODUCT_ID | ORDER_DATE

    1                  2                    1/4/2010

    2                  1                    1/14/2010   

    3                  1                    1/27/2010

    4                  3                    1/1/2010

    Rachel what i want as a result is the Product Cost of the product Based on the order date ! but what i want to add is a condition like : if the product ID Doesn't Exist on the product history get the cost from the product cost from the table product else get the cost from the table cost history based on the order date !

    i hope u undrestand better my point like that and thanks

       

    Tuesday, April 23, 2019 9:17 PM
  • Hi Ramyzo92,

     

    Would you like this one ?

    If Object_ID('Product','U') Is Not Null Drop Table Product
    If Object_ID('PRODUCT_COST_HISTORY','U') Is Not Null Drop Table PRODUCT_COST_HISTORY
    If Object_ID('Sales','U') Is Not Null Drop Table Sales
    go
    create table Product
    (
    [PRODUCT ID] int, 
    PRODUCT_NAME varchar(20),
    cost int ,
    price int 
    )
    insert into Product values 
    (1,'Car',60,70),
    (2,'Pen',3,5),
    (3,'Computer',10,13),
    (4,'House',120,150)
    create table PRODUCT_COST_HISTORY  
    (
    [PRODUCT ID] varchar(10) ,
    Cost int ,
    START_DATE  date ,
    END_DATE date
    )
    insert into PRODUCT_COST_HISTORY values 
    (1,50,'1/1/2010','1/25/2010'),
    (1,60,'1/26/2010',NULL),
    (2,3,'1/3/2010',NULL)
    create table Sales 
    (
    SALES_ID   int, 
    PRODUCT_ID  int ,
    ORDER_DATE  varchar(10) 
    )
    insert into Sales values 
    (1,2,'1/4/2010'),
    (2,1,'1/14/2010'), 
    (3,1,'1/27/2010'),
    (4,3,'1/1/2010')
    
    
    
    select a.[PRODUCT ID],a.PRODUCT_NAME,
    case when c.cost is not null then c.Cost else a.cost end as cost,
    a.price,b.ORDER_DATE
    from Product a
    left join Sales b on a.[PRODUCT ID]=b.PRODUCT_ID
    left join PRODUCT_COST_HISTORY c on a.[PRODUCT ID]=c.[PRODUCT ID]and b.ORDER_DATE between c.START_DATE and (case when c.END_DATE is null then cast('21991231'as date) else c.END_DATE end) 
    /*
    PRODUCT ID  PRODUCT_NAME         cost        price       ORDER_DATE
    ----------- -------------------- ----------- ----------- ----------
    1           Car                  50          70          1/14/2010
    1           Car                  60          70          1/27/2010
    2           Pen                  3           5           1/4/2010
    3           Computer             10          13          1/1/2010
    4           House                120         150         NULL
    */
    


    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Ramyzo92 Wednesday, April 24, 2019 1:57 PM
    Wednesday, April 24, 2019 8:03 AM
  • Thanks Rachel

    • Edited by Ramyzo92 Wednesday, April 24, 2019 1:57 PM
    Wednesday, April 24, 2019 1:57 PM