Returning an attribute from a range of numbers

• jhols1

  Valued Member

  Points: 50

  More actions

  September 22, 2017 at 7:25 am

  #339353

  Hi all! Thanks in advance for the help. I have quite a dilemma for a query and I'm not even sure how to attack it. Basically Table 1 has a unique identifier and a depth. Table 2 has the same unique identifier a range of depths and an attribute (vegetable). We can assume the depth is the start of where each vegetable can be found. For instance, on ID 123 the potato will be found from 500 until 624 then the carrot starts at 625 and so on. As you can see from the example, I'd like 123 at a depth of 700 from Table 1 to return Carrot as the Carrot range is 625 - 799 from Table 2.

  

• Phil Parkin

  SSC Guru

  Points: 246715

  More actions

  September 22, 2017 at 8:16 am

  #1960500

  jhols1 - Friday, September 22, 2017 7:25 AM

  Hi all! Thanks in advance for the help. I have quite a dilemma for a query and I'm not even sure how to attack it. Basically Table 1 has a unique identifier and a depth. Table 2 has the same unique identifier a range of depths and an attribute (vegetable). We can assume the depth is the start of where each vegetable can be found. For instance, on ID 123 the potato will be found from 500 until 624 then the carrot starts at 625 and so on. As you can see from the example, I'd like 123 at a depth of 700 from Table 1 to return Carrot as the Carrot range is 625 - 799 from Table 2.

  

  If you are prepared to take the time to provide DDL and sample data (in the form of INSERT statements) for the above, the solution will be provided by someone here: it's not that difficult.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• Thom A

  SSC Guru

  Points: 99126

  More actions

  September 22, 2017 at 8:18 am

  #1960502

  Welcome to SSC.

  When posting, you should provide data in a consumable format, rather that screenshots, which are more than often not useful for other users. I've done this for you on this occasion, but have a look at the link in my signature for future posts. 🙂

  The below should work for what you need. I've used a CTE and the LEAD function to get the depth range for each vegetable, which then gives you a dataset you can then JOIN on. if you don't understand what's going on, please do ask.
  USE Sandbox;
GO
--Sample Tables
CREATE TABLE Table1 (ID int, Depth int);
CREATE TABLE Table2 (ID int, Vegetable varchar(10), Depth int);
GO
--Sampel data
INSERT INTO Table1
VALUES (123, 700),(456,500);

  INSERT INTO Table2
VALUES
  (123,'Potato',500),
  (123,'Carrot',625),
  (123,'Onion',800),
  (456,'Carrot',325),
  (456,'Onion',450),
  (456,'Radish',680);
GO
--Check the data
SELECT *
FROM Table1;
SELECT *
FROM Table2;
GO
--Solution
WITH DepthRanges AS (
  SELECT ID, Vegetable,
    Depth AS StartDepth,
    --To give an upper limit on the last one, NULL returns are the Start Depth + 300
    -- The -1 to avoid "fence posting" that Drew later mentions that I'd missed in my initial copy paste :(
    LEAD(Depth,1,Depth+300) OVER (PARTITION BY ID ORDER BY Depth) -1 AS EndDepth
  FROM Table2)
SELECT T1.ID, T1.Depth,
   DR.Vegetable
FROM Table1 T1
  JOIN DepthRanges DR ON T1.ID = DR.ID AND T1.Depth BETWEEN DR.StartDepth AND DR.EndDepth;
GO
--Clean up
DROP TABLE Table1;
DROP TABLE Table2;
GO


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• jhols1

  Valued Member

  Points: 50

  More actions

  September 22, 2017 at 8:57 am

  #1960515

  Sorry, I didn't specify that the data is already in a database and tables. I was just using the image as an example. Many thanks to Thom A for giving me the full thing. I am going to work with the solution now.

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  September 22, 2017 at 9:07 am

  #1960518

  Thom A - Friday, September 22, 2017 8:18 AM

  Welcome to SSC.

  When posting, you should provide data in a consumable format, rather that screenshots, which are more than often not useful for other users. I've done this for you on this occasion, but have a look at the link in my signature for future posts. 🙂

  The below should work for what you need. I've used a CTE and the LEAD function to get the depth range for each vegetable, which then gives you a dataset you can then JOIN on. if you don't understand what's going on, please do ask.
  USE Sandbox;
GO
--Sample Tables
CREATE TABLE Table1 (ID int, Depth int);
CREATE TABLE Table2 (ID int, Vegetable varchar(10), Depth int);
GO
--Sampel data
INSERT INTO Table1
VALUES (123, 700),(456,500);

  INSERT INTO Table2
VALUES
  (123,'Potato',500),
  (123,'Carrot',625),
  (123,'Onion',800),
  (456,'Carrot',325),
  (456,'Onion',450),
  (456,'Radish',680);
GO
--Check the data
SELECT *
FROM Table1;
SELECT *
FROM Table2;
GO
--Solution
WITH DepthRanges AS (
  SELECT ID, Vegetable,
    Depth AS StartDepth,
    --To give an upper limit on the last one, NULL returns are the Start Depth + 300
    LEAD(Depth,1,Depth+300) OVER (PARTITION BY ID ORDER BY Depth) AS EndDepth
  FROM Table2)
SELECT T1.ID, T1.Depth,
   DR.Vegetable
FROM Table1 T1
  JOIN DepthRanges DR ON T1.ID = DR.ID AND T1.Depth BETWEEN DR.StartDepth AND DR.EndDepth;
GO
--Clean up
DROP TABLE Table1;
DROP TABLE Table2;
GO


  This suffers from a fencepost error.  When the depth from table 1 exactly matches the depth of an item in table 2, it will return two records instead of one for that item.  This can easily be fixed by using half-closed intervals (>= and <) instead of fully closed intervals(>= and <= [between]).

  Here is another possible solution using Thom's data setup.  I think that the CROSS APPLY will perform better, but that would need to be tested on a much larger set of data.

  SELECT t1.ID, t1.Depth, t2.Vegetable
FROM #Table1 t1
CROSS APPLY (
    SELECT TOP (1) *
    FROM #Table2 t2
    WHERE t2.ID = t1.ID
        AND t2.Depth <= t1.Depth
    ORDER BY t2.Depth DESC
) t2;


  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Thom A

  SSC Guru

  Points: 99126

  More actions

  September 22, 2017 at 9:15 am

  #1960522

  jhols1 - Friday, September 22, 2017 8:57 AM

  Sorry, I didn't specify that the data is already in a database and tables. I was just using the image as an example.

  The data may well be in a table and database on your server, but as I emphasised, that's your server; the user's of SSC don't have access to that database and table. Thus the need to provide the data in a consumable forma;, DDL and DLM, along with expected outputs. My post does this, so that if other users want to contribute they don't need to recreate your data.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Thom A

  SSC Guru

  Points: 99126

  More actions

  September 22, 2017 at 9:16 am

  #1960523

  drew.allen - Friday, September 22, 2017 9:07 AM

  Thom A - Friday, September 22, 2017 8:18 AM

  Welcome to SSC.

  When posting, you should provide data in a consumable format, rather that screenshots, which are more than often not useful for other users. I've done this for you on this occasion, but have a look at the link in my signature for future posts. 🙂

  The below should work for what you need. I've used a CTE and the LEAD function to get the depth range for each vegetable, which then gives you a dataset you can then JOIN on. if you don't understand what's going on, please do ask.
  USE Sandbox;
GO
--Sample Tables
CREATE TABLE Table1 (ID int, Depth int);
CREATE TABLE Table2 (ID int, Vegetable varchar(10), Depth int);
GO
--Sampel data
INSERT INTO Table1
VALUES (123, 700),(456,500);

  INSERT INTO Table2
VALUES
  (123,'Potato',500),
  (123,'Carrot',625),
  (123,'Onion',800),
  (456,'Carrot',325),
  (456,'Onion',450),
  (456,'Radish',680);
GO
--Check the data
SELECT *
FROM Table1;
SELECT *
FROM Table2;
GO
--Solution
WITH DepthRanges AS (
  SELECT ID, Vegetable,
    Depth AS StartDepth,
    --To give an upper limit on the last one, NULL returns are the Start Depth + 300
    LEAD(Depth,1,Depth+300) OVER (PARTITION BY ID ORDER BY Depth) AS EndDepth
  FROM Table2)
SELECT T1.ID, T1.Depth,
   DR.Vegetable
FROM Table1 T1
  JOIN DepthRanges DR ON T1.ID = DR.ID AND T1.Depth BETWEEN DR.StartDepth AND DR.EndDepth;
GO
--Clean up
DROP TABLE Table1;
DROP TABLE Table2;
GO


  This suffers from a fencepost error.  When the depth from table 1 exactly matches the depth of an item in table 2, it will return two records instead of one for that item.  This can easily be fixed by using half-closed intervals (>= and <) instead of fully closed intervals(>= and <= [between]).

  Here is another possible solution using Thom's data setup.  I think that the CROSS APPLY will perform better, but that would need to be tested on a much larger set of data.

  SELECT t1.ID, t1.Depth, t2.Vegetable
FROM #Table1 t1
CROSS APPLY (
    SELECT TOP (1) *
    FROM #Table2 t2
    WHERE t2.ID = t1.ID
        AND t2.Depth <= t1.Depth
    ORDER BY t2.Depth DESC
) t2;


  Drew

  I could have sworn i put a a -1 in the EndDepth..! It's in my SSMS window I still had open, must have copied before updating. I've amended my post.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  September 22, 2017 at 9:17 am

  #1960524

  Just wanted to look at an alternative that appears to perform better:
  --Sample Tables
CREATE TABLE #Table1 (ID int PRIMARY KEY CLUSTERED, Depth int);
CREATE TABLE #Table2 (ID int, Vegetable varchar(10), Depth int, CONSTRAINT IX_Table2_Depth_INCLUDE_Vegetab PRIMARY KEY CLUSTERED (ID, Depth));
GO
--Sampel data
INSERT INTO #Table1
VALUES (123, 700),(456,500);

  INSERT INTO #Table2
VALUES
(123,'Potato',500),
(123,'Carrot',625),
(123,'Onion',800),
(456,'Carrot',325),
(456,'Onion',450),
(456,'Radish',680);
GO

  DBCC FREEPROCCACHE;
GO

  SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

  WITH DepthRanges AS (
SELECT ID, Vegetable,
  Depth AS StartDepth,
  --To give an upper limit on the last one, NULL returns are the Start Depth + 300
  LEAD(Depth,1,Depth+300) OVER (PARTITION BY ID ORDER BY Depth) AS EndDepth
FROM #Table2)
SELECT T1.ID, T1.Depth,
 DR.Vegetable
FROM #Table1 T1
 JOIN DepthRanges DR ON T1.ID = DR.ID AND T1.Depth BETWEEN DR.StartDepth AND DR.EndDepth;
GO

  DBCC FREEPROCCACHE;
GO

  SELECT T1.ID, T1.Depth, V.Vegetable
FROM #Table1 AS T1
    CROSS APPLY (SELECT MAX(Depth) AS MAX_DEPTH FROM #Table2 AS T2 WHERE T2.ID = T1.ID AND T2.Depth <= T1.Depth) AS MD
    CROSS APPLY (SELECT Vegetable FROM #Table2 AS T3 WHERE T3.ID = T1.ID AND T3.Depth = MD.MAX_DEPTH) AS V
ORDER BY T1.ID;
GO

  SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO

  DROP TABLE #Table1;
DROP TABLE #Table2;

  Be sure to remove the DBCC FREEPROCCACHE and SET STATISTICS IO and SET STATISTICS TIME statements before running in production as those are there just to demonstrate the numbers.   See the screenshots attached for the Profiler Trace data.
  

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• jhols1

  Valued Member

  Points: 50

  More actions

  September 22, 2017 at 10:06 am

  #1960544

  This worked like a champ! Many thanks to Drew and everyone for the help. In the future I will make sure to provide everything needed.

  SELECT t1.ID, t1.Depth, t2.Vegetable
FROM #Table1 t1
CROSS APPLY (
  SELECT TOP (1) *
  FROM #Table2 t2
  WHERE t2.ID = t1.ID
   AND t2.Depth <= t1.Depth
  ORDER BY t2.Depth DESC
) t2;

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  September 22, 2017 at 10:17 am

  #1960549

  Thom A - Friday, September 22, 2017 9:16 AM

  I could have sworn i put a a -1 in the EndDepth..! It's in my SSMS window I still had open, must have copied before updating. I've amended my post.

  I would hesitate to hard code the value using a -1, because it's not clear that the depths are expressed as integers in the actual data, so using a -1 might introduce small gaps in the data.  Using a half-closed interval will ALWAYS work.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  September 22, 2017 at 10:23 am

  #1960551

  drew.allen - Friday, September 22, 2017 9:07 AM

  Thom A - Friday, September 22, 2017 8:18 AM

  Welcome to SSC.

  When posting, you should provide data in a consumable format, rather that screenshots, which are more than often not useful for other users. I've done this for you on this occasion, but have a look at the link in my signature for future posts. 🙂

  The below should work for what you need. I've used a CTE and the LEAD function to get the depth range for each vegetable, which then gives you a dataset you can then JOIN on. if you don't understand what's going on, please do ask.
  USE Sandbox;
GO
--Sample Tables
CREATE TABLE Table1 (ID int, Depth int);
CREATE TABLE Table2 (ID int, Vegetable varchar(10), Depth int);
GO
--Sampel data
INSERT INTO Table1
VALUES (123, 700),(456,500);

  INSERT INTO Table2
VALUES
  (123,'Potato',500),
  (123,'Carrot',625),
  (123,'Onion',800),
  (456,'Carrot',325),
  (456,'Onion',450),
  (456,'Radish',680);
GO
--Check the data
SELECT *
FROM Table1;
SELECT *
FROM Table2;
GO
--Solution
WITH DepthRanges AS (
  SELECT ID, Vegetable,
    Depth AS StartDepth,
    --To give an upper limit on the last one, NULL returns are the Start Depth + 300
    LEAD(Depth,1,Depth+300) OVER (PARTITION BY ID ORDER BY Depth) AS EndDepth
  FROM Table2)
SELECT T1.ID, T1.Depth,
   DR.Vegetable
FROM Table1 T1
  JOIN DepthRanges DR ON T1.ID = DR.ID AND T1.Depth BETWEEN DR.StartDepth AND DR.EndDepth;
GO
--Clean up
DROP TABLE Table1;
DROP TABLE Table2;
GO


  This suffers from a fencepost error.  When the depth from table 1 exactly matches the depth of an item in table 2, it will return two records instead of one for that item.  This can easily be fixed by using half-closed intervals (>= and <) instead of fully closed intervals(>= and <= [between]).

  Here is another possible solution using Thom's data setup.  I think that the CROSS APPLY will perform better, but that would need to be tested on a much larger set of data.

  SELECT t1.ID, t1.Depth, t2.Vegetable
FROM #Table1 t1
CROSS APPLY (
    SELECT TOP (1) *
    FROM #Table2 t2
    WHERE t2.ID = t1.ID
        AND t2.Depth <= t1.Depth
    ORDER BY t2.Depth DESC
) t2;


  Drew

  I just tested this on the existing data, and it has less reads than my code.  Well done sir!

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  September 22, 2017 at 11:47 am

  #1960568

  sgmunson - Friday, September 22, 2017 10:23 AM

  drew.allen - Friday, September 22, 2017 9:07 AM

  Thom A - Friday, September 22, 2017 8:18 AM

  Welcome to SSC.

  When posting, you should provide data in a consumable format, rather that screenshots, which are more than often not useful for other users. I've done this for you on this occasion, but have a look at the link in my signature for future posts. 🙂

  The below should work for what you need. I've used a CTE and the LEAD function to get the depth range for each vegetable, which then gives you a dataset you can then JOIN on. if you don't understand what's going on, please do ask.
  USE Sandbox;
GO
--Sample Tables
CREATE TABLE Table1 (ID int, Depth int);
CREATE TABLE Table2 (ID int, Vegetable varchar(10), Depth int);
GO
--Sampel data
INSERT INTO Table1
VALUES (123, 700),(456,500);

  INSERT INTO Table2
VALUES
  (123,'Potato',500),
  (123,'Carrot',625),
  (123,'Onion',800),
  (456,'Carrot',325),
  (456,'Onion',450),
  (456,'Radish',680);
GO
--Check the data
SELECT *
FROM Table1;
SELECT *
FROM Table2;
GO
--Solution
WITH DepthRanges AS (
  SELECT ID, Vegetable,
    Depth AS StartDepth,
    --To give an upper limit on the last one, NULL returns are the Start Depth + 300
    LEAD(Depth,1,Depth+300) OVER (PARTITION BY ID ORDER BY Depth) AS EndDepth
  FROM Table2)
SELECT T1.ID, T1.Depth,
   DR.Vegetable
FROM Table1 T1
  JOIN DepthRanges DR ON T1.ID = DR.ID AND T1.Depth BETWEEN DR.StartDepth AND DR.EndDepth;
GO
--Clean up
DROP TABLE Table1;
DROP TABLE Table2;
GO


  This suffers from a fencepost error.  When the depth from table 1 exactly matches the depth of an item in table 2, it will return two records instead of one for that item.  This can easily be fixed by using half-closed intervals (>= and <) instead of fully closed intervals(>= and <= [between]).

  Here is another possible solution using Thom's data setup.  I think that the CROSS APPLY will perform better, but that would need to be tested on a much larger set of data.

  SELECT t1.ID, t1.Depth, t2.Vegetable
FROM #Table1 t1
CROSS APPLY (
    SELECT TOP (1) *
    FROM #Table2 t2
    WHERE t2.ID = t1.ID
        AND t2.Depth <= t1.Depth
    ORDER BY t2.Depth DESC
) t2;


  Drew

  I just tested this on the existing data, and it has less reads than my code.  Well done sir!

  This approach works best when the data in the secondary table is dense with respect to the data in the primary table, there is an appropriate index on the secondary table, and the number of records in the primary table is smallish.  With sparse data or when the primary table gets largish, Thom's ranged approach will work better.  It's not clear how dense/sparse the data in the secondary table is with respect to the primary table or whether the primary table is largish/smallish, so it's unclear which approach will be better for this data set.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Thom A

  SSC Guru

  Points: 99126

  More actions

  September 22, 2017 at 12:16 pm

  #1960571

  drew.allen - Friday, September 22, 2017 10:17 AM

  Thom A - Friday, September 22, 2017 9:16 AM

  I could have sworn i put a a -1 in the EndDepth..! It's in my SSMS window I still had open, must have copied before updating. I've amended my post.

  I would hesitate to hard code the value using a -1, because it's not clear that the depths are expressed as integers in the actual data, so using a -1 might introduce small gaps in the data.  Using a half-closed interval will ALWAYS work.

  Drew

  Fair point; if the OP is not using Integers. 🙂

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk