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 6 years, 1 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 6 years, 1 months ago by pietlinden.
• This reply was modified 6 years, 1 months ago by pietlinden.
• This reply was modified 6 years, 1 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 6 years, 1 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 6 years, 1 months ago by pietlinden. Reason: additional info

Like this?

use tempdb;
go

CREATE TABLE SomeData (
 VendorID INT NOT NULL,
 PartID INT NOT NULL,
 PODate DATE NOT NULL);
GO

INSERT INTO SomeData VALUES(101,10,'1/1/2020'),(201,10,'2/10/2020');
-- setup is done, now answer the question...

SELECT *
FROM (
  SELECT...

NVARCHAR() fields are text.

How about...

"I have this table...

CREATE TABLE SomeData (…);

which contains data like this...

INSERT INTO SomeData(f1,f2,f3...) VALUES (1, 2,3),(2,3,4)…;"

and maybe when I run this query <paste statement here>, I get this result...

Use ROW_NUMBER() with PARTITION BY?

How about some sample data and expected results?