dwain.c (9/13/2012)

---

And here's one specially built for ChrisM@Work, who loves cascading CROSS APPLYs:

;WITH SourceNums AS (

SELECT num FROM (VALUES (1),(2),(5),(7),(8),(9)) a (num)

), MyNums AS (

SELECT num=a.num + 10*b.num + 100*c.num...

Each option in the CASE construct, including the ELSE, must have the same datatype - otherwise...

BeginnerBug (9/13/2012)

---

Yes.. But First name or MiddleNamee or Lastname would differ... I simulated my actual table into this one.. Thanks brown.. i will try to create clustered index on empno...

BeginnerBug (9/13/2012)

---

Thanks Brown!!!.. but i cant make column empno as autoincreament as per requirement....but i can additionally add one more column and make it auto increment. will that avoid table...

Interesting point - if you can't decrypt it, is there any argument for keeping it?

Try this:

declare @var int =2

select ClientInternalID, ClientName, NULL ParentInternalID

from TB_Client cl

CROSS APPLY (

SELECT OrderBy = CASE @var

WHEN 1 THEN CAST(ClientInternalID AS VARCHAR(10))

WHEN 2 THEN CAST(ClientName AS VARCHAR(10))

WHEN 4 THEN...

ScottPletcher (9/12/2012)

---

There is NO case where I would ever want to use a GUID as a clustered key column.

It would have to be THE ONLY WAY POSSIBLE for me to...

Lynn Pettis (9/13/2012)

---

ChrisM@Work (9/13/2012)

---

STUFF() is perfect for this:

SELECT

Data,

ChangedData = STUFF(Data,50,25,SPACE(25))

FROM ( -- note: '[' is position 49 and ']' is position 75

SELECT Data = 'ZY0503400000234567891234567890110000000000088975[_5668550000000XXXXXXXXXXX]'

) d

WHERE LEFT(Data,8)...

m.henly (9/13/2012)

---

Thanks for your time, will get those on and have a play around with it.

Cheers,

...

STUFF() is perfect for this:

SELECT

Data,

ChangedData = STUFF(Data,50,25,SPACE(25))

FROM ( -- note: '[' is position 49 and ']' is position 75

SELECT Data = 'ZY0503400000234567891234567890110000000000088975[_5668550000000XXXXXXXXXXX]'

) d

WHERE LEFT(Data,8) = 'ZY050340'

dwain.c (9/13/2012)

---

And the referee throws down...

Eugene Elutin (9/13/2012)

---

Man, you do really need to read the previous posts.

It's only going to work if you use dynamic sql.

Please note, if you run the following type of...

UPDATE tr SET

FV_Flag = 'N',

EX16 = ao.EX16

FROM Results tr

CROSS APPLY (

SELECT TOP 1

ao.EX16,

ao.Date_Effective

FROM AddOns ao

WHERE ao.Customer = tr.CustBookId

AND ao.FmNo =...

dwain.c (9/11/2012)

---

ChrisM@Work (9/11/2012)

---

Using the sample data provided, a simple calculation rCTE works fine with both positive and negative numbers.

DECLARE @AmountToAllocate INT = 21

;WITH Calculator AS (

SELECT

BucketID, TotalSize, Amount,

AmountLeftToAllocate...

SELECT

Id, A, B, C,

MinVal

FROM #MinAmt

CROSS APPLY (

SELECT MIN(a)

FROM (VALUES (A),(B),(C) ) v (a)

WHERE a > 0

) x (MinVal)

😉