Select query using an outer apply

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

Blog

Select query using an outer apply

Chris Cooper

Join Our Newsletter. No Spam, Only the good stuff.

Related