Hi niladri.primalink,

Using correlated subqueries (especially so many of them) is as bad as having an open bottle of rum on the front seat of your car or carrying a gun openly on a street.

Lack of enforcement from the police (probably because they can't read SQL very well) does not make it less criminal.

😀

I've tried to fix you query by moving subqueries to derived tables.

Some might suggest CTE - it's up to you which style to choose.

CTE might be the perfect choice for selecting "submission status" - if you can tell which one you need to select for each particular file_id.

I did not have table definitions and test data to test the script, so please do this part yourself - there might be some errors.

But anyway this might give you an idea how it should be done:

SELECT fb.{/*list pf columns here*/}

ISNULL(UL.USER_NAME,'')created_by,

ISNULL (ft.totcreditamount, FTE.totcreditamount) totcreditamount,

ISNULL (ft.totdebitamount, FTE.totdebitamount) totdebitamount,

FROM filebasic fb

LEFT JOIN userlogin UL ON UL.user_id=fb.createdby

LEFT JOIN (

SELECT fileid, MAX(status))status

FROM submission

WHERE file_type=1

GROUP BY fileid )sub_status ON fileid = file_id

LEFT JOIN (

SELECT fileid,

CONVERT (DECIMAL(13,2), SUM(CONVERT(FLOAT, CASE WHEN transactioncode IN ('99', 'Z4', 'Z5') THEN amount ELSE 0.00 END) )/100.00 ) totcreditamount,

CONVERT (DECIMAL(13,2), SUM(CONVERT(FLOAT, CASE WHEN transactioncode IN ('01', '17', '18', '19') THEN amount ELSE 0.00 END) )/100.00 ) totdebitamount

FROM filetransaction

WHERE iscontra=0 AND ISNUMERIC(amount + '.0e0') = 1

GROUP BY fileid

) ft ON ft.fileid=fb.file_id

LEFT JOIN (

SELECT FPSTemp.fileid,

CONVERT(DECIMAL(13,2), SUM(CASE WHEN mt.transaction_code IN ('99', 'Z4', 'Z5') THEN FPSTemp.amount ELSE 0 END )/100.00 ) totcreditamount,

CONVERT(DECIMAL(13,2), SUM(CASE WHEN mt.transaction_code IN ('01', '17', '18', '19') THEN FPSTemp.amount ELSE 0 END )/100.00 ) totdebitamount

FROM filetransaction_excel FPSTemp

INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code

WHERE ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL

GROUP BY FPSTemp.fileid

) FTE ON FTE.fileid= fb.file_id

WHERE company_id=@company_id

AND status=0 AND createdby = @INT_USERID

ORDER BY file_id DESC

I have doubts about use of table m_transactiontype in the last derived table.

It seems that "mt.transaction_code" must be replaced with "FPSTemp.transaction_code" and the INNER JOIN removed completely, as columns from the table are not used neither for SELECT not for WHERE clauses.