>Hi
>Tried, it taken 1 mins ++ to execute. But, can be faster?

I think so,
but hard to say without having in hand the tables

try SQL SERVER Optimization tool.

try to optimize this

SELECT COUNT(*) FROM ...

if it is slow then it is hard to make things better
without changing the logic of the tables.