One of the common requirements for SQL developer is to get
latest or last record from a transaction table. This can
be achieved by using Correlated subqueries.
Lets take an example, we have employee table with 7 fields.
Now, I would like to fetch latest transaction
record from employee table (
SELECT a.transid, a.emp_id, a.emp_name, a.emp_address, a.sex, a.marital_status, a.CreatedDate
from
Employee a
WHERE NOT EXISTS (select 1
from
Employee b
where
b.emp_name = a.emp_name and
b.emp_address
= a.emp_address
and
b.sex
= a.sex and
--b.[CreatedDate]
= a.[CreatedDate] and
b.[TransId]
> a.[TransId])
Next, based on transaction date I am displaying latest record.
SELECT a.transid, a.emp_id, a.emp_name, a.emp_address, a.sex, a.marital_status, a.CreatedDate
from
Employee a
WHERE NOT EXISTS (select 1
from
Employee b
where
b.emp_name = a.emp_name and
b.emp_address
= a.emp_address
and
b.sex
= a.sex and
b.[CreatedDate]
> a.[CreatedDate]
--b.[TransId]
> a.[TransId]
)
Following example shows how to fetch first transaction
record.
SELECT a.transid, a.emp_id, a.emp_name, a.emp_address, a.sex, a.marital_status, a.CreatedDate
from
Employee a
WHERE NOT EXISTS (select 1
from
Employee b
where
b.emp_name = a.emp_name and
b.emp_address
= a.emp_address
and
b.sex
= a.sex and
--b.[CreatedDate]
= a.[CreatedDate] and
b.[TransId]
< a.[TransId])
I came across with few business scenario’s where few of the employee’s
doesn’t have FirstLoginDate, so business asked me to take the first transaction
date (in this case field name is CreatedDate) as FirstLoginDate. I used below
query to update it.
UPDATE T1
SET
[FirstLoginDate] = T2.CreatedDate
FROM
[Varada_ETL_Test].[dbo].EmployeeLoginDetails
T1
INNER Join
(
SELECT
a.transid, a.emp_id, a.emp_name, a.emp_address, a.sex, a.marital_status, a.CreatedDate
from
Employee a
WHERE
NOT EXISTS (select 1
from Employee b
where b.emp_name = a.emp_name and
b.emp_address = a.emp_address and
b.sex = a.sex and
b.[CreatedDate] < a.[CreatedDate]
--b.[TransId] > a.[TransId]
)
) AS T2 ON T2.emp_id = T1.emp_id
WHERE
[FirstLoginDate] IS NULL
Reference material for Subqueries:
