I believe your query will run as follows:

select s.stud_code, s.given_name,s.surname,s.year_grp,form_cls,s.dol

from Students s

inner join

(select stud_code, x=count(*)

from Students as c

where dol between @StartDate and @EndDate

and cmpy_code='01'

group by stud_code

having count(*)...

I think there's more issues here than just the syntax error:

1. DOL is a datetime (presumably), so why the test on DOL <= '5'?

2. Where is the ON following the...

You might try the hint from this article:

http://www.mssqltips.com/sqlservertip/1692/using-schema-binding-to-improve-sql-server-udf-performance

Jeff Moden (5/25/2012)

---

dwain.c (5/15/2012)

---

Jeff Moden (5/15/2012)

---

BWAAA-HAAA!!! Thanks for having a good sense of humor, Dwain! You've made my day! 🙂

Shifting gears back to the subject at hand... there's...

You have no idea what a time-saver you just posted for me. I needed to search out that very same gaps and islands article for something else I'm working...

R.P.Rozema (5/25/2012)

---

The difference is that Mark used a cross-tab, where you turned to a pivot. Cross tabs are most of the times faster than pivots because it lacks one operation...

Yet another method that works but is ugly and probably not recommended. I like my previous one better.

SELECT @xml1 = (SELECT * FROM @t FOR XML PATH(''))

-- Add in...

Jeff Moden (5/24/2012)

---

dwain.c (5/24/2012)

---

Jeff - I've been a fan of this splitter for a long time even though I've been a slow adopter, 🙂 recommending it highly around my office...

Jeff Moden (5/24/2012)

---

Not sure. You DO have a love for rCTEs and I'm thinking that you'll pull a rabbit...

Jeff Moden (5/24/2012)

---

dwain.c (5/24/2012)

---

Jeff Moden (10/24/2010)

---

For the simple task of sorting in the correct order, we'll use the "Hierarchical Path" method. To build it, we'll use a "Recursive CTE"....

Jeff - I've been a fan of this splitter for a long time even though I've been a slow adopter, 🙂 recommending it highly around my office at any opportunity.

I...

dwain.c (5/24/2012)

---

This suggestion gives me an interesting idea which I am clueless how to act on (haven't read the link yet but I will).

Would it be possible to store each...

Mark-101232 (5/24/2012)

---

R.P.Rozema (5/24/2012)

---

Of course the xml should be improved on for any practical implementation, but still this challenged me. Since this is hypothetical already and on top of that Mark...

Mark-101232 (5/24/2012)

---

Try this, I can't see a simpler way with the XML as is.

WITH CTE AS (

SELECT row.value('local-name(.)', 'VARCHAR(50)') AS name

,row.value('text()[1]', 'VARCHAR(50)') AS val

...

Another option:

DECLARE @t TABLE (Names VARCHAR(20))

INSERT INTO @t

SELECT 'sarat' UNION ALL SELECT 'Sarat' UNION ALL SELECT 'SaRaT' UNION ALL SELECT 'SARAT'

SELECT Names, CASE WHEN Names2=LOWER(Names2) THEN 1 ELSE 0 END...