Like this maybe?

DELETE
FROM #tblTest1
WHERE ID IN (
SELECT ID
FROM #tblTest1 t1
CROSS APPLY STRING_SPLIT(t1.SName, '/') ss
WHERE ss.value  IN (SELECT SName FROM #tblTest2)
);

• This reply was modified 5 years, 9 months ago by pietlinden.

Did you try running SSMS as admin, too?

One way is to use a Calendar table, and to include Fiscal Month and Fiscal Quarter, and join to that. And then do the pivot

Without a Contacts table, you can't do it.

You'd get all the contacts who had been contacted in the last 18 months, and then exclude those ContactIDs from those in the...

STRING_AGG()?

USE AdventureWorks2017;
GO

SELECT OrganizationLevel
, STRING_AGG(CONVERT(nvarchar(max), JobTitle), CHAR(13)) AS csv
FROM HumanResources.Employee
GROUP BY OrganizationLevel;

Is there any way to replace all those NOT IN ( ) clauses? Basically, you're forcing a table scan on all of those columns you're using that on.

How would you get driving distance? use a web API or a .NET library to get the directions from Point A to Point B? (Is this a Solomon Rutzky question?)

Why not just a table of  (BuildingFromID, BuildingToID, Distance) ? you'd have to create a check to make sure there are no duplicates in (From,To) and (To,From).

DECLARE @v VARCHAR(100)='O:\Z\P_Metrics\Inbox\07-13-20200702_abc_enroll.csv';

DECLARE @V1 VARCHAR(100)='O:\Z\P_Metrics\Inbox\07-14-20200702_abc_complete_enroll.csv';

Solution:

SELECT LEFT(@V1,LEN(@V1) - CHARINDEX('\',REVERSE(@V1),1))

Returns:

O:\Z\P_Metrics\Inbox

O:\Z\P_Metrics\Inbox

• This reply was modified 5 years, 10 months ago by pietlinden.

Off the top of my head, you'd use a cross join so you'd get two copies of Building, and then if your buildings are really close together, you could just...

Okay, just found one really interesting feature... described here: https://cloud.google.com/bigquery/docs/querying-wildcard-tables#limitations

It makes sense if your data pipeline generates something like CSV files with a format of [standard file prefix]MMDDYY

the part to...

• This reply was modified 5 years, 10 months ago by pietlinden.

CREATE TABLE and INSERT scripts, so people can run your query against something.... Otherwise, you won't get a tested answer. You like answers that are actually tested, right?

Oh, I definitely wouldn't do that! It would be more like "Notes from the road: what I learned (and maybe didn't) using BigQuery"

Be warned, I'm a rank beginner... I'm absolutely clueless about BigQuery, but that might be a good thing. I guess I might have to just make an account for BigQuery...

Get well soon, Ken!

Who else will put up with my "How does this thing work again??!!!" questions?