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 6 years, 1 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 6 years, 1 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...

This works... you need a GO statement between your CREATE and INSERT statements if you're going to run a single script, because they have to be in different batches.

            
June 29, 2020 at 6:46 pm
            
#3766265

            
            
                                                
        

use AdventureWorks2017;
go

DECLARE @SalesOrderID INT = 43659;

SELECT SalesOrderID
-- , ProductID
-- , bom.AssemblyName
, bom.Component
, bom.ComponentID
-- , bom.PerAssemblyQty
, TotalPartQty = CAST(SUM(OrderQty * bom.PerAssemblyQty) AS INT)
FROM Sales.SalesOrderDetail sod
CROSS APPLY dbo.udfProductBOM(sod.ProductID) bom
WHERE...

• This reply was modified 6 years, 2 months ago by pietlinden.

So where are you stuck? Post the SQL statement(s), so we can figure out how to help you.