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

Latest blogs

Buy me a coffeeBuy me a coffee
Created: 16/01/2019 Total Comment: 0

Comments

(Not Displayed)
Human validation
Enter 2610 number, before submitting to confirm your human
[Home] [All Blogs] [Contact] [Me]
Buy me a coffeeBuy me a coffee