rajemessage (8/22/2012)

---

Thank u,

thing is i am keeping decimal (14,4) for from length and to length,

and it is not bridge it is road where paiting is going on.

will the query ...

venkidesaik (8/22/2012)

---

Thanks for all replying to my post

NOW IAM CLEARLY EXPLAING MY TASK,

SELECT DISTINCT ID FROM TABLE1

When i execute the above statement

The Output comes like this,

ID

----

100

200

300

400

500

so now i want to...

OK, let's setup some sample data first: -

SELECT bridgepid, fromlen, tolen

INTO #yourTable

FROM (VALUES(1,10,20),(2,18,21),(3,11,20),(4,40,50),(5,19,35)

)a(bridgepid, fromlen, tolen);

So, from that, we're expecting gaps of 1 -> 9...

GilaMonster (8/21/2012)

---

<soapbox>

Cursors are tools. Use them where they work, don't use them where they don't. Statements like 'you should never use a <whatever>' are just shortsighted

If we count while loops...

The closest I can come is this: -

SELECT s.DealerCode, s.ForecastID, fc.ForecastId, fc.Name

FROM [dbo].[Forecast] fc

OUTER APPLY (SELECT code

FROM...

J Livingston SQL (8/20/2012)

---

does this work for you?

;with cte as

(

SELECT ID, TranID, OriginCode AS scode, 1 as col

FROM #Test

union all

SELECT ID, tranID, DestinationCode AS scode, 2 as col

FROM ...

Requires more validation, I'd imagine. But something like this: -

SELECT

CAST(SUBSTRING(a.yourDate, 5, CHARINDEX('UTC',a.yourDate,1)-6) + SUBSTRING(a.yourDate,CHARINDEX('UTC',a.yourDate,1)+8,8000) AS DATETIME)

FROM (VALUES ('Thu Jun 14 15:07:06 UTC+0530 2012')) a(yourDate)

Really ugly recursive CTE solution (I'm hoping someone comes up with a better solution). The performance for this will scale terribly.

WITH CTE AS (

SELECT ID, TranID,

CASE WHEN a.N...

If we split your string based on the | deliminator, the result is as follows: -

ItemNumber Item

-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 ...

Not sure what your expected output is, but the function you want is "ceiling".

SELECT Amt, CEILING(Amt/10)*10 AS groupByTens

FROM #Range;

Returns: -

Amt ...

Abu Dina (8/16/2012)

---

Jeff, I'm in awe!

Respect my good man!

So we're rebuilding the string one character at a time and keeping only characters (LIKE '[a-z ''-]'). Maybe I'm just stupid but...

scott_lotus (8/16/2012)

---

Thank you for the posts. Enough information in this script to allow me to construct a solution adding a few joins (ps sorry about that lack of schema layout...

No problem. We're all here to learn and it's better to make a mistake on a forum than on production code, right? 😀

Adi Cohn-120898 (8/15/2012)

---

The UNIONs that some have posted are part of creating sample data so that they could test their solutions (really this is something you should have provided with...

CELKO (8/15/2012)

---

Now we have to fix the bad schema with DML. Look up the new CREATE SEQUENCE statement and use it. Here is an untested attempt:

WITH Corrected_Tests (device_id, test_value, device_test_seq)

AS

(SELECT...