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 5 years, 8 months ago by pietlinden.

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

have you looked up GROUP BY yet?

This is close, but it needs to be changed for the first week of year/last week of previous year comparison.

=CALCULATE( [Total Sales], 
    ...

Oh, sorry... left out a bit of critical information/part of a step. If you want to allow the parameter to be null, do this:

With the report in design mode, right-click...