Author |
Topic |
rogerclerkwell
Yak Posting Veteran
85 Posts |
Posted - 2013-02-17 : 11:53:05
|
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)GOINSERT 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 TableCREATE TABLE [dbo].[ThePriceList]( [Item] [nvarchar](50) NULL, [ColorCode] [nvarchar](50) NULL, [TheDate] [datetime] NULL, [SalesPrice] [decimal](18, 2) NULL)GOINSERT 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 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-17 : 12:25:00
|
Use OUTER APPLY instead of LEFT JOIN like shown below.SELECT a.[EntryID], a.[TheDate], a.[StoreCode], a.[Item], a.[ColorCode], a.[StockQty], s.[SalesPrice]FROM [TheStockLedger] a OUTER APPLY( SELECT TOP (1) SalesPrice FROM [ThePriceList] s WHERE a.[Item] = s.[Item] AND a.[ColorCode] = s.[ColorCode] AND a.[TheDate] >= s.[TheDate] ORDER BY s.[TheDate] DESC ) s |
|
|
|
|
|