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, 4 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, 4 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?

Thanks,  Steve, I'll check it out. (Gotta say, I never expected to have to help my nephew figure his way through BigQuery, but here I am!)

This book looks promising, but...

The OUTER APPLY lets you join a table to a table-valued function, which you can't do with LEFT JOIN (that just joins tables/views). The APPLY forces the TOP query to...

This is really odd:

LEFT JOIN View.Costs AS CO

You're using the keyword VIEW as a schema name? That feels all kinds of No. Don't use reserved words as object names in...

Brian,

Oh right. I think I was reacting to the design flaw... =(

Wonder if I can repivot once the two groups are separated.

Pieter

I went a slightly different route... I used Jeff Moden's DelimitedSplit8K function to do this...  (some of the bits are just intermediary calculations)

SELECT ft.RawValue
, ca.ItemNumber
, TrimmedItem...