Here is another addition to the code, identifying the gaps more efficiently.

😎

USE TESTDB;

GO

;WITH BASE_DATA AS

(

SELECT

ST.STG_ID

...

There are few options and which is...

Not much of a change needed from last time, adding a second conditional in the last part to allow for missing HardDrink did the trick.;-)

😎

USE tempdb;

GO

CREATE TABLE #TEMP (UserName varchar(20),Drink...

ChrisM@Work (9/5/2014)

---

Eirikur Eiriksson (9/5/2014)

---

ChrisM@Work (9/5/2014)

---

Meatloaf (9/3/2014)

---

Hello,

I trying to figure out the logic to add a number to the end of an ID to create a series.

For example, I have an...

It is more a question of...

Quick question, can you post the two execution plans?

😎

More for fun, I'm tossing three more spanners in the works

😎

USE tempdb;

GO

/********************************************************************

Sample data

********************************************************************/

DECLARE @SAMPLE_DATA TABLE

(

SD_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

...

Here is a quick sample, should get you passed the hurdle.

😎

USE tempdb;

GO

CREATE TABLE #BOMTbl

(

ItemNo HIERARCHYID NOT NULL,

Lvl AS ItemNo.GetLevel() PERSISTED,

MatID VARCHAR(25)...

Looks like you have been "Granted" a...

ChrisM@Work (9/5/2014)

---

Meatloaf (9/3/2014)

---

Hello,

I trying to figure out the logic to add a number to the end of an ID to create a series.

For example, I have an EventID that may...

Quick thought, use aggregation and group by to eliminate the nulls (cross tab style)

😎

Phil Parkin (9/5/2014)

---

cstrati (9/5/2014)

---

Hi Phil

Thanks for your reply.

Unfortunately I've ran your script and I'm getting the same 4 results.

It doesn't appear that the columns that are emtpy display when...

Jeff Moden (9/5/2014)

---

Eirikur Eiriksson (9/4/2014)

---

Jeff Moden (9/4/2014)

---

I guess my question would be, if you recognize it as not being a good practice, why would you post the technique? We...

Jeff Moden (9/4/2014)

---

I guess my question would be, if you recognize it as not being a good practice, why would you post the technique? We just don't know what...

Here is a quick demonstration of the formula I posted earlier (EventId * 10^(number of digits) + sub-event-id)

I am by no means recognizing this as a good practice, this is...