August 23, 2014 at 7:40 am
Hello,
We have a Item table and a Price table. Structure is mentioned below. An Item need to be matched with the Price table (or vice versa is also fine) and get the Cost from the Price table. The challenge (which I feel 🙂 ) is, mapping or Columns to be looked up between these two tables are not fixed.
For an Item, Country, City and Number of Days columns can have a value or can be null. For ex, Paper, US, New York <10 days is one combination which should be matched to the Item table. For Paper, UK, NULL, <5 days is another combination which need to be checked.
So it is not a static look up, it is kind of dynamic look up of columns between the two tables. Kindly suggest how to proceed with this scenario.
Rgds
Jim
Price Table
Item Country City Number of Days Cost
Paper US New York <10 100
Paper UK <5 150
Paper Chicago >10 200
Pen China <10 250
Item Table
Item Country City Number of Days
Paper US New York 5
Paper UK London 3
Paper US Chicago 15
Pen China Shangai 5
Paper China Beijing 15
Paper US Chicago 5
August 23, 2014 at 9:30 am
Please provide ready to use sample data including your expected result as described in the first link in my signature.
If possible, in your price table separate the number of days and the "direction" in two separate columns to and change the [NumberOfDays] column to the appropriate data type (e.g. INTEGER).
Furthermore, please describe how to handle multiple results, e.g. what should the result set look like if you'd add the following row to your item table:
Paper US New York 7
Since your Price table would query for <10 days, you'll have two matching rows.
August 23, 2014 at 4:16 pm
This is a fundamentally flawed design, including operators in a column, let alone combining operators and values in a single column is seriously wrong. First suggestion is to amend the schema to a proper relational design.
😎
Regardless of the design, I could not resist putting together a quick query that I think will work
USE tempdb;
GO
DECLARE @SQL_STR NVARCHAR(MAX) = N'';
;WITH Price_Table(Item, Country, City, [Number of Days], Cost )
AS
( SELECT Item, Country, City, [Number of Days], Cost FROM
(VALUES
('Paper' ,'US' ,'New York','<10' ,100)
,('Paper' ,'UK' ,NULL ,'<5' ,150)
,('Paper' ,NULL ,'Chicago' ,'>10' ,200)
,('Pen' ,'China' ,NULL ,'<10' ,250)
)AS X(Item, Country, City, [Number of Days], Cost )
)
,Item_Table(Item, Country, City, [Number of Days])
AS
( SELECT Item, Country, City, [Number of Days] FROM
(VALUES
('Paper' ,'US' ,'New York' , 5)
,('Paper' ,'UK' ,'London' , 3)
,('Paper' ,'US' ,'Chicago' ,15)
,('Pen' ,'China' ,'Shangai' , 5)
,('Paper' ,'China' ,'Beijing' ,15)
,('Paper' ,'US' ,'Chicago' , 5)
) AS X(Item, Country, City, [Number of Days])
)
SELECT @SQL_STR = N'SELECT Item,Country,City,Cost FROM (VALUES' + STUFF((SELECT
N',('+ CHAR(39) + CAST(ISNULL(PT.Item ,'') AS NVARCHAR(20)) + NCHAR(39) +
N',' + CHAR(39) + CAST(ISNULL(PT.Country,'') AS NVARCHAR(20)) + NCHAR(39) +
N',' + CHAR(39) + CAST(ISNULL(PT.City ,'') AS NVARCHAR(20)) + NCHAR(39) +
N',' + CHAR(39) + CAST(ISNULL(PT.Cost ,'') AS NVARCHAR(20)) + NCHAR(39) +
',(SELECT CASE WHEN ' + CAST(IT.[Number of Days] AS NVARCHAR(12))
+ N' ' + SUBSTRING(PT.[Number of Days],1,1)
+ N' ' + REPLACE(REPLACE(PT.[Number of Days],'>',''),'<','')
+ N' THEN 1 ELSE 0 END))'
FROM Price_Table PT
INNER JOIN Item_Table IT
ON PT.Item = IT.Item
WHERE ((PT.City = IT.City AND PT.Country IS NULL)
OR (PT.Country = IT.Country AND PT.City = IT.City)
OR (PT.City IS NULL AND PT.Country = IT.Country)
)
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)'),1,1,'') + N') AS X(Item,Country,City,Cost,Valid) WHERE X.Valid = 1;';
EXECUTE sp_executesql @SQL_STR;
Results
Item Country City Cost
----- ------- -------- ----
Paper US New York 100
Paper UK 150
Paper Chicago 200
Pen China 250
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply