sql-server

Using where clause in Sql Server LEAD

I'm writing a query which involves LEAD. I recently discovered it and made my life a lot easier. But I have a small problem

My query is this;

SELECT a.ActivityTypeID,acs.ActivityStatementID,
LEAD(acs.ActivityStatementID) OVER (PARTITION BY StatementCode ORDER BY a.ActualDateTime DESC) PreviousStatementID,
acs.Value as CurrentValue,
LEAD(acs.Value) OVER (PARTITION BY StatementCode ORDER BY a.ActualDateTime DESC) as Prev
FROM ActivityStatement acs 
   INNER JOIN Activity a on a.ActivityID = acs.ActivityID
WHERE a.CustomerID = 128077
and a.TenantID = 19
and a.ActualDateTime IS NOT NULL

And a line in my query result is this

+----------------+---------------------+---------------------+--------------+------+
| ActivityTypeID | ActivityStatementID | PreviousStatementID | CurrentValue | Prev |
+----------------+---------------------+---------------------+--------------+------+
| 397            | 849433              | 849609              | A            | A    |
+----------------+---------------------+---------------------+--------------+------+

But when I check the details of the returning ID's as this 

select acs.activitystatementid,a.ActivityTypeID,a.ActualDateTime from ActivityStatement acs
  inner join Activity a on a.ActivityID = acs.ActivityID
  where acs.ActivityStatementID IN (849433,849609)
  and a.CustomerID = 128077

I see that ActivityTypeID of records are not the same

+---------------------+----------------+-------------------------+
| activitystatementid | activitytypeid | ActualDateTime          |
+---------------------+----------------+-------------------------+
| 849433              | 397            | 2018-05-21 11:59:37.000 |
+---------------------+----------------+-------------------------+
| 849609              | 396            | 2018-05-21 11:59:05.000 |
+---------------------+----------------+-------------------------+

Basically, I want to make sure in my first query that LEAD is comparing only records with same ActivityTypeID but I couldn't find how to do it.

Sql Server Version is Sql Server 2016(SP1-CU3)

Solution

  • "...comparing only records with same ActivityTypeID".

    The key to accomplishing this is the additional , ActivityId in the PARTITION BY clause.

    SELECT    a.ActivityTypeID
        , acs.ActivityStatementID
        , LEAD(acs.ActivityStatementID) OVER (
            PARTITION BY StatementCode, ActivityId 
            ORDER BY a.ActualDateTime DESC) AS PreviousStatementID
        , acs.Value as CurrentValue
        , LEAD(acs.Value) OVER (
            PARTITION BY StatementCode, ActivityId 
            ORDER BY a.ActualDateTime DESC) AS Prev
FROM      ActivityStatement AS acs 
          JOIN Activity AS a 
            ON a.ActivityID = acs.ActivityID
WHERE     a.CustomerID = 128077
          AND a.TenantID = 19
          AND a.ActualDateTime IS NOT NULL