This is a simple example of using an SQL Outer apply to create a select query that can create queryable custom sub fields.
Before being shown outer apply logic, I was using a temp tables and sub query. The sub table added the data required on the temp table and then I queried the temp table as needed. Now I can do it all in one simpler and quicker select query.
The simple query
This example is done on a user_extended table that is a Key and value pair logic, that acts as a dumping group for records that maybe used later. When a record is inserted and it contains the same key as a record in the DB table, the DB table record is disabled and this new record is made active.
The purpose of this it to check if the created date record is the active and is not disabled.
SELECT Parent.Username, ActiveDate,DisableDate FROM User AS Parent OUTER APPLY
(
SELECT TOP 1
MAX (CASE WHEN Child.Active= 1 AND Child.key = 'LastLoggedIn' THEN Child.DateOfCreation END) AS ActiveDate,
MAX (CASE WHEN Child.Active = 0 AND Child.key = 'LastLoggedIn' THEN Child.DateOfCreation END) AS DisableDate
FROM [Users_Extended] AS Child
WHERE Child.UserId = Parent.Id
group by DateOfCreation
ORDER BY Child.DateOfCreation DESC
) Child
WHERE Parent.key = 'LastLoggedIn' AND Parent.Active = 0 AND ActiveDate < DisableDate;
ORDER BY Parent.Id DESC
Top comments (0)