June 21, 2018 at 1:14 pm
I have table where an event has a start date and end date. An event might not have a end date suggesting that its just one day event.
so here is my table
eventId title StartDate endDate
1 sometitle 2018-06-21 Null
2 someeventtitletest 2018-06-20 2018-06-26
3 anothertitle 2018-06-22 Null
So I want to have an output like list the event in all date that it has in its start and end date. How can I do this in SQL Query
evenDate eventID
2018-06-20 2
2018-06-21 1
2018-06-21 2
2018-06-22 3
2018-06-22 2
2018-06-23 2
2018-06-24 2
2018-06-25 2
2018-06-26 2
June 21, 2018 at 2:05 pm
oncloudninetynine - Thursday, June 21, 2018 1:14 PMI have table where an event has a start date and end date. An event might not have a end date suggesting that its just one day event.
so here is my table
eventId title StartDate endDate
1 sometitle 2018-06-21 Null
2 someeventtitletest 2018-06-20 2018-06-26
3 anothertitle 2018-06-22 NullSo I want to have an output like list the event in all date that it has in its start and end date. How can I do this in SQL Query
evenDate eventID
2018-06-20 2
2018-06-21 1
2018-06-21 2
2018-06-22 3
2018-06-22 2
2018-06-23 2
2018-06-24 2
2018-06-25 2
2018-06-26 2
Start by creating a function that will generate a list of datesCREATE 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 simply CROSS APPLY the function to your data-- 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;
June 21, 2018 at 2:17 pm
oncloudninetynine - Thursday, June 21, 2018 1:14 PMI have table where an event has a start date and end date. An event might not have a end date suggesting that its just one day event.
so here is my table
eventId title StartDate endDate
1 sometitle 2018-06-21 Null
2 someeventtitletest 2018-06-20 2018-06-26
3 anothertitle 2018-06-22 NullSo I want to have an output like list the event in all date that it has in its start and end date. How can I do this in SQL Query
evenDate eventID
2018-06-20 2
2018-06-21 1
2018-06-21 2
2018-06-22 3
2018-06-22 2
2018-06-23 2
2018-06-24 2
2018-06-25 2
2018-06-26 2
Here you go:CREATE TABLE #Table (
eventId int NOT NULL PRIMARY KEY CLUSTERED,
title varchar(20),
StartDate date NOT NULL,
endDate date
);
INSERT INTO #Table (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);
DECLARE @MinDate AS date = (SELECT MIN(StartDate) FROM #Table);
DECLARE @MaxDate AS date = (SELECT MAX(endDate) FROM #Table);
DECLARE @NumDays AS int = DATEDIFF(day, @MinDate, @MaxDate) + 1;
WITH N1(N) AS (
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
),
ALLDates AS (
SELECT TOP (@NumDays) DATEADD(day, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @MinDate) AS TheDate
FROM N1 AS A, N1 AS B, N1 AS C, N1 AS D, N1 AS E
)
SELECT AD.TheDate AS eventDate, T.eventId
FROM #Table AS T
INNER JOIN ALLDates AS AD
ON AD.TheDate BETWEEN T.StartDate AND ISNULL(T.endDate, T.StartDate);
DROP TABLE #Table;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 18, 2018 at 2:45 pm
DesNorton - Thursday, June 21, 2018 2:05 PMoncloudninetynine - Thursday, June 21, 2018 1:14 PMI have table where an event has a start date and end date. An event might not have a end date suggesting that its just one day event.
so here is my table
eventId title StartDate endDate
1 sometitle 2018-06-21 Null
2 someeventtitletest 2018-06-20 2018-06-26
3 anothertitle 2018-06-22 NullSo I want to have an output like list the event in all date that it has in its start and end date. How can I do this in SQL Query
evenDate eventID
2018-06-20 2
2018-06-21 1
2018-06-21 2
2018-06-22 3
2018-06-22 2
2018-06-23 2
2018-06-24 2
2018-06-25 2
2018-06-26 2Start by creating a function that will generate a list of dates
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;
GOThen simply CROSS APPLY the function to your data
-- 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;
This works fantastic and bring records with in a date range . How about if I want to pick the only one record with in the same ids which has event date nearest to today. what should i modify?
July 18, 2018 at 8:25 pm
What do you mean by "nearest to today"? Can be either before or after? Then you'd have to do MIN of ABS(DATEDIFF....) or return the top 1 value...
July 19, 2018 at 11:52 am
This post duplicates the question asked here: https://www.sqlservercentral.com/Forums/1977309/Need-help-with-the-getting-a-top-1-record-with-in-a-table-with-same-ids
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 19, 2018 at 12:06 pm
sgmunson - Thursday, July 19, 2018 11:52 AMThis post duplicates the question asked here: https://www.sqlservercentral.com/Forums/1977309/Need-help-with-the-getting-a-top-1-record-with-in-a-table-with-same-ids
True, but this was not the original question asked in that thread.
July 19, 2018 at 12:15 pm
Lynn Pettis - Thursday, July 19, 2018 12:06 PMsgmunson - Thursday, July 19, 2018 11:52 AMThis post duplicates the question asked here: https://www.sqlservercentral.com/Forums/1977309/Need-help-with-the-getting-a-top-1-record-with-in-a-table-with-same-idsTrue, but this was not the original question asked in that thread.
Perhaps, but the final question that needs asking of the OP ends up being the same in both.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 19, 2018 at 12:27 pm
sgmunson - Thursday, July 19, 2018 12:15 PMLynn Pettis - Thursday, July 19, 2018 12:06 PMsgmunson - Thursday, July 19, 2018 11:52 AMThis post duplicates the question asked here: https://www.sqlservercentral.com/Forums/1977309/Need-help-with-the-getting-a-top-1-record-with-in-a-table-with-same-idsTrue, but this was not the original question asked in that thread.
Perhaps, but the final question that needs asking of the OP ends up being the same in both.
Not disagreeing on that. I think he started a separate thread with a different, but actually related, question as he got no response to his expanded question here.
July 19, 2018 at 1:43 pm
oncloudninetynine - Wednesday, July 18, 2018 2:45 PMThis works fantastic and bring records with in a date range . How about if I want to pick the only one record with in the same ids which has event date nearest to today. what should i modify?
You need to find the records where the EventDate is greater(or equal) than today, and then find the earliest date per Event.-- 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-18', '2018-07-23' )
, ( 3, 'anothertitle', '2018-07-22', NULL );
-- CROSS APPLY the function to the sample data
DECLARE @Today date = GETDATE();-- Match this datatype to the datatype of the lookup field.
SELECT
evt.eventId
, evt.title
, EventDate = MIN(ed.EventDate) -- Get the earliest date per Event
FROM #Event AS evt
CROSS APPLY dbo.fn_GetEventDates(evt.StartDate, evt.endDate) AS ed
WHERE ed.EventDate >= @Today -- Exclude dates that have already passed
GROUP BY evt.eventId, evt.title;
July 19, 2018 at 1:48 pm
DesNorton - Thursday, July 19, 2018 1:43 PMoncloudninetynine - Wednesday, July 18, 2018 2:45 PMThis works fantastic and bring records with in a date range . How about if I want to pick the only one record with in the same ids which has event date nearest to today. what should i modify?You need to find the records where the EventDate is greater(or equal) than today, and then find the earliest date per Event.
-- 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-18', '2018-07-23' )
, ( 3, 'anothertitle', '2018-07-22', NULL );-- CROSS APPLY the function to the sample data
DECLARE @Today date = GETDATE();-- Match this datatype to the datatype of the lookup field.SELECT
evt.eventId
, evt.title
, EventDate = MIN(ed.EventDate) -- Get the earliest date per Event
FROM #Event AS evt
CROSS APPLY dbo.fn_GetEventDates(evt.StartDate, evt.endDate) AS ed
WHERE ed.EventDate >= @Today -- Exclude dates that have already passed
GROUP BY evt.eventId, evt.title;
And that is the answer that you will find on the other thread mentioned above.
July 19, 2018 at 1:53 pm
Lynn Pettis - Thursday, July 19, 2018 1:48 PMDesNorton - Thursday, July 19, 2018 1:43 PMoncloudninetynine - Wednesday, July 18, 2018 2:45 PMThis works fantastic and bring records with in a date range . How about if I want to pick the only one record with in the same ids which has event date nearest to today. what should i modify?You need to find the records where the EventDate is greater(or equal) than today, and then find the earliest date per Event.
-- 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-18', '2018-07-23' )
, ( 3, 'anothertitle', '2018-07-22', NULL );-- CROSS APPLY the function to the sample data
DECLARE @Today date = GETDATE();-- Match this datatype to the datatype of the lookup field.SELECT
evt.eventId
, evt.title
, EventDate = MIN(ed.EventDate) -- Get the earliest date per Event
FROM #Event AS evt
CROSS APPLY dbo.fn_GetEventDates(evt.StartDate, evt.endDate) AS ed
WHERE ed.EventDate >= @Today -- Exclude dates that have already passed
GROUP BY evt.eventId, evt.title;And that is the answer that you will find on the other thread mentioned above.
Oops. My bad. Busy playing catching up ...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply