However, as you can see, the age_in_years has to be calculated three times. And, that’s exactly where CROSS APPLY can help us.
Getting the report using the SQL CROSS APPLY
CROSS APPLY allows us to reuse the age_in_years value and just pass it further when calculating the next_anniversary and days_to_next_anniversary values.
For instance, the previous SQL Server query can be rewritten like this:
b.id as blog_id,
(age_in_years + 1),
) AS next_anniversary,
DATEADD(year, (age_in_years + 1), created_on)
) AS days_to_next_anniversary
FROM blog b
CROSS APPLY (
FLOOR(DATEDIFF(week, b.created_on, GETDATE()) / 52.177457) AS age_in_years
) AS t
ORDER BY blog_id
Much better, right?
The age_in_years is calculated for every record of the blog table. CROSS APPLY works like a correlated subquery, but, unlike a correlated subquery, the subquery records are also JOIN-ed with the primary table, and, for this reason, we can reference the columns produced by the CROSS APPLY subquery.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
CROSS APPLY is a very useful feature when working with SQL Server. You can use it with OPENJSON to map a JSON array to a relational database table that you can further transform using all the available SQL features.
Unlike joining directly with a Derived Table, CROSS APPLY is evaluated for every record in the primary table, and not just once.