If you know that DATEADD(s, nDateTime, '1970-01-01 00:00:00') converts seconds since 1970-01-01 to a datetime,

why not simply swap the faulty expression in your WHERE clause for the the expression...

Jeff Moden (1/4/2014)

---

sayedkhalid99 (1/3/2014)

---

what change shall i bring in here so that if the serial don't have any decimal point say , it gives error in left function.

DECLARE @Result TABLE...

cs_source (1/3/2014)

---

okay, wow well thank you all for your queries, i learnt something new with each one, the last query outputs the data in the format i'm exactly looking for...

It's a DB2 error, Laurie 😀

DECLARE @Result TABLE (Serial nvarchar(10),Title varchar(20))

INSERT INTO @Result

SELECT '1.1','a' UNION ALL

SELECT '1.2.1','b' UNION ALL

SELECT '1.2.2','C' UNION ALL

SELECT '1.2.3.4','G' UNION ALL

SELECT '1.11','B' UNION ALL

SELECT '1.2.33.4.5','Extra row' UNION ALL

SELECT '2.3','B' UNION ALL

SELECT...

Jeff Moden (1/3/2014)

---

ChrisM@Work (1/3/2014)

---

Jeff Moden (1/2/2014)

---

You can also just create a view called (for example) ANewID that generates a GUID as a single column, single row result and simply join...

;WITH

[4] AS (SELECT n = 0 FROM (VALUES (0), (0), (0), (0)) d (n)),

[16] AS (SELECT n = 0 FROM [4] a, [4] b),

[256] AS (SELECT n = 0...

cs_source (1/3/2014)

---

Thank you dwain.c, i'm working through the query now to under stand it (pretty complicated 🙂

hi Chris.m below is the sample data as well as my expected output (sorry...

You're welcome, thanks for the feedback 🙂

cs_source (1/2/2014)

---

Hi salamex,

Thank you, this query is very similar to my first query and it works great if the car is in "service/shop" on the same day, the problem i...

Thanks.

-- try this

SELECT a.EID, a.RATING, a.AbsenceStartDate,

AbsenceEndDate = ISNULL(b.AbsenceEndDate, a.AbsenceEndDate)

FROM @COMP_RATINGS a

LEFT JOIN @COMP_RATINGS b

ON b.EID = a.EID

AND b.RATING = a.RATING

AND b.AbsenceStartDate = a.AbsenceEndDate

WHERE NOT EXISTS (SELECT 1...

What's the maximum number of elements you can have in a "serial"? Your sample data shows 3, can it be more? There are two good ways of splitting this into...

Source data

(769,'b+','2011-06-30','2011-09-30'),

Required output

5364914 b+ 2011-09-30 00:00:00.0000000 2011-12-31 00:00:00.0000000

If the required output is incorrect then please correct it.

Thanks.

Please explain the result for rating = 'b+'.

There's only one row with this value in the sample data, and it's completely different in the required output.

kbhanu15 (1/3/2014)

---

HI MITESH,

THANK YOU FOR YOUR HELP.

THE QUERY IS SATISFYING THE SAMPLE GIVEN DATA.

BUT IT IS NOT SOLVING MY ORIGINAL PROBLEM.

THE ORIGINAL DATA IN THE TABLE:

5364914b+2011-09-30 00:00:00.00000002011-12-31 00:00:00.0000000

5364914bb2011-06-30 00:00:00.00000002011-09-30 00:00:00.0000000

5364914bb2011-12-31...