Since you do have the FK already established, you know that whatever is in the child table has to be in the parent table. So, how about something like:

SELECT PK=1,...

Steve Jones - SSC Editor (5/10/2011)

FYI, here are the results on my system (with my code throw in to test also):

--===== Test the RBAR function ========================================================================================

Total Duration in Seconds: 12

--===== Test the NON RBAR function...

Please read the articles linked to in my signature:

For better assistance in answering your questions

How to ask a question

Cross Tabs / Pivot Tables, Part 1

Cross Tabs / Pivot Tables, Part...

See you there, somewhere!

Like so?

;

WITH cte AS

(

SELECT Department,

Receipt,

StartDate,

EndDate,

...

I'd say with a combination of ROW_NUMBER(), DATEADD(), DATEDIFF() and possibly a self-referencing join. Maybe even RANK() and DENSE_RANK().

These links may help:

SQL Server Ranking Functions[/url]

Common Date Routines[/u]

You know, the people...

peterzeke (5/9/2011)

WayneS (5/9/2011)

I used to use an XML splitter all the time... that is, until Jeff showed me the light. There is a thread on here somewhere that discusses it,...

Plus a rewrite an expansion of Tally OH! based on new findings, plus your book, plus the varchar(max) splitter, plus the Running Totals...

Jeff Moden (5/8/2011)

peterzeke (5/6/2011)

DECLARE @STRING VARCHAR(8000)

SET @STRING = 'COMMUNITY GENERAL HOSPITAL'

DECLARE @TEXTXML XML

SELECT @textXML...

:blush: Thanks Pete - and I hope I can live up to that.

But, I am...

Look up sp_executesql in Books-Online

Try this:

DECLARE @STRING VARCHAR(8000);

SET @STRING = 'COMMUNITY GENERAL HOSPITAL';

SELECT (SELECT LEFT(Item,1)

FROM dbo.DelimitedSplit8K(@String, ' ')

...

(Raises hand)

dwg1011 (5/7/2011)

1) Extract the Codes values to a temp table indexed on the...