Cool, glad I could help. I’ve certainly received quite a bit of help from this forum.

perhaps this will help...

DROP TABLE if exists #batch 
create table #batch (batchID int IDENTITY(1,1), 
            ...

Try this:

drop table if exists #testAddress 
CREATE TABLE #testAddress(
[ID] [int] NULL,
[houseNum] [int] NULL,
[streetNm] [varchar](50) NULL,
[StreetType] [varchar](5) NULL,
[aptno] [varchar](10) NULL,
[fullstreet] [varchar](100) NULL
) 

INSERT #testAddress ([ID], [houseNum], [streetNm], [StreetType],...

• This reply was modified 3 years, 2 months ago by  jmetape.

Thanks  for the interesting problem... Perhaps consider something like this:

SELECT ID, b.[value] bVal , .ordinal bOrd, CASE WHEN CHARINDEX ( ',', fullStreet ) = 0 THEN ''
ELSE...

• This reply was modified 3 years, 2 months ago by  jmetape.

That's a much better suggestion, Phil. Thank you.

How about a not in query?

SELECT 1 as id, 100 as subID, 'John' as [name], '123 main' as [address], 1 as status into #t
UNION SELECT 2,100,'John', 'PO box 100', 2
UNION SELECT 3,100,'John', 'fake', 2
UNION SELECT 4,200,'Mary','890 Hills',2
UNION SELECT 5,200,'Mary','890 Hills',2
UNION SELECT 6,300,'Jen','Po Box',        3

SELECT * FROM #t t
WHERE subID  NOT IN (SELECT subID from #t where status = 1)

• This reply was modified 3 years, 4 months ago by  jmetape.

That's awesome info. Thank you so much for taking the time to look into that!

Select CAST((CONVERT(DATETIMEOFFSET, GETDATE()) AT TIME ZONE 'EASTERN Standard TIME') AS DATETIME)

That did it! THank you so much!!!

Thank you for your help. I've edited my post to be easier to read and included the ddl and links to the "Paste the plan" execution plans

Update on this--it is an intermittent issue. Sometimes a trace started using the service account captures all the data, sometimes not.

Thanks for the messages, I appreciate the info. For the timing of the loading of the files, here is what we are going to do, once a day:

1....

Hi, I discovered that starting the trace from a job (as our service account) results in the username not being populated. When executed from my machine (as me), the username...

Hi PB_BI, I'm interested in your solution using the SSAS Events...

You the man