This works quite nicely too:

SELECT

EmployeeID,

Email,

DomainLHS = SUBSTRING(Email, x.PosStart,x.PosEnd-x.PosStart),

DomainRHS = SUBSTRING(Email, y.PosStart,y.PosEnd-y.PosStart)

FROM @t

CROSS APPLY (

SELECT PosStart = 1+CHARINDEX('@',EMAIL,1),

PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,1)),0)

) x

CROSS APPLY (

SELECT PosStart = NULLIF(1+CHARINDEX('@',EMAIL,x.PosEnd),1),

PosEnd =...

Only yours, Sean 😉

No problem:

select distinct likp.VBELN as delivery,vbak.VBELN as sales_document,vbrp.VBELN as bill_no,

vbrk.FKDAT as invoice_date,AUDAT as sales_order_date,likp.ZZCND as delivery_date,likp.PODAT as POD_date,

submission_date as sub_date,vbak.ZZP01 as payment_rule,vbrk.ZTERM as payment_term,

(case

when vbak.ZZP01 = 'A' then DATEADD(DD,x.DateDelta,likp.ZZCND)

when vbak.ZZP01...

Don't forget the rCTE version:

;WITH OrderedData AS (SELECT *, rn = ROW_NUMBER() OVER (ORDER BY HotelId, RoomTypeId, DateKey DESC) FROM @booking),

Calculator AS (

SELECT rn, Id, HotelId, RoomTypeId, DateKey, FreeCount,

Availability =...

Change the final select to this:

SELECT

LevelNum,

MemberID,

sortorder,

ProductID, SaleClosedPrice,

CAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName,

[Commission%],

[Incentive%]

FROM Hierarchy

CROSS APPLY (SELECT [Commission%] = CASE WHEN LevelNum = 1...

SaleClosePrice and Commission% are both returned by me last query. I can't understand where you are having a problem - surely all you have to do is multiply the two?

DECLARE@MemberID...

sivag (6/25/2012)

---

the commission will be place depen upon closed price

from the closed price of the sale only the...

vinu512 (6/25/2012)

---

Hi guys,

Thanks for all the replies. I solved the problem. Well, technically... I didn't solve it, I took a Work around.

My Procedure had the following Query:

SELECT Distinct a.BookCode, b.MaterialType,...

sivag (6/25/2012)

---

in final u just mentioned

CAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName,

[Commission] = CASE WHEN LevelNum = 1 THEN 6 ELSE 2 END

i need...

Try this. I've left in several columns to show how the query works, and omitted the final aggregate of the results.

DECLARE@MemberID INTEGER = 1

;WITH

MemberAndParent AS (

SELECT m.MemberID, m.Name AS...

Please make sure your DDL/DML statements actually do work before posting them here.

A few questions:

How much commission does Siva get for selling ProductID = 1?

How much commission does Siva get...

vinu512 (6/25/2012)

---

O.K. no replies yet...that means I did something wrong.

I've simplified the requirement further.

The following query runs in 4 seconds on the above mention table having 40,000+rows bringing back the...

What's your context? If you're attempting to identify Star-join optimisation, then here's a...

dwain.c (6/22/2012)

---

...The only thing I don't like about your version, and I'm really splitting hairs here, is the hardcoding of the 4 on the LEFT. If you use this...

Nthuloane.Marotholi (6/22/2012)

---

I am not sure if this is something close to what you were looking for. But I got three rows as you wanted.. Here check this out

select distinct C.*...