You use the (longitude, latitude) to create/define a @Point. and then you use STDistance to calculate the distance between them.

STDistance (geography Data Type) - SQL Server | Microsoft Learn

Day late probably, but one thing to remember is that

<Aggregate> OVER (PARTITION BY <columnName>)

is the windowing version of

<Aggregate> FROM... GROUP BY <columnName>

and windowing functions do not reduce the number of...

Something like this?

SELECT  c.CustomerID, MAX(c.CallDate), STRING_AGG(c.Notes,' | ')
FROM (VALUES
(433,    '2018-11-06 11:08:12.793',  'Customer called'),
(433,     '2018-11-06 11:08:12.793',  'Customer left...

Dave,

Got some more data? Enough to weed out the false positives etc?

Like this?

SELECT c.CustomerID, c.CallDate, STRING_AGG(c.Notes,'|')
FROM (VALUES
(433,    '2018-11-06 11:08:12.793',  'Customer called'),
(433,     '2018-11-06 11:08:12.793',  'Customer left message')
) c(CustomerID, CallDate, Notes)
GROUP...

My code basically looks for the Causality or Relatedness columns and then counts back from there. That was the only sensible way of doing it. The weird 1/3 or whatever......

just wondering. There's not really a way to achieve this without dynamic SQL, right? I don't think so because you don't know beforehand how many of the repeating groups there...

Your sample desired output looks a lot like a PowerPivot in Excel. So I don't think trying to write a query for this is maybe what you want. (or is...

• This reply was modified 2 weeks, 2 days ago by pietlinden. Reason: more information/clarification
• This reply was modified 2 weeks, 2 days ago by pietlinden.

Okay, I sorted it out. It's not ideal yet... It should be a stored procedure and should receive the tablename containing the data as input. Just because it's usually called...

I was thinking of writing an iTVF to basically do the CROSS APPLY stuff for me. I'd do it tonight, but SQL Saturday is tomorrow... so I'll try it this...

The basic pattern is SymptomName, Causality#, Relatedness#

It's oncology stuff, so there are no other column names like that. Causality and Relatedness all have numeric suffixes from 1 to however many...

The basic pattern is SymptomName, Causality#, Relatedness#

It's oncology stuff, so there are no other column names like that. Causality and Relatedness all have numeric suffixes from 1 to however many...

Oh, I STRING_AGG WITHIN GROUP was a good catch. Thanks for that!

The bigger problem is that I have lots of tables with the same hideous design. That's what I was...

Funnily enough, I have that book too. Guess I should be brave and actually open it!

Kinda to answer my own question... I'd definitely recommend reading Star Schema: The Complete Reference before reading Data Warehouse Toolkit. Complete Reference starts from a much less theoretical place, so...

• This reply was modified 1 months, 2 weeks ago by pietlinden.