This is certainly one of the more bizarre and exasperating threads on the go at the moment - suggestions for differences in performance have been posted and these seem perfectly plausible...

You are wrong in stating that functions cannot be used as defaults - getdate() works fine, for example.

What can be used - from BOL:

"Is an expression that contains only constant...

Given that you have so many tables, I suspect that the relationships will be pretty complex.  Without knowing the relationships between all the tables, it is difficult to get specific.

However,...

Try running SQL Profiler when you run the query - you should be able to get exact execution times that way.

Sticking with Northwind, is this roughly where you're heading?

select sum(t1.Count1), sum(t1.Count2) from

(select employeeid,

Count1 = (

case

 when Count(EmployeeID) < 100 then Count(EmployeeID)

 else 0

end

),

Count2 = (

case

 when Count(EmployeeID) >= 100...

OK, here's an example.  Try running against the Northwind database.

select employeeid,

Count1 = (

case

 when Count(EmployeeID) < 100 then Count(EmployeeID)

 else 0

end

),

Count2 = (

case

 when Count(EmployeeID) >= 100 then Count(EmployeeID)

 else 0

end

)

from...

OK - I see now what you're trying to do.

One idea I've had, which I've not tested, is for you to have a single subquery containing two counts

select studentid, count1,...

If you put the COUNTs in the subqueries, I think that this will work:

SELECT t1.TotalRows, t2.TotalRows FROM

(SELECT count(StudentID) TotalRows

FROM Attendance

WHERE ClassId=1 and Month(Date)=11 and status=0

GROUP BY studentID

HAVING...

Any time, glad to help.

You appear to have ditched my CASE statement, for some reason!  That's the way to get zero returned if B is 'no'.

Also, you should prefix your date column selections (tab1.date,...

I actually typed in your data to test my query, so it should be right

Please post the actual SQL that is generating the...

This should do it - uses a co-related subquery:

select t.b, t.d, t.c,

Calc = case t.b

when 'yes' then t.c / (select cast(sum(t2.c) as decimal(19)) from A t2 where t2.d =...

Have you tried a full reboot of the server?

Has anything been installed on the server recently?

Assuming that's all you want ...

SELECT MIN([DATE]) FROM STOCK_MOVEMENT_HISTORY

If you actually want more than that, please post an example of your required results.

Check this article & see whether it's relevant:

http://support.microsoft.com/kb/885442