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:
--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
July 15, 2022 at 6:40 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 15, 2022 at 7:16 pm
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'
July 15, 2022 at 7:40 pm
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
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
July 15, 2022 at 9:45 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply