Only property developers Steve, they nick all the best shooting land...

jwpquinn (9/15/2008)

---

Chris, Thank you for taking the time to review this issue.

Your SQL is correct for the EmpID 2 with only Alogs 4 through 8.

But when I uncomment the code...

Is this what you're looking for?

[font="Courier New"]SET DATEFORMAT mdy

DROP TABLE #OVERLAP

CREATE TABLE #OVERLAP

(

ALog INT,

ABegin DATETIME,

AEnd DATETIME,

EmpID CHAR(11),

APercent FLOAT,

PRIMARY KEY (ALog, ABegin, AEnd)

)

INSERT INTO #OVERLAP (ALog, ABegin, AEnd, EmpID, APercent)

SELECT 1,...

Happy Birthday Mr Jones for when the day comes.

Late November this year I'll be 50. About ten months ago I finally got around to taking up the English longbow after...

That pretty much identifies this as a homework question. Rather than solve this for you, here's an article which discusses more than one method...

Hi Mathew

What do you have in the way of pk / indexes in this table?

Cheers

ChrisM

You're welcome, Karthik. Thanks for the feedback.

Jeff Moden (9/10/2008)

---

Chris Morris (9/10/2008)

---

Hi Khushbu

You're welcome, thanks for the feedback.

The number of rows will probably matter if there are hundreds of thousands. If the query runs too slowly (and...

It works on SQL Server 2k, and I'm pretty sure it will work in SQL Server 2k5 too. This is a SQL Server 2k5 forum section.

You first 😉

If two tally tables makes it too confusing, then one will do at a push. The output of this is the position of the first @, the first and second...

I've just run this again, exactly...

There isn't a Function CHAR_LENGTH in my code. Did you copy and paste?

Or even better - one tally table for each delimiter 😛

[font="Courier New"]DECLARE @holdingAndWeightAndComments VARCHAR(60)

SET @holdingAndWeightAndComments  = '@MAGFX~51~Comment1@MALOX~52~Comment2@MAGFX~53~Comment3@'

SELECT n1.number,

   n2.number,

   CHARINDEX('~', @holdingAndWeightAndComments, n2.number+1),

   CHARINDEX('@', @holdingAndWeightAndComments, n1.number+1),

   SUBSTRING(@holdingAndWeightAndComments, n1.number+1, n2.number...

Like this?

[font="Courier New"]DECLARE @holdingAndWeightAndComments VARCHAR(60)

SELECT @holdingAndWeightAndComments  = '@MAGFX~50~Comment1@MALOX~50~Comment2@MAGFX~50~Comment3@'

DROP TABLE #hold

SELECT number, '9' AS [ID], --[ID] = IDENTITY(5),

   SUBSTRING(@holdingAndWeightAndComments,number+1,

          CHARINDEX('@',SUBSTRING(@holdingAndWeightAndComments,number+1,DATALENGTH(@holdingAndWeightAndComments)))-1) AS hold

INTO #hold

FROM Numbers -- Tally Table is a direct replacement of...