TSQL Inner Join

Transact-SQL
1

I am having an issue with my selection. I need to return the old rank if the PaymentDate less than the datestamp from Log_Promotion, but I can’t seem to get it to return the correct record. Not all Customers are in the Table Log_Promotion

OrderID 1000285 = should be OldRankID = 1
OrderID 1000539 = should be OldRankID = 2
OrderID 1000581 = should be OldRankID = 2
OrderID 1000611 = should be OldRankID = 3

Table Log_Promotion
CustomerID OldRankID NewRankID CommissionPeriod datestamp
9082958342 1 2 201509 2015-09-21 18:11:43.543
9082958342 2 3 201509 2015-09-21 19:45:59.100

Table Orders
OrderID CustomerID PaymentDate
1000285 9082958342 2015-09-21 17:11:43.543
1000539 9082958342 2015-09-21 19:18:54.123
1000581 9082958342 2015-09-21 19:41:59.100
1000611 9082958342 2015-09-21 20:17:24.173

MSSQL
IIF(lp.datestamp IS NULL,c.RankID,lp.OldRankID) FROM Customers c WITH (NOLOCK) LEFT JOIN Log_Promotion lp WITH (NOLOCK)
ON (lp.CustomerID = c.CustomerID AND @PaymentDate <= lp.datestamp) WHERE c.CustomerID = 9082958342)

2

You might be able to use some of this:

with Log_Promotion(CustomerID,OldRankID,NewRankID,CommissionPeriod,datestamp)
  as (          select 9082958342,1,2,201509,cast('2015-09-21 18:11:43.543' as datetime)
      union all select 9082958342,2,3,201509,cast('2015-09-21 19:45:59.100' as datetime)
     )
    ,Orders(OrderID,CustomerID,PaymentDate)
  as (          select 1000285,9082958342,cast('2015-09-21 17:11:43.543' as datetime)
      union all select 1000539,9082958342,cast('2015-09-21 19:18:54.123' as datetime)
      union all select 1000581,9082958342,cast('2015-09-21 19:41:59.100' as datetime)
      union all select 1000611,9082958342,cast('2015-09-21 20:17:24.173' as datetime)
     )
    ,cte1(customerid,oldrankid,newrankid,datestamp,rn) /* prepare to selfjoin log_promotion in correct order */
  as (select customerid
            ,oldrankid
            ,newrankid
            ,datestamp
            ,row_number() over(partition by customerid order by datestamp) as rn
        from log_promotion
       where customerid=9082958342 /* can be removed if querying all customers */
     )
    ,cte2(customerid,rankid,datestampstart,datestampend) /* prepare log_promotion with datetime ranges */
  as (select a.customerid
            ,a.oldrankid as rankid
            ,isnull(b.datestamp,'19000101 00:00:00.000') as datestampstart
            ,a.datestamp as datestampend
        from cte1 as a
             left outer join cte1 as b
                          on b.customerid=a.customerid
                         and b.rn=a.rn-1
       where a.rn=1 /* only get first rank (for each customer) */
      union all
      select a.customerid
            ,a.newrankid as rankid
            ,a.datestamp as datestampstart
            ,isnull(b.datestamp,cast('99991231 23:59:59.998' as datetime)) as datestampend
        from cte1 as a
             left outer join cte1 as b
                          on b.customerid=a.customerid
                         and b.rn=a.rn+1
     )
select o.*
      ,p.rankid
  from orders as o
       inner join cte2 as p
               on p.customerid=o.customerid
              and p.datestampstart<=o.paymentdate
              and p.datestampend>o.paymentdate
 where o.customerid=9082958342
;

ps.: And please avoid using nolock