There's one DB table 'SiteTransactions' (in Sql Server) which currently has the records in following sample format

This table stores all of the SiteName transaction details. Both Create and Delete transactions are stored in this same table. The problem here is that there is no dedicated identifier column which can tell whether a given SiteGroup-SiteName combination is currently active (i.e. not yet been deleted)

SiteName is always unique and no 2 active SiteNames can have same name UNLESS a given SiteName has been previously deleted. 'TestSite2' in above table example.

Requirement

The requirement as mentioned above is to get a list of active only SiteGroup-SiteName combination which have not been deleted yet.

Only way to do so is to check if any SiteGroup-SiteName combination has a Delete transaction type value or not and compare the TransactionTime.

The correct output for above sample table should be as follows since only TestSite3 after once created has not been deleted (hence it's considered to be active):

What I Tried

Right now I've built following query by comapring TransactionTime timestamps:

SELECT DISTINCT(A.SiteName), A.SiteGroup, A.TransactionType, A.TransactionTime
FROM SiteTransactions A, SiteTransactions B
WHERE A.SiteName= B.SiteNameAND A.SiteGroup = B.SiteGroup 
AND A.TransactionType = 'Create'
AND A.TransactionTime> B.TransactionTime
ORDER BY A.TransactionTime DESC

But the problem with my query is that it also returns TestSite2 records (which has been deleted twice) due to the TransactionTime comparison check I have in place

What would be the tweak I need to make to get the required output?

CodePudding user response：

You can use a conditional windowed count for this

SELECT
  Id,
  TransactionType,
  SiteGroup,
  SiteName
FROM (
    SLEECT *,
      cnt = COUNT(CASE WHEN TransactionType = 'Delete' THEN 1 END)
              OVER (PARTITION BY SiteGroup, SiteName
                   ORDER BY TransactionTime ROWS UNBOUNDED PRECEDING)
    FROM YourTable t
) t
WHERE cnt = 0;

CodePudding user response：

You can use the Row_Number ranking function to determine the type of the last transaction in each group.

Select ID, TransactionType, SiteGroup, SiteName
From 
(Select ID, TransactionType, SiteGroup, SiteName,
Row_Number() Over (Partition by SiteGroup, SiteName Order by TransactionTime Desc) As Num
From SiteTransactions) As t
Where Num=1 And TransactionType='Create'