Sounds like a situation where you might want to consider the ideas from Gail's article about multiple execution paths.

http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/%5B/url%5D

Or use any of the free fake name generators on the interwebz. Here is one I have used in the past, there are plenty of others.

http://homepage.net/name_generator/%5B/url%5D

Kapil, you have been around these forums long enough to know that what you posted as sample data is not the best way to post it. Can you turn that...

dwain.c (6/4/2013)

---

I like Luis's first choice (to avoid the triangular join). Here's another:

SELECT cStudentID, iSchoolYearCode

,IsRepeat=CASE WHEN n<>iSchoolYearCode THEN 1 ELSE 0 END

FROM (

SELECT *

...

Jeff Moden (6/4/2013)

---

Brandie Tarvin (6/4/2013)

---

My Recovery Plan

1) SCREAM really loudly so everyone knows there is a problem.

2) Run out of the building as fast as possible.

3) Update my resume.

4) Job...

One cat, four ways to skin it. You gotta love SSC.

You are quite welcome. Glad that worked for you.

Awesome job posting ddl and sample data!!! This is exactly the right way to post!!!

Here is one way to solve your challenge.

select * ,

case when x.iSchoolYearCode is null then 0...

prtk.raval (6/4/2013)

---

Ya most of them I got solution..but struglling for the last one, but I found this forum pretty helpful..

The members like you , lowel and others..are really helping a...

GrassHopper (6/4/2013)

---

I have a stored procedure that uses a paramater (which is the table name) I am running this sproc on. How can I run it on all my...

There is no way to delete them. Well, Steve can delete...

David.Lester (6/4/2013)

---

Thanks! I am using modulo to get to what I need. I just could not figure out how to in one set operation get the count to reset after...

In addition to Lowell's excellent explanation I would add that you need to be VERY careful about NOLOCK. Given your question I am assuming you are testing because you have...

David.Lester (6/4/2013)

---

I have been struggling with this all morning, and I think I am missing something obvious. What I need to do is take a typical date dim table...

Your issue is here.

cast(left('pehPErcontrol',8) as datetime),

You are trying to cast the string literal 'pehPErcontrol' instead of the column value to a datetime.

Try this instead.

cast(left(pehPErcontrol, 8) as datetime)

Also be very...