Need help with the getting a top 1 record with in a table with same ids

• oncloudninetynine

  SSC Enthusiast

  Points: 150

  More actions

  July 18, 2018 at 2:07 pm

  #414394

  I have  a table which have values like this 

  id date
  1  2018-07-16
  2  2018-07-18
  3  2018-07-19
  2  2018-07-19
  3  2018-07- 20
  1  2018-07-17
  1  2018-07-18

  I want to display the top 1 record for each Id for like this or the first record which have the nearest date with today. ideas ?

  1  2018-07-16
  2  2018-07-18
  3  2018-07-19

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  July 18, 2018 at 2:20 pm

  #1998100

  How about something like this:

  CREATE TABLE [dbo].[test](
  [id] [int] NULL,
  [date] [date] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test] ([id], [date]) VALUES (1, CAST(N'2018-07-16' AS Date))
GO
INSERT [dbo].[test] ([id], [date]) VALUES (2, CAST(N'2018-07-18' AS Date))
GO
INSERT [dbo].[test] ([id], [date]) VALUES (3, CAST(N'2018-07-19' AS Date))
GO
INSERT [dbo].[test] ([id], [date]) VALUES (2, CAST(N'2018-07-19' AS Date))
GO
INSERT [dbo].[test] ([id], [date]) VALUES (3, CAST(N'2018-07-20' AS Date))
GO
INSERT [dbo].[test] ([id], [date]) VALUES (1, CAST(N'2018-07-17' AS Date))
GO
INSERT [dbo].[test] ([id], [date]) VALUES (1, CAST(N'2018-07-18' AS Date))
GO

  WITH top3
  AS (SELECT Row_number()
      OVER(
       partition BY id
       ORDER BY date) AS rn,
      *
   FROM test)
SELECT id,
   date
FROM top3
WHERE rn = 1


  Mike Scalise, PMP
  https://www.michaelscalise.com

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 18, 2018 at 2:36 pm

  #1998103

  Or this:
  
CREATE TABLE [dbo].[test](
  [id] [int] NULL,
  [date] [date] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test] ([id], [date])
VALUES (1, CAST(N'2018-07-16' AS Date))
, (2, CAST(N'2018-07-18' AS Date))
, (3, CAST(N'2018-07-19' AS Date))
, (2, CAST(N'2018-07-19' AS Date))
, (3, CAST(N'2018-07-20' AS Date))
, (1, CAST(N'2018-07-17' AS Date))
, (1, CAST(N'2018-07-18' AS Date));
GO

  SELECT * FROM [dbo].[test]; -- Show all data

  SELECT
    [t].[id]
    , MIN([t].[date])
  FROM
    [dbo].[test] AS [t]
  GROUP BY
    [t].[id]; -- show requested data
  GO

  DROP TABLE [dbo].[test];
  GO
  

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 18, 2018 at 2:39 pm

  #1998104

  oncloudninetynine - Wednesday, July 18, 2018 2:07 PM

  I have  a table which have values like this 

  id date
  1  2018-07-16
  2  2018-07-18
  3  2018-07-19
  2  2018-07-19
  3  2018-07- 20
  1  2018-07-17
  1  2018-07-18

  I want to display the top 1 record for each Id for like this or the first record which have the nearest date with today. ideas ?

  1  2018-07-16
  2  2018-07-18
  3  2018-07-19

  Do you want the earliest date or the date closest to todays date (which is 2018-07-18 as of this posting) and what constitutes closest; same date, tomorrows date, yesterdays date, if both of those are the same?

• oncloudninetynine

  SSC Enthusiast

  Points: 150

  More actions

  July 18, 2018 at 2:48 pm

  #1998106

  Lynn Pettis - Wednesday, July 18, 2018 2:39 PM

  oncloudninetynine - Wednesday, July 18, 2018 2:07 PM

  I have  a table which have values like this 

  id date
  1  2018-07-16
  2  2018-07-18
  3  2018-07-19
  2  2018-07-19
  3  2018-07- 20
  1  2018-07-17
  1  2018-07-18

  I want to display the top 1 record for each Id for like this or the first record which have the nearest date with today. ideas ?

  1  2018-07-16
  2  2018-07-18
  3  2018-07-19

  Do you want the earliest date or the date closest to todays date (which is 2018-07-18 as of this posting) and what constitutes closest; same date, tomorrows date, yesterdays date, if both of those are the same?

  I want the record which has the most upcoming date  to todays date so for example if today is 18 july  if dates are  19 july ,20 july I would like to get only 19 july

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 18, 2018 at 2:54 pm

  #1998108

  oncloudninetynine - Wednesday, July 18, 2018 2:48 PM

  Lynn Pettis - Wednesday, July 18, 2018 2:39 PM

  oncloudninetynine - Wednesday, July 18, 2018 2:07 PM

  I have  a table which have values like this 

  id date
  1  2018-07-16
  2  2018-07-18
  3  2018-07-19
  2  2018-07-19
  3  2018-07- 20
  1  2018-07-17
  1  2018-07-18

  I want to display the top 1 record for each Id for like this or the first record which have the nearest date with today. ideas ?

  1  2018-07-16
  2  2018-07-18
  3  2018-07-19

  Do you want the earliest date or the date closest to todays date (which is 2018-07-18 as of this posting) and what constitutes closest; same date, tomorrows date, yesterdays date, if both of those are the same?

  I want the record which has the most upcoming date  to todays date so for example if today is 18 july  if dates are  19 july ,20 july I would like to get only 19 july

  First, that means you wont get anything for id = 1.

  
CREATE TABLE [dbo].[test](
  [id] [int] NULL,
  [date] [date] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test] ([id], [date])
VALUES (1, CAST(N'2018-07-16' AS Date))
, (2, CAST(N'2018-07-18' AS Date))
, (3, CAST(N'2018-07-19' AS Date))
, (2, CAST(N'2018-07-19' AS Date))
, (3, CAST(N'2018-07-20' AS Date))
, (1, CAST(N'2018-07-17' AS Date))
, (1, CAST(N'2018-07-18' AS Date));
GO

  SELECT * FROM [dbo].[test];

  SELECT
    [t].[id]
    , MIN([t].[date])
  FROM
    [dbo].[test] AS [t]
  WHERE
    [t].[date] > GETDATE()
  GROUP BY
    [t].[id];

  GO

  DROP TABLE [dbo].[test];
  GO
  

• oncloudninetynine

  SSC Enthusiast

  Points: 150

  More actions

  July 18, 2018 at 3:13 pm

  #1998110

  CREATE FUNCTION dbo.fn_GetEventDates(
@startDate date
, @endDate date
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
WITH
T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
    , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
    , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
    , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
    , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
    , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
    , (0),(0),(0),(0)) AS X(N))
, Nums AS (SELECT TOP(DATEDIFF(dd, @startDate, ISNULL(@endDate, @startDate)) +1)
  rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FROM T T1, T T2
  )
SELECT rn, EventDate = dateadd(dd, rn-1, @startDate)
FROM Nums;
GO
 -- Then I am  CROSS APPLYing the function  like this
-- Create sample data
CREATE TABLE #Event (
eventId int
, title varchar(20)
, StartDate date
, endDate date
);

  INSERT INTO #Event ( eventId, title, StartDate, endDate )
VALUES ( 1, 'sometitle', '2018-06-21', NULL )
, ( 2, 'someeventtitletest', '2018-06-20', '2018-06-26' )
, ( 3, 'anothertitle', '2018-06-22', NULL );

  -- CROSS APPLY the function to the sample data
SELECT
ed.EventDate
, evt.eventId
FROM #Event AS evt
CROSS APPLY dbo.fn_GetEventDates(evt.StartDate, evt.endDate) AS ed;

  Now i am required to pick only one record where event date is nearest to the todays date , how can i do that in this situaiton  ?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 18, 2018 at 3:48 pm

  #1998116

  oncloudninetynine - Wednesday, July 18, 2018 3:13 PM

  CREATE FUNCTION dbo.fn_GetEventDates(
  @startDate date
  , @endDate date
  )
  RETURNS TABLE WITH SCHEMABINDING
  AS RETURN
  WITH
  T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0)) AS X(N))
  , Nums AS (SELECT TOP(DATEDIFF(dd, @startDate, ISNULL(@endDate, @startDate)) +1)
    rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM T T1, T T2
    )
  SELECT rn, EventDate = dateadd(dd, rn-1, @startDate)
  FROM Nums;
  GO
   -- Then I am  CROSS APPLYing the function  like this
  -- Create sample data
  CREATE TABLE #Event (
  eventId int
  , title varchar(20)
  , StartDate date
  , endDate date
  );

  INSERT INTO #Event ( eventId, title, StartDate, endDate )
  VALUES ( 1, 'sometitle', '2018-06-21', NULL )
  , ( 2, 'someeventtitletest', '2018-06-20', '2018-06-26' )
  , ( 3, 'anothertitle', '2018-06-22', NULL );

  -- CROSS APPLY the function to the sample data
SELECT
ed.EventDate
, evt.eventId
FROM #Event AS evt
CROSS APPLY dbo.fn_GetEventDates(evt.StartDate, evt.endDate) AS ed;

  Now i am required to pick only one record where event date is nearest to the todays date , how can i do that in this situaiton  ?

  Okay, apply what I showed you to this problem.

• oncloudninetynine

  SSC Enthusiast

  Points: 150

  More actions

  July 18, 2018 at 3:55 pm

  #1998117

  Lynn Pettis - Wednesday, July 18, 2018 3:48 PM

  oncloudninetynine - Wednesday, July 18, 2018 3:13 PM

  CREATE FUNCTION dbo.fn_GetEventDates(
  @startDate date
  , @endDate date
  )
  RETURNS TABLE WITH SCHEMABINDING
  AS RETURN
  WITH
  T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0)) AS X(N))
  , Nums AS (SELECT TOP(DATEDIFF(dd, @startDate, ISNULL(@endDate, @startDate)) +1)
    rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM T T1, T T2
    )
  SELECT rn, EventDate = dateadd(dd, rn-1, @startDate)
  FROM Nums;
  GO
   -- Then I am  CROSS APPLYing the function  like this
  -- Create sample data
  CREATE TABLE #Event (
  eventId int
  , title varchar(20)
  , StartDate date
  , endDate date
  );

  INSERT INTO #Event ( eventId, title, StartDate, endDate )
  VALUES ( 1, 'sometitle', '2018-06-21', NULL )
  , ( 2, 'someeventtitletest', '2018-06-20', '2018-06-26' )
  , ( 3, 'anothertitle', '2018-06-22', NULL );

  -- CROSS APPLY the function to the sample data
SELECT
ed.EventDate
, evt.eventId
FROM #Event AS evt
CROSS APPLY dbo.fn_GetEventDates(evt.StartDate, evt.endDate) AS ed;

  Now i am required to pick only one record where event date is nearest to the todays date , how can i do that in this situaiton  ?

  Okay, apply what I showed you to this problem.

  Where should i apply it ? inside the funtion ? any hint would be very helpful. 
  thanks

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 18, 2018 at 3:58 pm

  #1998119

  oncloudninetynine - Wednesday, July 18, 2018 3:55 PM

  Lynn Pettis - Wednesday, July 18, 2018 3:48 PM

  oncloudninetynine - Wednesday, July 18, 2018 3:13 PM

  CREATE FUNCTION dbo.fn_GetEventDates(
  @startDate date
  , @endDate date
  )
  RETURNS TABLE WITH SCHEMABINDING
  AS RETURN
  WITH
  T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0)) AS X(N))
  , Nums AS (SELECT TOP(DATEDIFF(dd, @startDate, ISNULL(@endDate, @startDate)) +1)
    rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM T T1, T T2
    )
  SELECT rn, EventDate = dateadd(dd, rn-1, @startDate)
  FROM Nums;
  GO
   -- Then I am  CROSS APPLYing the function  like this
  -- Create sample data
  CREATE TABLE #Event (
  eventId int
  , title varchar(20)
  , StartDate date
  , endDate date
  );

  INSERT INTO #Event ( eventId, title, StartDate, endDate )
  VALUES ( 1, 'sometitle', '2018-06-21', NULL )
  , ( 2, 'someeventtitletest', '2018-06-20', '2018-06-26' )
  , ( 3, 'anothertitle', '2018-06-22', NULL );

  -- CROSS APPLY the function to the sample data
SELECT
ed.EventDate
, evt.eventId
FROM #Event AS evt
CROSS APPLY dbo.fn_GetEventDates(evt.StartDate, evt.endDate) AS ed;

  Now i am required to pick only one record where event date is nearest to the todays date , how can i do that in this situaiton  ?

  Okay, apply what I showed you to this problem.

  Where should i apply it ? inside the funtion ? any hint would be very helpful. 
  thanks

  Look with eye.

  
CREATE FUNCTION dbo.fn_GetEventDates(
  @startDate date
  , @endDate date
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
  WITH
  T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0)) AS X(N))
  , Nums AS (SELECT TOP(DATEDIFF(dd, @startDate, ISNULL(@endDate, @startDate)) +1)
   rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM T T1, T T2
  )
  SELECT rn, EventDate = dateadd(dd, rn-1, @startDate)
  FROM Nums;
GO
 -- Then I am  CROSS APPLYing the function  like this
-- Create sample data
CREATE TABLE #Event (
  eventId int
  , title varchar(20)
  , StartDate date
  , endDate date
);

  INSERT INTO #Event ( eventId, title, StartDate, endDate )
  VALUES  ( 1, 'sometitle', '2018-07-21', NULL )
    , ( 2, 'someeventtitletest', '2018-07-20', '2018-07-26' )
    , ( 3, 'anothertitle', '2018-07-22', NULL );

  
  -- CROSS APPLY the function to the sample data
  SELECT
    MIN(ed.EventDate) EventDate
    , evt.eventId
  FROM
    #Event AS evt
    CROSS APPLY dbo.fn_GetEventDates(evt.StartDate, evt.endDate) AS ed
  WHERE
    ed.EventDate > GETDATE()
  GROUP BY
    evt.EventId
  ;
  GO

  DROP TABLE #Event;
  DROP FUNCTION dbo.fn_GetEventDates;
  GO
  

• oncloudninetynine

  SSC Enthusiast

  Points: 150

  More actions

  July 18, 2018 at 4:38 pm

  #1998123

  Lynn Pettis - Wednesday, July 18, 2018 3:58 PM

  oncloudninetynine - Wednesday, July 18, 2018 3:55 PM

  Lynn Pettis - Wednesday, July 18, 2018 3:48 PM

  oncloudninetynine - Wednesday, July 18, 2018 3:13 PM

  CREATE FUNCTION dbo.fn_GetEventDates(
  @startDate date
  , @endDate date
  )
  RETURNS TABLE WITH SCHEMABINDING
  AS RETURN
  WITH
  T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0)) AS X(N))
  , Nums AS (SELECT TOP(DATEDIFF(dd, @startDate, ISNULL(@endDate, @startDate)) +1)
    rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM T T1, T T2
    )
  SELECT rn, EventDate = dateadd(dd, rn-1, @startDate)
  FROM Nums;
  GO
   -- Then I am  CROSS APPLYing the function  like this
  -- Create sample data
  CREATE TABLE #Event (
  eventId int
  , title varchar(20)
  , StartDate date
  , endDate date
  );

  INSERT INTO #Event ( eventId, title, StartDate, endDate )
  VALUES ( 1, 'sometitle', '2018-06-21', NULL )
  , ( 2, 'someeventtitletest', '2018-06-20', '2018-06-26' )
  , ( 3, 'anothertitle', '2018-06-22', NULL );

  -- CROSS APPLY the function to the sample data
SELECT
ed.EventDate
, evt.eventId
FROM #Event AS evt
CROSS APPLY dbo.fn_GetEventDates(evt.StartDate, evt.endDate) AS ed;

  Now i am required to pick only one record where event date is nearest to the todays date , how can i do that in this situaiton  ?

  Okay, apply what I showed you to this problem.

  Where should i apply it ? inside the funtion ? any hint would be very helpful. 
  thanks

  Look with eye.

  
CREATE FUNCTION dbo.fn_GetEventDates(
  @startDate date
  , @endDate date
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
  WITH
  T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0)) AS X(N))
  , Nums AS (SELECT TOP(DATEDIFF(dd, @startDate, ISNULL(@endDate, @startDate)) +1)
   rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM T T1, T T2
  )
  SELECT rn, EventDate = dateadd(dd, rn-1, @startDate)
  FROM Nums;
GO
 -- Then I am  CROSS APPLYing the function  like this
-- Create sample data
CREATE TABLE #Event (
  eventId int
  , title varchar(20)
  , StartDate date
  , endDate date
);

  INSERT INTO #Event ( eventId, title, StartDate, endDate )
  VALUES  ( 1, 'sometitle', '2018-07-21', NULL )
    , ( 2, 'someeventtitletest', '2018-07-20', '2018-07-26' )
    , ( 3, 'anothertitle', '2018-07-22', NULL );

  
  -- CROSS APPLY the function to the sample data
  SELECT
    MIN(ed.EventDate) EventDate
    , evt.eventId
  FROM
    #Event AS evt
    CROSS APPLY dbo.fn_GetEventDates(evt.StartDate, evt.endDate) AS ed
  WHERE
    ed.EventDate > GETDATE()
  GROUP BY
    evt.EventId
  ;
  GO

  DROP TABLE #Event;
  DROP FUNCTION dbo.fn_GetEventDates;
  GO
  

  SELECT   MIN(ed.EventDate) as EventDate,evt.eventId,Title
from Event as evt
CROSS APPLY dbo.fn_GetEventDates(evt.[EventStartDate], evt.[EventEndDate]) AS ed
where ed.EventDate > GETDATE()
group by EventDate,.eventid,Title

  Change my code to above SQL , but its still returning all records. Please help.

• pietlinden

  SSC Guru

  Points: 63425

  More actions

  July 18, 2018 at 4:42 pm

  #1998124

  You shouldn't be grouping by EventDate - you're trying to return a MIN([EventDate]), so grouping by it (return each unique value) doesn't make sense.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 18, 2018 at 4:57 pm

  #1998127

  oncloudninetynine - Wednesday, July 18, 2018 4:38 PM

  Lynn Pettis - Wednesday, July 18, 2018 3:58 PM

  oncloudninetynine - Wednesday, July 18, 2018 3:55 PM

  Lynn Pettis - Wednesday, July 18, 2018 3:48 PM

  oncloudninetynine - Wednesday, July 18, 2018 3:13 PM

  CREATE FUNCTION dbo.fn_GetEventDates(
  @startDate date
  , @endDate date
  )
  RETURNS TABLE WITH SCHEMABINDING
  AS RETURN
  WITH
  T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
      , (0),(0),(0),(0)) AS X(N))
  , Nums AS (SELECT TOP(DATEDIFF(dd, @startDate, ISNULL(@endDate, @startDate)) +1)
    rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM T T1, T T2
    )
  SELECT rn, EventDate = dateadd(dd, rn-1, @startDate)
  FROM Nums;
  GO
   -- Then I am  CROSS APPLYing the function  like this
  -- Create sample data
  CREATE TABLE #Event (
  eventId int
  , title varchar(20)
  , StartDate date
  , endDate date
  );

  INSERT INTO #Event ( eventId, title, StartDate, endDate )
  VALUES ( 1, 'sometitle', '2018-06-21', NULL )
  , ( 2, 'someeventtitletest', '2018-06-20', '2018-06-26' )
  , ( 3, 'anothertitle', '2018-06-22', NULL );

  -- CROSS APPLY the function to the sample data
SELECT
ed.EventDate
, evt.eventId
FROM #Event AS evt
CROSS APPLY dbo.fn_GetEventDates(evt.StartDate, evt.endDate) AS ed;

  Now i am required to pick only one record where event date is nearest to the todays date , how can i do that in this situaiton  ?

  Okay, apply what I showed you to this problem.

  Where should i apply it ? inside the funtion ? any hint would be very helpful. 
  thanks

  Look with eye.

  
CREATE FUNCTION dbo.fn_GetEventDates(
  @startDate date
  , @endDate date
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
  WITH
  T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
     , (0),(0),(0),(0)) AS X(N))
  , Nums AS (SELECT TOP(DATEDIFF(dd, @startDate, ISNULL(@endDate, @startDate)) +1)
   rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM T T1, T T2
  )
  SELECT rn, EventDate = dateadd(dd, rn-1, @startDate)
  FROM Nums;
GO
 -- Then I am  CROSS APPLYing the function  like this
-- Create sample data
CREATE TABLE #Event (
  eventId int
  , title varchar(20)
  , StartDate date
  , endDate date
);

  INSERT INTO #Event ( eventId, title, StartDate, endDate )
  VALUES  ( 1, 'sometitle', '2018-07-21', NULL )
    , ( 2, 'someeventtitletest', '2018-07-20', '2018-07-26' )
    , ( 3, 'anothertitle', '2018-07-22', NULL );

  
  -- CROSS APPLY the function to the sample data
  SELECT
    MIN(ed.EventDate) EventDate
    , evt.eventId
  FROM
    #Event AS evt
    CROSS APPLY dbo.fn_GetEventDates(evt.StartDate, evt.endDate) AS ed
  WHERE
    ed.EventDate > GETDATE()
  GROUP BY
    evt.EventId
  ;
  GO

  DROP TABLE #Event;
  DROP FUNCTION dbo.fn_GetEventDates;
  GO
  

  SELECT   MIN(ed.EventDate) as EventDate,evt.eventId,Title
from Event as evt
CROSS APPLY dbo.fn_GetEventDates(evt.[EventStartDate], evt.[EventEndDate]) AS ed
where ed.EventDate > GETDATE()
group by EventDate,.eventid,Title

  Change my code to above SQL , but its still returning all records. Please help.

  Looking at the GROUP BY I have to wonder how it even runs.  Since you added Title you need to group by the EventId and Title.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  July 19, 2018 at 11:49 am

  #1998256

  Just one comment on this...   What, exactly, does "closest" mean?   The reason I ask this is because let's say an event spans today by starting before today and ending after today, and you want the "closest" date.   Is that today, or is it both yesterday AND tomorrow?   Or if the event starts today and ends some day later than today, do you define "closest" as today or tomorrow?   Similarly, if an event starts before today, and ends today, do you want yesterday or today?   I know this analysis may seem like overkill, but without being able to answer such questions, at this somewhat anal level of detail, it's usually a guessing game as to what exactly someone means, and can complicate someone being able to help you without going through a lot of iterations to finally figure it out.

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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 19, 2018 at 12:03 pm

  #1998261

  sgmunson - Thursday, July 19, 2018 11:49 AM

  Just one comment on this...   What, exactly, does "closest" mean?   The reason I ask this is because let's say an event spans today by starting before today and ending after today, and you want the "closest" date.   Is that today, or is it both yesterday AND tomorrow?   Or if the event starts today and ends some day later than today, do you define "closest" as today or tomorrow?   Similarly, if an event starts before today, and ends today, do you want yesterday or today?   I know this analysis may seem like overkill, but without being able to answer such questions, at this somewhat anal level of detail, it's usually a guessing game as to what exactly someone means, and can complicate someone being able to help you without going through a lot of iterations to finally figure it out.

  Based on what the OP stated when asked this question earlier, the greater than but closest to today.  For today, 2018-07-19, that could be 2018-07-20 if that is the date closest for a given event.