>The smart safe transaction table has two possible eventcode values DECL and VER
>
>a VER transaction can relate to one or DECL transactions.
>
>The verifyid column in a DECL transaction points to the VER transaction for that row, if any.
>
>I was to select ALL DECL rows where the verifyid column is 0 or if it's not zero the postingdate in the related VER transaction is earlier than a given date
>
>I get this far and go blank
>
>
select * from view _expandedsmartsafetrans where eventcode = 'DECL' and verifyid = 0
UNION ALL
select v.* from view _expandedsmartsafetrans v cross apply (select top(1) * from _expandedsmartsafetrans vVer
where vVer.eventCode = 'VER' and v.Verifyid = vVer.Id and vVer.PostingDate < @SomeDate order by vVer.PostingDate DESC) Ver
where v.eventcode = 'DECL'
This would not exclude rows if you have verfied afer the @SomeDate.
If it's not broken, fix it until it is.
My Blog