>Run the query with SQL Analyzer. It'll tell you where the slow parts are

I guess you're stuck window SQL2000. Query Analyzer does not exist anymore...
sure you meant to run it in SSMS with display of the query plan

Walter



>
>>Dear Experts,
>>I am using these codes.
>>The codes work fine, no issue.
>>
>>Is it possible to make the code shorten for speed?
>>
>>

>>SELECT distinct(LEFT(data.acc_code,4)) as acc_code,
>>sum(isnull(data.dr_amount,0))as dr_amount1,
>>sum(isnull(data.cr_amount,0))as  cr_amount1,
>>(case when sum(isnull(data.dr_amount,0)) + sum(isnull(data.cr_amount,0))>0  then sum(isnull(data.dr_amount,0)) + sum(isnull(data.cr_amount,0)) end) as dr_amount,
>>(case when sum(isnull(data.dr_amount,0)) + sum(isnull(data.cr_amount,0))<0  then sum(isnull(data.cr_amount,0)) + sum(isnull(data.dr_amount,0)) end) as cr_amount
>>   from (
>>SELECT max(acc_code)as acc_code,
>>(case when sum(open_dr) > sum(open_cr)  then sum(open_dr) - sum(open_cr) end) as dr_amount,
>>(case when sum(open_dr) < sum(open_cr)  then sum(open_dr) - sum(open_cr) end) as cr_amount
>> from master where  len(acc_code)=7 group by left(acc_code,4)
>>union all
>>SELECT max(acc_code)as acc_code, 
>>(case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
>>(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
>> from vouchers  where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
>>union all
>>SELECT max(acc_code)as acc_code, 
>>(case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
>>(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
>>  from cashsalp   where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
>>union all
>>SELECT max(acc_code)as acc_code,
>>(case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
>>(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
>>  from crsalp    where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
>>union all
>>SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
>>(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
>>  from brsalp  where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
>>union all
>>SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
>>(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
>>  from mrsalp    where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
>>union all
>>SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
>>(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
>>  from orsalp     where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
>>union all
>>SELECT max(acc_code)as acc_code,  (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
>>(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
>>  from frsalp  where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
>>union all
>>SELECT max(acc_code)as acc_code,  (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
>>(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
>>  from Srsalp    where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
>>union all
>>SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
>>(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
>>  from cashprp   where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4)
>>union all
>>SELECT max(acc_code)as acc_code, (case when sum(dr_amount) > sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as dr_amount,
>>(case when sum(dr_amount) < sum(cr_amount)  then sum(dr_amount) - sum(cr_amount) end) as cr_amount
>>  from crprp    where  date <=   '".$t_date1 ."' and  len(acc_code)=7 group by left(acc_code,4))
>> as data
>>where  len(acc_code)=7
>>group by left(acc_code,4)
>>
>>