developer developer - 3 years ago 89
SQL Question

SQL cross apply

I have a SQL table which contains audit information:

GroupId AuditDate ID FirstName LastName
1 01/06/2011 123 Michael Jackson
1 01/09/2010 123 M J
1 01/06/2009 123 Mike J


and trying show the differences between the audit records:

GroupId AuditDate ID Attribute From To
1 01/06/2011 123 FirstName M Michael
1 01/06/2011 123 LastName J Jackson
1 01/09/2010 123 FirstName Mike M
1 01/06/2009 123 FirstName NULL Mike
1 01/06/2009 123 LastName NULL J


I am using the following SQL query:

WITH result AS (
SELECT [Current].Id,
[Current].GroupId,
[Current].AuditDate,
[Current].FirstName,
[Current].LastName
Previous.FirstName AS PFirstName,
Previous.LastName AS PLastName,
FROM
(SELECT
*, ROW_NUMBER() OVER(PARTITION BY GroupId ORDER BY AuditDate ASC) AS RowNumber
FROM
AuditTable
WHERE
Id = @ID
) AS [Current]
LEFT JOIN
(SELECT
*, ROW_NUMBER() OVER(PARTITION BY GroupId ORDER BY AuditDate ASC) AS RowNumber
FROM
AuditTable
WHERE
Id = @ID
) AS [Previous]
ON
[Current].RowNumber = [Previous].RowNumber + 1
)

SELECT r.Id,r.GroupId, r.AuditDate
x.Attribute,
x.[From],
x.[To]
FROM result r
CROSS APPLY
(
VALUES
('FirstName', t.FirstName, t.PFirstName),
('LastName', t.LastName, t.PLastName),
) x (Attribute, [To], [From])
where
ISNULL(x.[From],'') <> ISNULL(x.[To],'')
ORDER BY r.AuditDate asc;


Is it possible to merge two select queries to improve the performance?

Answer Source

Try this query

WITH result AS (
SELECT Id, 
       GroupId, 
       AuditDate, 
       FirstName, 
       LastName,          
       ROW_NUMBER() OVER(PARTITION BY GroupId ORDER BY AuditDate ASC) AS RowNumber 
FROM AuditTable  
WHERE Id = @ID
 )
SELECT r.Id,r.GroupId, r.AuditDate,
       x.Attribute,
       x.[From],
       x.[To]
FROM result r LEFT JOIN result r2 ON r.RowNumber = r2.RowNumber + 1
CROSS APPLY (
             VALUES ('FirstName', r.FirstName, r2.FirstName),
                    ('LastName', r.LastName, r2.LastName)
             ) x (Attribute, [To], [From])
WHERE ISNULL(x.[From],'') <> ISNULL(x.[To],'') 
ORDER BY r.AuditDate ASC;

Demo on SQLFiddle

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download