Return data from a calendar table with associated active events

Question

Post reply

Return data from a calendar table with associated active events

Chrissy321

SSCoach

Points: 15750

July 15, 2022 at 5:07 pm

Go to Answer

#4063916

Hello,

Thanks if you can help.

I am trying to build a view that returns all calendar data for a date range and any associated events if the event is active on that day.

I am getting error "An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference." on view creation.

The view should return all dates between @StartDate and @EndDate

Given my sample data and @StartDate and @EndDate values:

Event1 should not be returned
Event2 should be returned for all dates.
Event3 should not be returned
Event4 should be returned between 2022-07-01 and 2022-07-15
Event5 should be returned between 2022-07-20 and 2022-07-25

--Build a temp calendar table
DROP TABLE IF EXISTS Calendar
CREATE TABLE Calendar (CalendarDate DATE)

DECLARE @Start DATE = '2022-01-01'
DECLARE @End DATE = '2022-12-31'

WHILE ( @Start < @End )
BEGIN
  INSERT INTO Calendar  (CalendarDate) VALUES( @Start )
  SELECT @Start = DATEADD(DAY, 1, @Start )
END

DROP TABLE IF EXISTS Events

SELECT * 
INTO Events
FROM
(
SELECT 1 AS EventID, '2022-01-01' AS EventStartDate, '2022-02-01' AS EventEndDate UNION ALL
SELECT 2 AS EventID, '2022-01-01' AS EventStartDate, '2023-01-01' AS EventEndDate UNION ALL
SELECT 3 AS EventID, '2023-01-01' AS EventStartDate, '2023-02-01' AS EventEndDate UNION ALL
SELECT 4 AS EventID, '2022-07-01' AS EventStartDate, '2022-07-15' AS EventEndDate UNION ALL
SELECT 5 AS EventID, '2022-07-20' AS EventStartDate, '2022-07-25' AS EventEndDate
) E

--SELECT * FROM Calendar
--SELECT * FROM Events
GO
CREATE OR ALTER VIEW Test
AS
SELECT * FROM Calendar C
LEFT JOIN Events E
ON 
E.EventStartDate <= MAX(C.CalendarDate) AND EventEndDate >= MIN(C.CalendarDate)
GO
DECLARE 
@StartDate DATE = '2022-07-01',
@EndDate DATE = '2022-07-31'

SELECT
*
FROM Test T
WHERE
CalendarDate >= @StartDate AND CalendarDate <= @EndDate

kaj

SSCommitted

Points: 1580
More actions
July 15, 2022 at 8:10 pm
Answer
#4063984
Okay, maybe I'm a bit daft, but couldn't you simply use this:
```
CREATE OR ALTER VIEW Test
AS
SELECT * 
FROM Calendar C
LEFT JOIN Events E ON C.CalendarDate BETWEEN E.EventStartDate AND E.EventEndDate
```

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100949 More actions · Answer 1

Btw, you don't seem to realize that you are not including the last day of the month in the Calendar table.

CREATE OR ALTER VIEW Test
AS
SELECT * FROM Calendar C
LEFT JOIN Events E
ON 
E.EventStartDate <= (SELECT MAX(CalendarDate) FROM Calendar) AND
E.EventEndDate >= (SELECT MIN(CalendarDate) FROM Calendar) 
GO

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Chrissy321 SSCoach Points: 15750 More actions · Answer 2

Event 1 ended on 2022-02-01 so it shouldn't be included when querying the view for @StartDate = '2022-07-01' and @EndDate = '2022-07-31'

Chrissy321 SSCoach Points: 15750 More actions · Answer 3

I think the below does it. Any comments about performance best practices for doing this sort of thing would be welcome.

CREATE OR ALTER VIEW Test

AS

SELECT * FROM Calendar C

CROSS APPLY

(

SELECT * FROM Events E

WHERE

E.EventStartDate <= C.CalendarDate AND EventEndDate >= C.CalendarDate

) E

Chrissy321 SSCoach Points: 15750 More actions · Answer 4

kaj wrote:

Okay, maybe I'm a bit daft, but couldn't you simply use this:
CREATE OR ALTER VIEW Test
AS
SELECT * 
FROM Calendar C
LEFT JOIN Events E ON C.CalendarDate BETWEEN E.EventStartDate AND E.EventEndDate

No not daft, just sensible. You have seen right through the unnecessary complexity I was creating. Thanks.