quote:

---

Originally posted by ajthepoolman

According to your dataset the top date should be the 10/22 date. Unless I missed something...

Hey, it compiles.

---

quote:

---

Originally posted by nigelrivett

I think you might be looking for the last ocurrance of the first sequence - i.e. take the first entry for the code then find the last entry before another code

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

declare @t table (Account int, Code varchar(15), Dt datetime)
insert @t 
select 123456, 'INBND', '10/15/2010 1:43:00 PM' union all
select 123456, 'CICBND', '10/15/2010 1:45:00 PM' union all
select 123456, 'CICBND', '10/15/2010 1:55:00 PM' union all
select 123456, 'CICBND', '10/16/2010 6:53:00 PM' union all
select 123456, 'OTBND', '10/17/2010 11:17:00 PM' union all
select 123456, 'CICBND', '10/22/2010 5:29:00 PM' union all
select 123456, 'OTBND', '10/25/2010 9:15:00 AM'

;with 
c1 as (
       select account
              ,code
              ,dt
              ,row_number() over (partition by account order by dt) rn
       from   @t
       )
,c2 as (
       select account
              ,code
              ,min(rn) rn
       from   c1
       group by account
              ,code
       )
,c3 as (
       select c1.account
              ,c2.code
              ,min(c1.rn) rn
       from   c2
       join   c1
              on c1.account = c2.account
              and c1.code != c2.code
              and c1.rn > c2.rn
       group by c1.account
              ,c2.code
       )
select c1.account
       ,c1.code
       ,max(dt) date
from   c3
join   c1
       on c1.account = c3.account
       and c1.code = c3.code
       and c1.rn < c3.rn
group by c1.account
       ,c1.code


OUTPUT:

account     code            date
----------- --------------- -----------------------
123456      CICBND          2010-10-16 18:53:00.000
123456      INBND           2010-10-15 13:43:00.000
123456      OTBND           2010-10-17 23:17:00.000

;WITH cte AS
(
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY DT)
        -  ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY DT) AS GroupSequence
    FROM
        @t        
)

SELECT 
    MAX(dt) AS MaxDate
FROM cte
WHERE
    GroupSequence = 
    (
        SELECT MIN(GroupSequence) 
        FROM cte
        WHERE
            Code = 'CICBND'
            AND Account = 123456
    )