Nearest Date value

• ansaryarshad

  SSC-Addicted

  Points: 424

  More actions

  October 30, 2017 at 10:50 am

  #409735

  Product	Location	Date	Inventory value	No_entry
  4711	M001	1/10/2017	10
  4711	M001	2/10/2017	20
  4711	M001	3/10/2017		X
  4711	M001	4/10/2017	40
  4711	M001	5/10/2017	50
  4711	M001	6/10/2017		X

  Hi Experts,

  I have a temporary table like above ,if you see that its missing inventory value for dates 3/10/2017 and 6/10/2017 where NO_ENTRY flag is set .
  I want this values to be copied from the nearest_date below the missing date i.e for eg 3/10/2017 from 2/10/2017 i.e 20 and for 6/10/2017 from 5/10/2017 as 50 . I do not have the option CTEs or Recursive CTEs . Is it possible with window function or Subquery?

  BR
  Arshad

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  October 30, 2017 at 10:55 am

  #1965818

  Arshad

  Have you looked at the LAG function?  Why can you not use CTEs, incidentally?

  John

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  October 30, 2017 at 11:07 am

  #1965821

  here's one solution using LAG. Requires SQL Server 2012 and later.

  use tempdb;
go

  create table #data(ProductID INT
                , LocationID CHAR(4)
                , EventDate DATE
                , Qty TINYINT
);
GO

  INSERT INTO #Data(ProductID, LocationID, EventDate, Qty)
VALUES (4711, 'M001',    '1/10/2017',    10),    
(4711, 'M001',    '2/10/2017',    20),    
(4711, 'M001',    '3/10/2017', null),
(4711, 'M001',    '4/10/2017',    40),    
(4711, 'M001',    '5/10/2017',    50),    
(4711, 'M001',    '6/10/2017', null);

  SELECT ProductID, LocationID, EventDate,
    CASE WHEN Qty IS NULL THEN LAG(Qty,1) OVER (PARTITION BY ProductID, LocationID ORDER BY EventDate) ELSE Qty END Qty2
FROM #Data;

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 30, 2017 at 11:32 am

  #1965826

  Here's a version using a subquery:

  use tempdb;

  go

  DROP table #data;

  create table #data (ProductID INT, LocationID CHAR(4), EventDate DATE, Qty TINYINT);

  GO

  INSERT INTO #Data(ProductID, LocationID, EventDate, Qty)

  VALUES

  (4711, 'M001', '20171001', 10),

  (4711, 'M001', '20171002', 20),

  (4711, 'M001', '20171003', null),

  (4711, 'M001', '20171004', null),

  (4711, 'M001', '20171005', 40),

  (4711, 'M001', '20171006', 50),

  (4711, 'M001', '20171007', null);

  SELECT o.ProductID, o.LocationID, o.EventDate, Qty = ISNULL(o.Qty,x.Qty2)

  FROM #Data o

  OUTER APPLY (

  SELECT TOP(1)

  Qty2 = i.Qty

  FROM #Data i

  WHERE i.ProductID = o.ProductID

  AND i.LocationID = o.LocationID

  AND o.Qty IS NULL

  AND i.Qty IS NOT NULL

  AND i.EventDate < o.EventDate

  ORDER BY i.EventDate DESC

  ) x;

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  October 30, 2017 at 2:26 pm

  #1965863

  pietlinden - Monday, October 30, 2017 11:07 AM

  here's one solution using LAG. Requires SQL Server 2012 and later.

  use tempdb;
go

  create table #data(ProductID INT
                , LocationID CHAR(4)
                , EventDate DATE
                , Qty TINYINT
);
GO

  INSERT INTO #Data(ProductID, LocationID, EventDate, Qty)
VALUES (4711, 'M001',    '1/10/2017',    10),    
(4711, 'M001',    '2/10/2017',    20),    
(4711, 'M001',    '3/10/2017', null),
(4711, 'M001',    '4/10/2017',    40),    
(4711, 'M001',    '5/10/2017',    50),    
(4711, 'M001',    '6/10/2017', null);

  SELECT ProductID, LocationID, EventDate,
    CASE WHEN Qty IS NULL THEN LAG(Qty,1) OVER (PARTITION BY ProductID, LocationID ORDER BY EventDate) ELSE Qty END Qty2
FROM #Data;

  The problem with LAG is that you need to specify how far to look back and that may vary.  I've added additional data that illustrates the problem.

  use tempdb;
go

  create table #data(ProductID INT
      , LocationID CHAR(4)
      , EventDate DATE
      , Qty TINYINT
);
GO

  INSERT INTO #Data(ProductID, LocationID, EventDate, Qty)
VALUES (4711, 'M001',  '1/10/2017',  10), 
(4711, 'M001',  '2/10/2017',  20), 
(4711, 'M001',  '3/10/2017', null),
(4711, 'M001',  '4/10/2017',  40), 
(4711, 'M001',  '5/10/2017',  50), 
(4711, 'M001',  '6/10/2017', null),
(4711, 'M002',  '1/10/2017',  10), 
(4711, 'M002',  '2/10/2017',  20), 
(4711, 'M002',  '3/10/2017', null),
(4711, 'M002',  '4/10/2017', null), 
(4711, 'M002',  '5/10/2017',  50), 
(4711, 'M002',  '6/10/2017', null);

  SELECT ProductID, LocationID, EventDate,
  CASE WHEN Qty IS NULL THEN LAG(Qty,1) OVER (PARTITION BY ProductID, LocationID ORDER BY EventDate) ELSE Qty END Qty2
FROM #Data;

  There are two differrent approaches that will work here.  The simple approach will only work if the value you wan't is monotonic with respect to the date field.

  SELECT ProductID, LocationID, EventDate,
    MAX(Qty) OVER(PARTITION BY ProductID, LocationID ORDER BY EventDate ROWS UNBOUNDED PRECEDING), -- Simple Approach. Monotonic values only.
    CAST(STUFF(MAX(CONVERT(CHAR(8), EventDate, 112) + CAST(Qty AS VARCHAR(10))) OVER(PARTITION BY ProductID, LocationID ORDER BY EventDate ROWS UNBOUNDED PRECEDING), 1, 8, '') AS tinyint) -- Complicated Approach
FROM #data


  Drew

  Edit: I added ROWS UNBOUNDED PRECEDING which is shorthand for ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, because that is much more efficient than the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• ansaryarshad

  SSC-Addicted

  Points: 424

  More actions

  October 31, 2017 at 2:25 am

  #1965910

  pietlinden - Monday, October 30, 2017 11:07 AM

  here's one solution using LAG. Requires SQL Server 2012 and later.

  use tempdb;
go

  create table #data(ProductID INT
                , LocationID CHAR(4)
                , EventDate DATE
                , Qty TINYINT
);
GO

  INSERT INTO #Data(ProductID, LocationID, EventDate, Qty)
VALUES (4711, 'M001',    '1/10/2017',    10),    
(4711, 'M001',    '2/10/2017',    20),    
(4711, 'M001',    '3/10/2017', null),
(4711, 'M001',    '4/10/2017',    40),    
(4711, 'M001',    '5/10/2017',    50),    
(4711, 'M001',    '6/10/2017', null);

  SELECT ProductID, LocationID, EventDate,
    CASE WHEN Qty IS NULL THEN LAG(Qty,1) OVER (PARTITION BY ProductID, LocationID ORDER BY EventDate) ELSE Qty END Qty2
FROM #Data;

  Hi PietLinden,

  Thanks for the response . I tried this LAG function .This works for above case .But if there are multiple null values,I want to copy the value from the nearest_date for all the null values . If 5/10 is also null, then I want 5/10 and 6/10 values to be filled from 4/10 . Is that possible in LAG  function ?

  BR
  Arshad

• ansaryarshad

  SSC-Addicted

  Points: 424

  More actions

  October 31, 2017 at 2:29 am

  #1965911

  drew.allen - Monday, October 30, 2017 2:26 PM

  pietlinden - Monday, October 30, 2017 11:07 AM

  here's one solution using LAG. Requires SQL Server 2012 and later.

  use tempdb;
go

  create table #data(ProductID INT
                , LocationID CHAR(4)
                , EventDate DATE
                , Qty TINYINT
);
GO

  INSERT INTO #Data(ProductID, LocationID, EventDate, Qty)
VALUES (4711, 'M001',    '1/10/2017',    10),    
(4711, 'M001',    '2/10/2017',    20),    
(4711, 'M001',    '3/10/2017', null),
(4711, 'M001',    '4/10/2017',    40),    
(4711, 'M001',    '5/10/2017',    50),    
(4711, 'M001',    '6/10/2017', null);

  SELECT ProductID, LocationID, EventDate,
    CASE WHEN Qty IS NULL THEN LAG(Qty,1) OVER (PARTITION BY ProductID, LocationID ORDER BY EventDate) ELSE Qty END Qty2
FROM #Data;

  The problem with LAG is that you need to specify how far to look back and that may vary.  I've added additional data that illustrates the problem.

  use tempdb;
go

  create table #data(ProductID INT
      , LocationID CHAR(4)
      , EventDate DATE
      , Qty TINYINT
);
GO

  INSERT INTO #Data(ProductID, LocationID, EventDate, Qty)
VALUES (4711, 'M001',  '1/10/2017',  10), 
(4711, 'M001',  '2/10/2017',  20), 
(4711, 'M001',  '3/10/2017', null),
(4711, 'M001',  '4/10/2017',  40), 
(4711, 'M001',  '5/10/2017',  50), 
(4711, 'M001',  '6/10/2017', null),
(4711, 'M002',  '1/10/2017',  10), 
(4711, 'M002',  '2/10/2017',  20), 
(4711, 'M002',  '3/10/2017', null),
(4711, 'M002',  '4/10/2017', null), 
(4711, 'M002',  '5/10/2017',  50), 
(4711, 'M002',  '6/10/2017', null);

  SELECT ProductID, LocationID, EventDate,
  CASE WHEN Qty IS NULL THEN LAG(Qty,1) OVER (PARTITION BY ProductID, LocationID ORDER BY EventDate) ELSE Qty END Qty2
FROM #Data;

  There are two differrent approaches that will work here.  The simple approach will only work if the value you wan't is monotonic with respect to the date field.

  SELECT ProductID, LocationID, EventDate,
    MAX(Qty) OVER(PARTITION BY ProductID, LocationID ORDER BY EventDate ROWS UNBOUNDED PRECEDING), -- Simple Approach. Monotonic values only.
    CAST(STUFF(MAX(CONVERT(CHAR(8), EventDate, 112) + CAST(Qty AS VARCHAR(10))) OVER(PARTITION BY ProductID, LocationID ORDER BY EventDate ROWS UNBOUNDED PRECEDING), 1, 8, '') AS tinyint) -- Complicated Approach
FROM #data


  Drew

  Edit: I added ROWS UNBOUNDED PRECEDING which is shorthand for ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, because that is much more efficient than the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  Hi Allen,
  Thanks for the immediate response
  I tried the Max(qty) but it is copying the same value across all the null cells which is not the requirement.I did not try the other approach bcs I am working HANA sql script and we dont have a function like STUFF:(
  BR
  Arshad

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 31, 2017 at 2:50 am

  #1965912

  This is a bogstandard SQL version of the APPLY query I posted above. One of the many handy features of APPLY is that the subqueries can often by transposed directly into the SELECT list as correlated subqueries, like this, which has an execution plan virtually identical to the APPLY version:

  IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data;
CREATE TABLE #data (ProductID INT, LocationID CHAR(4), EventDate DATE, Qty TINYINT);
INSERT INTO #Data(ProductID, LocationID, EventDate, Qty)
VALUES
 (4711, 'M001', '20171001', 10),
 (4711, 'M001', '20171002', 20),
 (4711, 'M001', '20171003', null),
 (4711, 'M001', '20171004', null),
 (4711, 'M001', '20171005', 40),
 (4711, 'M001', '20171006', 50),
 (4711, 'M001', '20171007', null);

  SELECT
   o.ProductID,
   o.LocationID,
   o.EventDate,
   Qty = CASE
    WHEN o.Qty IS NOT NULL THEN o.Qty
    ELSE (
     SELECT TOP(1) i.Qty
     FROM #Data i
     WHERE i.ProductID = o.ProductID
      AND i.LocationID = o.LocationID
      AND o.Qty IS NULL
      AND i.Qty IS NOT NULL
      AND i.EventDate < o.EventDate
     ORDER BY i.EventDate DESC  
    ) END 
  FROM #Data o

  Note that, like the APPLY version I posted yesterday, this caters for more than one sequential NULL value for Qty.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ansaryarshad

  SSC-Addicted

  Points: 424

  More actions

  October 31, 2017 at 5:25 am

  #1965933

  ChrisM@Work - Tuesday, October 31, 2017 2:50 AM

  This is a bogstandard SQL version of the APPLY query I posted above. One of the many handy features of APPLY is that the subqueries can often by transposed directly into the SELECT list as correlated subqueries, like this, which has an execution plan virtually identical to the APPLY version:

  IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data;
CREATE TABLE #data (ProductID INT, LocationID CHAR(4), EventDate DATE, Qty TINYINT);
INSERT INTO #Data(ProductID, LocationID, EventDate, Qty)
VALUES
 (4711, 'M001', '20171001', 10),
 (4711, 'M001', '20171002', 20),
 (4711, 'M001', '20171003', null),
 (4711, 'M001', '20171004', null),
 (4711, 'M001', '20171005', 40),
 (4711, 'M001', '20171006', 50),
 (4711, 'M001', '20171007', null);

  SELECT
   o.ProductID,
   o.LocationID,
   o.EventDate,
   Qty = CASE
    WHEN o.Qty IS NOT NULL THEN o.Qty
    ELSE (
     SELECT TOP(1) i.Qty
     FROM #Data i
     WHERE i.ProductID = o.ProductID
      AND i.LocationID = o.LocationID
      AND o.Qty IS NULL
      AND i.Qty IS NOT NULL
      AND i.EventDate < o.EventDate
     ORDER BY i.EventDate DESC  
    ) END 
  FROM #Data o

  Note that, like the APPLY version I posted yesterday, this caters for more than one sequential NULL value for Qty.

  Thanks Chris for your continued support . . I am using HANA sql script in which TOP and ORDER are not supported in subqueries .

  BR
  Arshad

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 31, 2017 at 6:16 am

  #1965947

  ansaryarshad - Tuesday, October 31, 2017 5:25 AM

  ChrisM@Work - Tuesday, October 31, 2017 2:50 AM

  This is a bogstandard SQL version of the APPLY query I posted above. One of the many handy features of APPLY is that the subqueries can often by transposed directly into the SELECT list as correlated subqueries, like this, which has an execution plan virtually identical to the APPLY version:

  IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data;
CREATE TABLE #data (ProductID INT, LocationID CHAR(4), EventDate DATE, Qty TINYINT);
INSERT INTO #Data(ProductID, LocationID, EventDate, Qty)
VALUES
 (4711, 'M001', '20171001', 10),
 (4711, 'M001', '20171002', 20),
 (4711, 'M001', '20171003', null),
 (4711, 'M001', '20171004', null),
 (4711, 'M001', '20171005', 40),
 (4711, 'M001', '20171006', 50),
 (4711, 'M001', '20171007', null);

  SELECT
   o.ProductID,
   o.LocationID,
   o.EventDate,
   Qty = CASE
    WHEN o.Qty IS NOT NULL THEN o.Qty
    ELSE (
     SELECT TOP(1) i.Qty
     FROM #Data i
     WHERE i.ProductID = o.ProductID
      AND i.LocationID = o.LocationID
      AND o.Qty IS NULL
      AND i.Qty IS NOT NULL
      AND i.EventDate < o.EventDate
     ORDER BY i.EventDate DESC  
    ) END 
  FROM #Data o

  Note that, like the APPLY version I posted yesterday, this caters for more than one sequential NULL value for Qty.

  Thanks Chris for your continued support . . I am using HANA sql script in which TOP and ORDER are not supported in subqueries .

  BR
  Arshad

  Check if you can use ROW_NUMBER() in subqueries.
  I'm really surprised that HANA supports TOP but not in subqueries. Crazy.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  October 31, 2017 at 8:06 am

  #1965968

  ansaryarshad - Tuesday, October 31, 2017 2:29 AM

  drew.allen - Monday, October 30, 2017 2:26 PM

  pietlinden - Monday, October 30, 2017 11:07 AM

  here's one solution using LAG. Requires SQL Server 2012 and later.

  use tempdb;
go

  create table #data(ProductID INT
                , LocationID CHAR(4)
                , EventDate DATE
                , Qty TINYINT
);
GO

  INSERT INTO #Data(ProductID, LocationID, EventDate, Qty)
VALUES (4711, 'M001',    '1/10/2017',    10),    
(4711, 'M001',    '2/10/2017',    20),    
(4711, 'M001',    '3/10/2017', null),
(4711, 'M001',    '4/10/2017',    40),    
(4711, 'M001',    '5/10/2017',    50),    
(4711, 'M001',    '6/10/2017', null);

  SELECT ProductID, LocationID, EventDate,
    CASE WHEN Qty IS NULL THEN LAG(Qty,1) OVER (PARTITION BY ProductID, LocationID ORDER BY EventDate) ELSE Qty END Qty2
FROM #Data;

  The problem with LAG is that you need to specify how far to look back and that may vary.  I've added additional data that illustrates the problem.

  use tempdb;
go

  create table #data(ProductID INT
      , LocationID CHAR(4)
      , EventDate DATE
      , Qty TINYINT
);
GO

  INSERT INTO #Data(ProductID, LocationID, EventDate, Qty)
VALUES (4711, 'M001',  '1/10/2017',  10), 
(4711, 'M001',  '2/10/2017',  20), 
(4711, 'M001',  '3/10/2017', null),
(4711, 'M001',  '4/10/2017',  40), 
(4711, 'M001',  '5/10/2017',  50), 
(4711, 'M001',  '6/10/2017', null),
(4711, 'M002',  '1/10/2017',  10), 
(4711, 'M002',  '2/10/2017',  20), 
(4711, 'M002',  '3/10/2017', null),
(4711, 'M002',  '4/10/2017', null), 
(4711, 'M002',  '5/10/2017',  50), 
(4711, 'M002',  '6/10/2017', null);

  SELECT ProductID, LocationID, EventDate,
  CASE WHEN Qty IS NULL THEN LAG(Qty,1) OVER (PARTITION BY ProductID, LocationID ORDER BY EventDate) ELSE Qty END Qty2
FROM #Data;

  There are two differrent approaches that will work here.  The simple approach will only work if the value you wan't is monotonic with respect to the date field.

  SELECT ProductID, LocationID, EventDate,
    MAX(Qty) OVER(PARTITION BY ProductID, LocationID ORDER BY EventDate ROWS UNBOUNDED PRECEDING), -- Simple Approach. Monotonic values only.
    CAST(STUFF(MAX(CONVERT(CHAR(8), EventDate, 112) + CAST(Qty AS VARCHAR(10))) OVER(PARTITION BY ProductID, LocationID ORDER BY EventDate ROWS UNBOUNDED PRECEDING), 1, 8, '') AS tinyint) -- Complicated Approach
FROM #data


  Drew

  Edit: I added ROWS UNBOUNDED PRECEDING which is shorthand for ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, because that is much more efficient than the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  Hi Allen,
  Thanks for the immediate response
  I tried the Max(qty) but it is copying the same value across all the null cells which is not the requirement.I did not try the other approach bcs I am working HANA sql script and we dont have a function like STUFF:(
  BR
  Arshad

  If MAX(Qty) isn't working, it's probably because you're values aren't monotonic.  I did say multiple times that it would only work if the values were monotonic.

  In the other version, the STUFF is used to remove the first 8 characters (the date portion).  You can use SUBSTRING instead.

  If you are using HANA SQL, why are you posting in a forum dedicated to T-SQL?  If you post in a T-SQL forum, you're going to get T-SQL answers.  If you want HANA SQL answers, post in a HANA SQL forum.

  Drew

  PS: My name is DREW.  That's how I signed my post.  Please use that name.  If you insist on referring to me using my last name, at least have the courtesy to say "Mr. Allen".

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA