Retrieving last changes from log table.

Table definition

ScreenShot131

SELECT LogId, CreationDate, UserId, ApprovalForSendingSMS
FROM dbo.Log
WHERE UserId = 102
ORDER BY CreationDate DESC

ScreenShot133

SELECT TOP 1 x.CreationDate, x.CurrentApprovalForSendingSMS, x.PreviousApprovalForSendingSMS
FROM
(
    SELECT l.CreationDate, l.ApprovalForSendingSMS AS CurrentApprovalForSendingSMS, (SELECT TOP 1 e.ApprovalForSendingSMS FROM dbo.Log e WHERE l.UserId = e.UserId AND e.CreationDate < l.CreationDate ORDER BY e.CreationDate DESC) AS PreviousApprovalForSendingSMS
    FROM dbo.Log l
    WHERE l.UserId = 102    
) x 
WHERE x.CurrentApprovalForSendingSMS  x.PreviousApprovalForSendingSMS
ORDER BY x.CreationDate DESC 

Final result…

ScreenShot134

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s