Tricky Join Question

  • I need to calculate Cost of Goods sold for a particular item.  Basically, I have 2 tables. One table stores the item sold by transaction date and the other stores a history of cost adjustments for an item.  Keep in mind this a very simple example.  We actually process several thousand transactions with any number of items sold per day.

     

    Below is an example of the tables

    -- Build CostHist table

    Select

    Item = 1, Cost = 1.00, CostDate = '1/1/2007'

    Into

    #CostHist

    Union

    All

    Select

    1, 1.50, '2/1/2007'

    Union

    All

    Select

    1, 1.00, '3/1/2007'

    -- Build Sales table

    Select

    SaleID = 1, Item = 1, SaleDate = '1/2/2007'

    Into

    #Sales

    Union

    All

    Select

    2, 1, '1/4/2007'

    Union

    All

    Select

    3, 1, '2/10/2007'

    Union

    All

    Select

    4, 1, '3/15/2007'

    Question:  How much did item 1 cost for sale 3?

    Desired answer: Item 1 cost 1.00 for sale 1.50

  • Please try the below given script

     

     

    WITH CHDetails(Item, Cost, CostDateFrom, CostDateTo) AS

    (

          SELECT      Item,

                      Cost,

                      CostDate,

                      ISNULL(

                            (SELECT     MIN(CostDate)

                             FROM #CostHist AS ch

                             WHERE      (CostDate > #CostHist.CostDate)

                                  AND (Item = #CostHist.Item)),

                            CONVERT(varchar, GETDATE(), 103)) AS CostDateTo

          FROM  #CostHist

    )

    SELECT      #Sales.SaleID,

                #Sales.Item,

                #Sales.SaleDate,

                CostTable.Cost

    FROM  #Sales

          INNER JOIN CHDetails AS CostTable

                ON #Sales.Item = CostTable.Item

    WHERE #Sales.SaleID =

        AND #Sales.SaleDate

              BETWEEN CostTable.CostDateFrom AND CostTable.CostDateTo

  • Thanks for the suggestion.  It worked pretty well.  I have a question about the use of CTEs.  Does it only get the data requested through the join or does it run against the entire dataset?

  • CTE is a temporary view defined within the scope of an executing statement; CTEs are almost same as the derived tables

     

    To know more about CTE, please read this article

     

    http://www.sqlservercentral.com/columnists/sSampath/commontableexpressionsinsqlserver2005.asp

  • Are you using SQL Server 2005?  Your post is in the SQL Server 7,2000 T-SQL section....

    Here's the cost w/o the CTE:

    SELECT Cost

    FROM #CostHist ch

        INNER JOIN (

                    SELECT s.SaleID, s.Item, MAX(CostDate) AS MaxCostDate

                    FROM #CostHist ch

                        INNER JOIN #Sales s

                        ON ch.Item = s.Item AND ch.CostDate < s.SaleDate

                    WHERE s.SaleID = 3

                    GROUP BY s.SaleID, s.Item

                    ) t

        ON ch.Item = t.Item AND ch.CostDate = t.MaxCostDate

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Sorry , I didn’t notice that the post is in the SQL Server 7, 2000 T-SQL section.

     

    We can create the same script using a derived table

  • DECLARE @SaleId  int

    SET  @SaleId=3

    SELECT    TOP 1 'Final answers is:  '+ convert(varchar,C1.Cost)

    FROM       #CostHist C1,#CostHist C2

    WHERE     (C1.CostDate<>C2.CostDate)

     AND

     (

      (SELECT SaleDate FROM #sales WHERE SaleId= @SaleId) BETWEEN C1.CostDate AND C2.CostDate

      )

    ORDER BY C1.CostDate desc,C2.CostDate desc

    -- Run the above code and get the result

  • I appreciate everyone's input.  I tried the CTE solution and it worked, but with 1 major flaw...  It blew our TEMPDB to 50gb under a normal load (a few million records).  I'm still searching for viable solution.  I guess I should have explained in my example that we would be doing this in a batch process.  Again, thanks for all your help.

Viewing 8 posts - 1 through 7 (of 7 total)

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