CROSS APPLY is typically used with a table-valued function however I’ve stumbled across a neat trick with CROSS APPLY to simplify your code
Problem:
We have a set of dates in a table stored as strings. We need to check if the dates are VALID dates using ISDATE() and we also
need to check some other conditions (here I’m simplifying my example using x=x and y=y)
Solution(1)
As you can see below this solution is repeating the same logic 5 times for each column (using a CASE statement).
For each column it is repeating the condition “CASE WHEN ISDATE(dt) = 1 AND ‘x’=’x’ AND ‘y’=’y’ THEN…”
Solution(2)
This solution is using a neat trick with CROSS APPLY.
By using CROSS APPLY we can simplify the code and alias 2 result sets (dates and dates2)
The first result set (dates) returns 1 new column called “IsValid”
The second result set (dates2) returns 1 new column called “TheDate” only if dates.IsValid = 1
As you can see we have simplified Solution(1) and elimated repeating code. We have also used nested CROSS APPLY statements that
refer to the previous (i.e. dates2 refers to dates)
There are other ways of simplyfing Solution(1) using Common Table Expressions and Derived Tables though CROSS APPLY is my favourite
Solution(1)
DECLARE @Dates TABLE (dt varchar(10))
INSERT INTO @Dates
VALUES
('2012-02-27'),
('2012-02-28'),
('2012-02-29'),
('2012-02-30'),
('2012-02-31')
SELECT dt ,CASE WHEN ISDATE(dt) = 1 AND 'x'='x' AND 'y'='y' THEN 1 ELSE 0 END AS IsValid ,CASE WHEN ISDATE(dt) = 1 AND 'x'='x' AND 'y'='y' THEN CONVERT(datetime,dt) ELSE NULL END AS TheDate ,CASE WHEN ISDATE(dt) = 1 AND 'x'='x' AND 'y'='y' THEN CONVERT(datetime,dt) -1 ELSE NULL END AS ThePreviousDate ,CASE WHEN ISDATE(dt) = 1 AND 'x'='x' AND 'y'='y' THEN CONVERT(datetime,dt) +1 ELSE NULL END AS TheNextDate ,CASE WHEN ISDATE(dt) = 1 AND 'x'='x' AND 'y'='y' THEN DATEADD(m,1,CONVERT(datetime,dt)) ELSE NULL END AS TheDateNextMonth FROM @Dates
Solution(2)
SELECT dt ,dates.IsValid ,dates2.TheDate ,dates2.TheDate-1 AS ThePreviousDate ,dates2.TheDate+1 AS TheNextDate ,DATEADD(m,1,dates2.TheDate) AS TheDateNextMonth FROM @Dates CROSS APPLY ( SELECT CASE WHEN ISDATE(dt) = 1 AND 'x'='x' AND 'y'='y' THEN 1 ELSE 0 END AS IsValid) AS dates CROSS APPLY ( SELECT IIF(dates.IsValid = 1, CONVERT(datetime,dt), NULL) AS TheDate) AS dates2