Basically what I want is all transactions that are only in 'T' . The
issue here is that all transactions that are not in 'T' (like 'E', 'A',
etc) will have and initial 'T' transaction first.
So there might by a transaction 111 that is only in 'T' but another transaction 112 is in 'T' and in 'A'.
not sure if i am clear but this is what I ended up with (just look at
transT_lgnNumber that is the 111,112 etc and entryevent_strevent that is
'T' , 'A' ,'E' etc)
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 -- T.EntryEvent_intID
T.[TransT_lgnNumber]
-- ,T.[TransT_intSequence]
,T.[EntryEvent_strEvent]
,T.[EntryEvent_dtmEventDate]
,T.[Workstation_strCode]
,T.[User_intUserNo]
FROM tblEntryEvent T
inner join ( select EE.TransT_lgnNumber from tblEntryEvent EE where (EE.entryevent_strevent <> 'X') and (EE.entryevent_strevent <> 'A') and (EE.entryevent_strevent <> 'P') and (EE.entryevent_strevent <> 'E')
group by EE.TransT_lgnNumber
) as EE
on EE.TransT_lgnNumber = T.TransT_lgnNumber
where (T.entryevent_strevent <> 'E') and (T.entryevent_strevent <> 'X') and (T.entryevent_strevent <> 'A') and (T.entryevent_strevent <> 'P')
group by T.[TransT_lgnNumber] --,T.EntryEvent_intID
-- ,T.[TransT_intSequence]
,T.[EntryEvent_strEvent]
,T.[EntryEvent_dtmEventDate]
,T.[Workstation_strCode]
,T.[User_intUserNo]
-- select * from tblEntryEvent where TransT_lgnNumber = 115251
-- or this one is more clear on the except-- All this is in 'T'
--SELECT TOP 1000
--T.[TransT_lgnNumber]
--,T.[EntryEvent_strEvent]
--,T.[EntryEvent_dtmEventDate]
--,T.[Workstation_strCode]
--,T.[User_intUserNo]
--FROM tblEntryEvent T where T.entryevent_strevent = 'T'
EXCEPT
--- all in 'X','A','P','E' and 'T' but not all that is just in 'T'
select T.TransT_lgnNumber ,T.[EntryEvent_strEvent]
,T.[EntryEvent_dtmEventDate]
,T.[Workstation_strCode]
,T.[User_intUserNo] from tblEntryEvent T
inner join ( select EE.TransT_lgnNumber from tblEntryEvent EE where EE.entryevent_strevent in('X','A','P','E') and (EE.entryevent_strevent <>'T')
group by EE.TransT_lgnNumber) as EE on EE.TransT_lgnNumber = T.TransT_lgnNumber
--and (EE.entryevent_strevent <> 'A') and (EE.entryevent_strevent <> 'P') and (EE.entryevent_strevent <> 'E')
--and (EE.entryevent_strevent <> 'T')
where (T.entryevent_strevent = 'T')
group by T.TransT_lgnNumber
--, T.EntryEvent_intID
-- ,T.[TransT_intSequence]
,T.[EntryEvent_strEvent]
,T.[EntryEvent_dtmEventDate]
,T.[Workstation_strCode]
,T.[User_intUserNo]
So there second transaction will bring everything that is in 'T' and
'X', 'A', 'P', 'E' but not just in 'T' , so EXCEPT-ed with the first
transaction that will bring everything up (and probably did it wrong as I
wanted to bring every transaction this in 'T' and in( 'X', 'A', 'P',
'E' ) I end up with this.
Anything better?