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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy