pietlinden (12/22/2013)

---

Dwain,

First off, thanks for taking the time to crank that out... Of course, it'll take me a while to figure it out!!!

...

In your example, the ID column is analogous...

ndiro (12/21/2013)

---

Little better:

select A.[Product Id] as ProductId,

'-' as SubProductId,

(

select exp(sum(log(quantity))) from product_table B

where isprimary = 0

and patindex(ltrim(rtrim(A.[Product Id]))+'.%',B.[Product Id]) > 0

) as Value

from product_table A

where

A.isPrimary =...

You can try this:

CREATE TABLE #Data

(

ID INT

,N INT

,Flag INT

);

CREATE INDEX i1 ON #Data (ID, N);

CREATE...

I'm not sure I'm getting the full meaning of your business rules, this being a Sat morning and me having a hangover. However...

Looking at the code that I provided,...

Unfortunately I don't think you're going to be able to do it within the FUNCTION because of the error it is throwing but there is no reason you can't do...

Note that if you want Quantity on the line with the SubProduct to be zero, just change this line:

VALUES(a.[Product Id], SubProduct, NULL), (a.[Product Id], NULL, b.TotalQty)

To...

There are only 3 ways I can think of in SQL to get a running product. A recursive CTE, a CURSOR (gag me! :-P) and the Quirky Update (QU)....

Sean Lange (12/19/2013)

---

Net result is that the clunky conditional code runs a bit quicker. However, either approach is pretty acceptable as they are both quite fast even against a million...

Sean Lange (12/19/2013)

---

Everyone on this thread should take a look at the link in my signature for splitting strings. In there you will find a number of ways to split...

Jeff Moden (12/18/2013)

---

Using no more arcane knowledge than knowing that NULL can't be "not equal" to anything, the following code will do the same thing even on fixed length datatypes...

Jeff Moden (12/18/2013)

---

I take that back... that's actually an illegal form of update that will sometimes work and sometimes not. I'll explain more after I see the rest of...

A very easy way to do this is with a pattern-based string splitter:

WITH SampleData (TestStr) AS

(

SELECT '01-08-087-0101W5'

)

SELECT *, DesiredResult=CAST(Item AS INT)

FROM SampleData

CROSS APPLY dbo.PatternSplitCM(TestStr, '[0-9]')

WHERE [Matched]=1;

The...

I wouldn't hold my breath. The last time I saw snow was in Toronto a...

robert.l.craig (12/16/2013)

---

OK, so something like this?

WITH Table1 (ID, Val) AS

(

SELECT prefix, cost

UNION ALL SELECT prefix, cost

UNION ALL SELECT...

Are you perhaps confusing the sample data I set up in...