Select all records where open code, but no close with date later than open?

Question

I have a table that contains the data as below

AccountNo Code    Date
123456    OpenCS  01/12/2013
123456    CloseCS 03/12/2013
123456    CALLIN  11/10/2013
123456    CloseCS 08/08/2009
111587    OpenCS  12/12/2013
111587    CloseCS 01/12/2013

What I need to get is all Accounts with a completed OpenCS code but no CloseCS AFTER the OpenCS

So I have 2 selects querys

Select AccountNo, Code, Date FROM tblTrans
WHERE Code = ‘OpenCS’

AND

Select AccountNo, Code, Date FROM tblTrans
WHERE Code = ‘CloseCS’

Where im falling down is joining these to get the required data. Tried a couple of joins but cant get the select to exclude the records.

So with this it would only return account 111587 as the Close was done before the Open.

score 2 · Accepted Answer · answered Dec 13 '13 at 12:51

2

Use NOT EXISTS:

SELECT t.* 
FROM tblTrans t
WHERE Code = 'OpenCS' 
AND NOT EXISTS
(
   SELECT 1 FROM tblTrans t2
   WHERE t2.AccountNo = t.AccountNo
   AND   t2.Date > t.Date
   AND   t2.Code = 'CloseCS'
)

Demo

ACCOUNTNO   CODE    DATE
111587     OpenCS   December, 12 2013 00:00:00+0000

Worth reading in this context: should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS? (conclusion: NOT EXISTS is typically going to be your best choice)

answered Dec 13 '13 at 12:51

Tim Schmelter

450,073
74
686
939

@dav1dsm1th: Thanks, but why have you posted this link? It's a matter of preference if you use `NULL` or `1` or `*` or whatever in the `EXISTS` since nothing is selected anyway. I prefer `1` since it's short and readable. – Tim Schmelter Dec 13 '13 at 12:58
I'm pretty sure `*` and `1` are the same length and are equally readable - but one doesn't perpetuate the myth that there is an effect on performance by choosing one over the other (which is my preference). – dav1dsm1th Dec 13 '13 at 13:08
1

@dav1dsm1th: for this reason i have earlier used `NULL` because it makes it even more clar that it's irrelevant what it selects, meanwhile i don't like `NULL` anymore since i'm lazy. `SELECT 1` makes it clearer than `SELECT *` that the query returns nothing (imho). – Tim Schmelter Dec 13 '13 at 13:16
hence my addition on the link - so that people can make up their mind which coding standard to adopt - one that they will see in every code example from Microsoft - or one that has an arbitrary/random constant that has no impact on performance but which gives the impression that it might. – dav1dsm1th Dec 13 '13 at 14:24

score 0 · Answer 2 · answered Dec 13 '13 at 12:51

0

SELECT t1.AccountNo 
FROM tblTrans T1
INNER JOIN tblTrans T2 ON T1.AccountNo = T2.AccountNo
WHERE T1.Code = 'OpenCS' AND T2.Code = 'CloseCS' AND T2.Date < T1.Date

answered Dec 13 '13 at 12:51

Dan

10,480
23
49

Select all records where open code, but no close with date later than open?

2 Answers2