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 2 years, 9 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 2 years, 9 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 2 years, 11 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

"this sounds like an anti-virus problem"

Mr Magoo wins the prize. I installed FileZilla FTP yesterday.. and some antivirus software got installed along with it. I uninstalled that... no more problem

Thank...

Thank you to you both for the suggestions. I haven't made time to investigate these proposed solutions, but I'll let you know the results when I do.

Thanks,

Joe