Be brave. try it. LAG() looks at the previous record in a window (defined by the PARTITION clause) and LEAD() looks at the "next" record.

Use a numeric index on the possible values and then use NEW_ID() to generate a random number?

Huh? This HTML is messed up. Standard format for an HTML table is

<table>
 <tr>
     <td>R1C1</td>
     <td>R1C2</td>
     <td>R1C3</td>
</tr>
<tr>
     <td>R2C1</td>
  ...

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

"Pfffft! *he* wrote it??? Not reading that!!"

LOL

If a driver can NEVER drive more than one car, and you don't care about the car if it doesn't belong to that driver, then you could incorporate the Car...

"[Drivers] can technically "own" more than one vehicle personally, but they only drive one for our company really."

I would probably have one table for Drivers and one for Vehicles, and...

wait... this design is off.

Taking a step back... Can you explain the relationship between Person and Vehicle? Can one person own more than one vehicle? Can one vehicle be owned...

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

I totally missed that full outer join. =(

So you mean you're trying to UNION the two split results? Like this? the short version of union is

SELECT  f1, f2, f3
FROM t1
UNION ALL
SELECT f4,f5,f6
FROM t2;

So your...

Use a better splitter function?  I used Jeff Moden's DelimitedSplit8K which is here ...

Setup (this is the part you were supposed to do):

use tempdb;
go
CREATE TABLE #BadData...

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

are you querying the master or querying a replica?

Ouch... had to reformat all that so it was easier to read...

IF OBJECT_ID('product') IS NOT NULL
DROP TABLE [dbo].[product]

CREATE TABLE [dbo].[product](
[productid] [VARCHAR](20) NULL,
[productEventID] [VARCHAR](50) NULL,
[productTransID] [VARCHAR](50) NULL,
[productEffectiveDate] [DATETIME]...

Post the definition of your Calendar table.  Are you familiar with dimension tables in data warehouse designs? Normally a Calendar dimension will be something like this:

CREATE TABLE...

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

Re-reading this... are you trying to do a running total, or just a normal total? If you want a running total, I'd use a windowing function.

SUM([numeric column]) OVER (PARTITION BY...

WITH (nolock) isn't a magic "go faster" button. It's allowing dirty reads. If you want it to read faster, index the columns in the joins and the filters.

We don't even...

• This reply was modified 5 years, 11 months ago by pietlinden. Reason: additional info