Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2011
    Posts
    19

    Unanswered: How to get prices from table where date not exists

    Hope this is an easy on for those in the know...

    I have 2 test tables one for stock and one for prices.

    I need to select all the rows from Stock but also the price on the stock but the price table doesn't always have the date, so I can not do stock date = price date.

    What it needs to do is if the Stoc Date isn't in the price table use the price before... this would also have to be able to run on my rows...

    Any ideas, I've been trying for 2 hours and just going in circles...

    Thanks,

    -- Create Test Table (not sure if dates USA or UK format on your machine...

    CREATE TABLE [dbo].[TheStockLedger](
    [EntryID] [int] NULL,
    [TheDate] [datetime] NULL,
    [StoreCode] [nvarchar](50) NULL,
    [Item] [nvarchar](50) NULL,
    [ColorCode] [nvarchar](50) NULL,
    [StockQty] [numeric](18, 0) NULL
    )
    GO

    INSERT INTO [TheStockLedger]
    VALUES ('1','01-01-2013','ABC','CA001','BLUE','100')
    ;
    INSERT INTO [TheStockLedger]
    VALUES ('2','02-01-2013','ABC','CA001','BLUE','50')
    ;
    INSERT INTO [TheStockLedger]
    VALUES ('3','03-01-2013','ABC','CA001','BLUE','25')
    ;
    INSERT INTO [TheStockLedger]
    VALUES ('4','04-01-2013','ABC','CA001','BLUE','100')
    ;
    INSERT INTO [TheStockLedger]
    VALUES ('5','05-01-2013','ABC','CA001','BLUE','50')
    ;
    INSERT INTO [TheStockLedger]
    VALUES ('6','10-01-2013','ABC','CA001','BLUE','4')
    ;
    INSERT INTO [TheStockLedger]
    VALUES ('7','11-01-2013','ABC','CA001','BLUE','2')
    ;

    -- Create Test Price Table

    CREATE TABLE [dbo].[ThePriceList](
    [Item] [nvarchar](50) NULL,
    [ColorCode] [nvarchar](50) NULL,
    [TheDate] [datetime] NULL,
    [SalesPrice] [decimal](18, 2) NULL
    )
    GO

    INSERT INTO [ThePriceList]
    VALUES ('CA001','BLUE','01-01-2013','99.99')
    ;

    INSERT INTO [ThePriceList]
    VALUES ('CA001','BLUE','05-01-2013','12.99')
    ;

    INSERT INTO [ThePriceList]
    VALUES ('CA001','BLUE','07-01-2013','1.99')
    ;

    -- Run Query but need to do something with Date join...

    SELECT a.[EntryID]
    ,a.[TheDate]
    ,a.[StoreCode]
    ,a.[Item]
    ,a.[ColorCode]
    ,a.[StockQty]

    ,s.[SalesPrice]

    FROM [TheStockLedger] a

    LEFT OUTER JOIN [ThePriceList] s
    ON a.[Item] = s.[Item]
    AND a.[ColorCode] = s.[ColorCode]

    AND a.[TheDate] = s.[TheDate]

    GO

  2. #2
    Join Date
    Jun 2011
    Posts
    19

    Picture of current select

    This is a picture of current selct with the nulls which need price for date before.
    Attached Thumbnails Attached Thumbnails sql pic.JPG  

  3. #3
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    SELECT a.[EntryID]
          ,a.[TheDate]
          ,a.[StoreCode]
          ,a.[Item]
          ,a.[ColorCode]
          ,a.[StockQty]
    
          ,(select top 1 s.[SalesPrice]
            from [ThePriceList] s
            where s.[Item] = a.[Item] AND 
                  s.[ColorCode] = a.[ColorCode] AND 
                  s.[TheDate] <= a.[TheDate]
            order by s.[TheDate]) as SalesPrice
    
    FROM [TheStockLedger] a

    Hope this helps.

  4. #4
    Join Date
    Jun 2011
    Posts
    19
    Thanks Imex for the code.

    I just tried it on my test data and it returned 99.99 for every row.
    the 05-JAN-13 row should be 12.99, the 10-JAN-12 12.99 and 11-JAN-13 1.99.

    I'll see if I can play around with your code and ma:make it work...

  5. #5
    Join Date
    Jun 2011
    Posts
    19
    Sorry I put 10-JAN-12 12.99 it was meant to be year 10-JAN-13 12.99.

  6. #6
    Join Date
    Apr 2012
    Posts
    213
    Missed the descending order in the sub-query:

    Code:
          ,(select top 1 s.[SalesPrice]
            from [ThePriceList] s
            where s.[Item] = a.[Item] AND 
                  s.[ColorCode] = a.[ColorCode] AND 
                  s.[TheDate] <= a.[TheDate]
            order by s.[TheDate] desc) as SalesPrice
    Hope this helps.

  7. #7
    Join Date
    Jun 2011
    Posts
    19
    OK great will give it a go. Thanks.

  8. #8
    Join Date
    Jun 2011
    Posts
    19
    Many Thanks Imex that code now works perfectly.

  9. #9
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Thanks for the DDL, but it is all wrong. You have no keys and no way to have keys; no constraints, Declarative Referential Integrity, data types, etc. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats and not either of the dialects you used.

    What you did post garbage. I see the magical NVARCHAR(50) that ACCESS programers use, why did you put “the_” in the table and column names? Etc.

    Let's try to clean it up first:

    I have 2 test tables one for stock and one for prices. I need to select all the rows from Stock but also the price on the stock but the price table doesn't always have the date, so I can not do stock date = price date.
    That is not how to build a history table. There are some DDL tricks to assure that there are no gaps in the dates, but I am not sure you are ready yet.

    CREATE TABLE Price_History
    (sku CHAR(10) NOT NULL
    REFERENCES Stock(sku)
    ON UPDATE CASCADE,
    price_start_date DATE NOT NULL,
    price_end_date DATE, --- null is current
    CHECK (price_start_date <= price_end_date),
    unit_price DECIMAL (12,2) NOT NULL
    CHECK (unit_price > 0.00),
    PRIMARY KEY (sku, price_start_date));

    The items you are selling are entities and they get a table with their attributes.

    CREATE TABLE Stock
    (sku CHAR(10) NOT NULL,
    item_name VARCHAR(50) NOT NULL,
    color_code CHAR(5) NOT NULL, --- which industry standard?
    ..);

    I will assume that you do a store by store inventory on a daily basis.

    CREATE TABLE Daily_Inventory
    (store_nbr CHAR(5) NOT NULL,
    sku CHAR(10) NOT NULL
    REFERENCES Stock(sku)
    ON UPDATE CASCADE,
    inventory_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (store_nbr, sku, inventory_date),
    onhand_qty INTEGER NOT NULL
    CHECK (onhand_qty >= 0),
    ..);

    To find the value of the inventory on a particular date,

    SELECT I.sku, @in_report_date, SUM(I.onhand_qty * S.unit_price) AS inventory_value
    FROM Inventory AS I,
    Price_History AS H
    WHERE @in_report_date
    BETWEEN H.price_start_date
    AND COALESCE (H.price_end_date, CURRENT_TIMESTAMP)
    AND I.inventory_date = @in_report_date
    AND H.sku = I.sku
    GROUP BY I.sku;

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    His method of implementing historical records is fine. I've implemented many methods, and finally settled on the one he is using as the best.

    I would, however, suggest that he consider storing the price at the date of the stock acquisition in the stock table itself. It is a snapshot of the item price at the time the stock was acquired, and is an attribute of the stock item.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2011
    Posts
    19
    Hi Celko,

    Thanks for your post, I'll have to digest it a bit to see what it is doing but my tables above are only test tables to make the example easy to understand, they are not my live tables which contain many fields also with keys...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •