SQL查询记录之间的开始日期和结束日期范围

i have database structured like this:

我有这样的数据库结构:

and Output like this:

和输出像这样:

so, how to make SQL Query?

那么,如何进行SQL查询?

1 个解决方案

#1

Try the following query:

请尝试以下查询:

first we need to generate CalendarData on the fly using sys.all_objects or [master]..spt_values as shown here:

首先,我们需要使用sys.all_objects或[master] .. spt_values动态生成CalendarData,如下所示:

DECLARE @MinDate DATE = '2017-01-01',
        @MaxDate DATE = '2017-12-31';

SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
    DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate) as CalendarDate
FROM
    sys.all_objects a
CROSS JOIN 
    sys.all_objects b;

For more information check this link https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2

有关更多信息,请访问此链接https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2

So the full solution might be like

所以完整的解决方案可能就像

DECLARE @MinDate DATE = '2017-01-01',
        @MaxDate DATE = '2017-12-31';

WITH calendareDates AS
(
    SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.number) - 1, @MinDate) AS CalendarDate
    FROM    
        [master]..spt_values a
    --CROSS JOIN [master]..spt_values b   -- use this cross join to get more records only if your dates between Start and End Date are over years.
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY CalendarDate) id,
    c.CalendarDate, e.eventTitle, e.eventId  
FROM
    Events e 
LEFT JOIN
    calendareDates c ON c.CalendarDate BETWEEN e.StartDate AND e.EndDate

#1