Seriously? The new forum format is going to replace ending parens with smirky faces???

Something a little more along these lines...

CREATE TABLE dbo.Player (

PlayerID INT NOT NULL,

FirstName VARCHAR(20) NOT NULL,

LastName VARCHAR(20) NOT NULL

CONSTRAINT pk_Player PRIMARY KEY CLUSTERED (PlayerID)

);

CREATE TABLE dbo.Sport (

SportID INT NOT NULL,

SportName...

ben.brugman (1/5/2017)

---

First of all have a Happy Newyear, to all of you.

At home I only have access to a 2008R2 version of SQL-server. With the supplied code the message :...

Joe - Welcome to SSCI don't wan't to shatter your vision but both designs are pretty horrible and both violate the 1st normal form... The one you're trying to get...

Jeff Moden (12/23/2016)

---

Jason A. Long (12/23/2016)

---

@jeff - It looks to be the result of some sort of matching process, where A was matched to B and B was matched to...

@jeff - It looks to be the result of some sort of matching process, where A was matched to B and B was matched to A... Resulting in 50% redundant...

The code works of a simple idea...

If you order the rows by start date, the end of early rows controls the begin dates of all subsequent rows.

If the end date...

ben.brugman (12/23/2016)

---

Exactly what was asked, thank you.

I...

Here's what the solution looks like laid out graphically in Excel (note: I change a few dates to compact the ranges...)

This should be a simpler & faster solution as it only takes a single pass over the data...

-- Setup some test data...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL

DROP TABLE #TestData;

CREATE...

Jeff Moden (12/21/2016)

---

High Jason...

Yes. The old article is what I was talking about. Hadn't seen the new one. Unfortunately, I'm like a lot of humans in that...

Is this the article...

drew.allen (12/20/2016)

---

I started using a similar approach, but the ID isn't smeared and you would run into problems when there is more than one ID if you try to smear...

This is based on an old(ish) Itzik Ben-Gan solution... The Last non NULL Puzzle

IF OBJECT_ID('tempdb..#Data', 'U') IS NOT NULL

DROP TABLE #Data;

CREATE TABLE #Data (

ID VARCHAR(20),

SEQ INT,

Date DATETIME,

Action VARCHAR(20)

);

INSERT INTO...

You've got a much better chance of getting an answer if you were to post consumable data.

Please read Forum Etiquette: How to post data/code on a forum to get the...