Post reply

Help with SELECT involving 2 tables !!!

  • September 27, 2007 at 2:52 pm

    #198405

    Hi All,

    I have a table called #Promo with following fields and types.

    StartDate (datetime)

    EndDate (datetime)

    ClientOutletID (nvarchar)

    ClientProductID (nvarchar)

    PromoPrice (decimal)

    PromoCost (decimal)

    PromotionType (nvarchar)

    The records are like this: (each field is seperated by comma):

    StartDate, EndDate, ClientOutletID, ClientProductID, PromoPrice, PromoCost, PromotionType

    2005-12-10, 2005-12-17, 00402, 000-24200-04917, 2.99, 2.33,Price Promotion

    I have another table called #sh with following fields & types.

    Saledate (datetime)

    ClientProductID (nvarchar)

    ClientOutletID (nvarchar)

    SaleCost (decimal)

    SalePrice (decimal)

    The records are like this in #sh table: (each field is seperated by comma):

    Saledate,ClientProductID,ClientOutletID,SaleCost,SalePrice

    2005-12-03, 000-11110-37050, 00402,1.02,2.97

    2005-12-24, 000-11110-37050, 00402,1.03,2.99

    2006-02-25, 000-11110-37050, 00402,.95,2.99

    2006-05-13, 000-11110-37050, 00402,.95,2.99

    Please bear in mind that I want to join these 2 tables based on ClientOutletID and ClientProductID (both these fields exist in each table)

    In my select I want all fields from #Promo table and #sh.SalePrice and #sh.SaleCost from #sh table where the #sh.Saledate is the earliest date available but greater than #promo.EnDDate.

    So in the above example my OUTPUT would be:

    StartDate, EndDate, ClientOutletID, ClientProductID, PromoPrice, PromoCost, PromotionType, SaleCost, SalePrice

    2005-12-10, 2005-12-17, 00402, 000-24200-04917, 2.99, 2.33,Price Promotion,1.03,2.99

    Explanation for the output:

    The script will check if its same ClientoutletID and same ClientProductID which in this example it is. Then it will check the earliest #promo.EnDDate which is greater than #sh.Saledate. In this example it is 2005-12-24. And then it will get the #sh.SaleCost(1.03) and #sh.SalePrice(2.99) on 2005-12-24.

    If there's no earliest #promo.EnDDate which is greater than #sh.Saledate then in the SELECT SalePrice will be same as PromoPrice and SaleCost will be same as PromoCost

    In any case the SELECT should return all th records from #promo table.

    How can I write a SQL script to do this.

    Any help would be highly appreciated.

    Thanks,

    Zee

  • September 27, 2007 at 7:16 pm

    #736980

    What have you tried so far?

    This looks a lot like a homework question, so please bear with me;

    in order to get information from two tables, you want to use a JOIn;

    typical join types are INNER JOIN,LEFTOUTER JOIN,RIGHT OUTER JOIN and FULL OUTER JOIN.

    which did you use,and what results were not right?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 28, 2007 at 1:54 am

    #737030

    Lowell,

    I guess it should be like this:

    SELECT p.*, COALESCE(s.SalePrice, p.SalePrice) AS Price, COALESCE(s.SaleCost, p.SaleCost) AS Cost

    FROM [#PROMO] p

    LEFT JOIN [#sh] s

    ON s.ClientOutletID = p.ClientOutletID

    AND s.ClientProductID = p.ClientProductID

    AND s.SaleDate = ( SELECT min ( i.saleDate )

    FROM [#sh] i

    WHERE i.ClientOutletID = p.ClientOutletID

    AND i.ClientProductID = p.ClientProductID

    AND i.SaleDate >= p.EndDate

    )

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply