SQLTeam.com | Weblogs | Forums

Trying to get data that is both "T' and "E" on a table


#1

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?


#2

Please post some sample data (CREATE TABLE statements and INSERT INTO statements) and show what results you expect using that sample data.


#3

TransT_lgnNumber EntryEvent_strEvent EntryEvent_dtmEventDate Workstation_strCode User_intUserNo
1190090 T 2015-02-04 19:15:31.000 PagUsher1 3000
1219806 T 2015-04-25 18:57:23.000 PagUsher3 9999
1221162 T 2015-04-30 21:46:29.000 PagUsher3 9999
1221162 T 2015-04-30 22:12:21.000 PagUsher1 3000
1234061 T 2015-06-25 21:48:09.000 PagUsher1 3000
1234061 E 2015-06-25 21:48:09.000 PagUsher1 3000

expected all except transaction 1234061


#4

Thanks for the data, but we need CREATE TABLE statements and INSERT INTO statements.


#5

[code] GO

/****** Object: Table [dbo].[tblEntryEvent] Script Date: 3/7/2015 4:46:05 μμ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblEntryEvent](
[EntryEvent_intID] [int] IDENTITY(1,1) NOT NULL,
[TransT_lgnNumber] [int] NOT NULL,
[TransT_intSequence] [smallint] NOT NULL,
[EntryEvent_strEvent] varchar NULL,
[EntryEvent_dtmEventDate] [datetime] NULL,
[Workstation_strCode] varchar NULL,
[User_intUserNo] [smallint] NULL,
CONSTRAINT [PK_tblEntryEvent] PRIMARY KEY NONCLUSTERED
(
[EntryEvent_intID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

[/code]

Insert into tblentryevent values '1190090 ,   'T' ,   '2015-02-04 19:15:31.000' ,   'PagUsher1',    '3000')
,values (1234061,    'T',    '2015-06-25 21:48:09.000',    'PagUsher1',    '3000'),
values(1234061    'E'    '2015-06-25 21:48:09.000'    'PagUsher1'    '3000')

#6

Ok -- so I'm looking at this, Just need to know: How do I identify a unique transaction with multiple states? UserId (PagUsher1) or something else?


#7

Here's one way:

SELECT *
FROM tblentryevent t1
WHERE EntryEvent_strEvent = 'T'
	AND 'T' = ALL (
		SELECT EntryEvent_strEvent
		FROM tblEntryEvent t2
		WHERE t2.Workstation_strCode = t1.Workstation_strCode
			AND t2.User_intUserNo = t1.User_intUserNo
		)

#8
SELECT --TOP (1000)
      ee.[TransT_lgnNumber]
      ,ee.[TransT_intSequence]
      ,ee.[EntryEvent_strEvent]
      ,ee.[EntryEvent_dtmEventDate]
      ,ee.[Workstation_strCode]
      ,ee.[User_intUserNo]
FROM tblEntryEvent ee
INNER JOIN (
    SELECT TOP (1000) ee2.TransT_lgnNumber
    FROM tblEntryEvent ee2
    GROUP BY ee2.TransT_lgnNumber
    HAVING SUM(CASE WHEN ee2.EntryEvent_strEvent = 'T' THEN 1 ELSE 0 END) = COUNT(*)
) AS ee_T_only ON
    ee_T_only.TransT_lgnNumber = ee.TransT_lgnNumber

#9

Seems to work.
Thanks.
So my approach was wrong?


#10

Hey gbritton.
I do not need to bind to workstation or user_intuserno.
Just need to find the T that are not also in X,E,A,P.
If i remove the where's inside the ALL clause i get an empty result set.


#11

no surprise there! You're basically saying "return all rows with T where there are no rows with 'T' in the table. That's why the WHERE clause is there. Note, I had to guess what you were matching on.

(You haven't answered the question, "Ok -- so I'm looking at this, Just need to know: How do I identify a unique transaction with multiple states? UserId (PagUsher1) or something else?")

If I guessed wrong, just change the WHERE to what you are matching on e.g. if you are matching on TransT_lgnNumber then change it to:

WHERE T2.TransT_lgnNumber  = T1.TransT_lgnNumber

#12

Yep, works fine now.Thanks

[CODE]

SELECT t1.[TransT_lgnNumber]
-- ,ee.[TransT_intSequence]
,t1.[EntryEvent_strEvent]
,t1.[EntryEvent_dtmEventDate]
,t1.[Workstation_strCode]
,t1.[User_intUserNo]
FROM tblentryevent t1
WHERE EntryEvent_strEvent = 'T'
AND 'T' = ALL (
SELECT EntryEvent_strEvent
FROM tblEntryEvent t2
WHERE --t2.Workstation_strCode = t1.Workstation_strCode
T2.TransT_lgnNumber = T1.TransT_lgnNumber
)
group by t1.[TransT_lgnNumber] -- ,ee.[TransT_intSequence]
,t1.[EntryEvent_strEvent]
,t1.[EntryEvent_dtmEventDate]
,t1.[Workstation_strCode]
,t1.[User_intUserNo]
[/CODE]


#13

SELECT t.TransT_lgnNumber, t.EntryEvent_strEvent, t.EntryEvent_dtmEventDate, t.Workstation_strCode, t.User_intUserNo FROM dbo.tblEntryEvent AS t INNER JOIN ( SELECT TransT_lgnNumber FROM dbo.tblEntryEvent GROUP BY TransT_lgnNumber HAVING MIN(EntryEvent_strEvent) = 'T' AND MAX(EntryEvent_strEvent) = 'T' ) AS w ON w.TransT_lgnNumber = t.TransT_lgnNumber;


#14

Another method similar to the one posted by Peso

SELECT t.TransT_lgnNumber,
t.EntryEvent_strEvent,
t.EntryEvent_dtmEventDate,
t.Workstation_strCode,
t.User_intUserNo
FROM dbo.tblEntryEvent AS t
INNER JOIN (
SELECT TransT_lgnNumber
FROM dbo.tblEntryEvent
GROUP BY TransT_lgnNumber
HAVING COUNT(DISTINCT EntryEvent_strEvent) = 1
AND MAX(EntryEvent_strEvent) = 'T'
) AS w ON w.TransT_lgnNumber = t.TransT_lgnNumber;