select ..., max(CASE

WHEN m.Period_Abbreviation='EAM'

AND m.Att_Code IN ('VU','UA','A')

THEN 'U'

WHEN m.Period_Abbreviation='EAM'

AND m.Att_Code NOT IN ('VU','UA','A')

THEN 'E'

ELSE NULL

END),

...

FROM Ps_Attendance_Meeting m

right join Students S on s.id = m.Studentid

WHERE M.Presence_Status_Cd ='Absent'

AND M.Period_Abbreviation IN ('EAM','EPM')

GROUP BY...

FROM Ps_Attendance_Meeting Am

Students S

WHERE M.Presence_Status_Cd ='Absent'

AND M.Period_Abbreviation IN ('EAM','EPM')

In the parameters to patindex, you are creating a wildcard that looks for the first character of your delimited string, (or the last character) but what you want is to...

We will need sample data in order to tell you exactly what the problem is... It looks like your join is not doing what you expect it is.

Said another...

You might try just adding the columns GasSiteID and DroppedProd to the orderDetails.OrderStatus index.

I agree though, it looks like there's a lot of improvement that could be made in those...

Back to the question of the OP:

I have used this technique (or part of it), and as a solution to precisely the problems mentioned by the first couple of answerers...

I'm afraid you'll have to post more details in order for me to understand what you're looking for. For a start, your sample code does not match your sample...

Overall, R.P.R. got the overall answer correct, however I felt compelled to add a minor correction:

The two versions of the select max statement R.P.R. included are equivalent.

The way to write...

In defense of the people who haven't read the BOL, It can be exceedingly hard to figure out where to find something if you're not familiar with the organization (or...

If an account was active every other year for the last ten years instead of being fully active for the last 5 years, I believe your code would still...

I agree with John, this is a task better left to the display layer.

That said, in 2005 you can use the row_number to check if you have the first row...

Your problem is that <blank> is not the same as <null>

When you're executing from SSRS, I believe it's passing parameter values of '' instead of just not passing the parameter,...

Yes, please update the stats with a full scan, so that we can rule out bad stats as a cause for bad performance.

After you do that, post the updated code...

I think you want

Select ActName, PName

FROM SalesDetailsCTE

GROUP BY ActName, PName

HAVING COUNT(DISTINCT DATEPART(yyyy, SoDate)) = 5

(this says for each customer and product, show me the customers that have bought...

Post your table definition, including the index create statements.