I think it can be simplified a bit, while also allowing for another value to be found in the future (maybe 'null'?):

SELECT
    LongString,
 ...

Most likely that's still best.

Btw, do you know for certain that a given product only occurs once on each order?

Actually, in ISO week, week# is controlled by Thursday being in the week.  (Seriously, you can look it up.)

So, since this year happens to start on a Friday, ISO week...

I think SQL Server loosened up on that.  I believe you just need a unique index to create a FK against.

It seems like you're doing a straightforward counting of 7 days as a week, regardless of the staring day of the week (SQL doesn't do that), then try this calc...

Nope, not unless you do it yourself in some way.

Hmm, yeah, I would think that would give you a lot of old customers, since there's no "recent sale date" requirement.  Maybe you need something more along these lines...?

            
February 4, 2021 at 6:59 pm
            
#3841115

            
            
                                                
        

Assuming "dates" could only be a number between 1 and 31...

DECLARE @UnitMembership TABLE
        (
    ...

As Jeff Moden as pointed out, for performance reasons, it's likely to best to create a physical tally table (if you don't already have one) to use for this query:

I would think this would be the most straightforward way, although it may not be the most efficient way.  But it might work just fine for you if...

Actually the use of "parent" and "child" predates databases, for example with directories.  Parent directory and child directory were very commonly used terms.

As to tables, not every referenced table is...

I would think this would be the most straightforward way, although it may not be the most efficient way.  But it might work just fine for you if your data...

My personal opinion is that this has all the ear-markings of a classic invoice/invoice detail or work-order/work-order detail problem and should be handled as two individual tables rather than...

Or you could do this 😉

SELECT IDENTITY(int, 1, 1) AS id, A.*,B.* 
INTO ##C 
FROM A 
JOIN B ON  X = Y

No, there's no direct way to do that.

However, you could use a standard identity column for the second part of the key, and use ROW_NUMBER() to get a sequential number...