Select col1,col2,
(SELECT USERNAME FROM USER_HD WHERE USERID = A.LOGINAUTHUSERID) Verified_By,       
 (SELECT USERNAME FROM USER_HD WHERE USERID = A.CLEXECUSERID) Exec_By
FROM CLLOG A 

SELECT <columns>
      ,UH1.USERNAME  verified_by
      ,UH2.USERNAME  exec_by
FROM CLLOG   A
    ,USER_HD UH1
    ,USER_HD UH2
WHERE UH1.USERID = A.LOGINAUTHUSERID
AND   UH2.USERID = A.CLEXECUSERID

SELECT P.CityCode City_Code,
	CASE B.CALLTYPE WHEN 'CSH'
	THEN '-P'
	ELSE B.CALLTYPE END Call_Type, 
	A.CLNATURE B_R, 
	A.CLCALLNO Call_Id, 
	A.CLEXPCLTM TAT_Time, P.CustBrCode Branch_Code,  
	CASE B.CALLTYPE WHEN 'ATMREPL' THEN A.CLATMID ELSE ABC.CUSTCUSTNAME END Client_Name,  
	CASE B.CALLTYPE WHEN 'ATMREPL' THEN ISNULL(PQR.LOCDESC,ABC.LOCDESC) ELSE ABC.LOCDESC END_Area,  
	ISNULL(A.CLAMT,0) Amount_INR,  
	CASE 	WHEN A.CLCALLSTATUS = 'OP' THEN 0 
		WHEN A.CLCALLSTATUS = 'AS' THEN 0 
		ELSE  
		CASE B.CALLTYPE+A.CLCALLACTION 
			WHEN 'CSHPKP' THEN (ISNULL(A.CLAMT,0) + ISNULL(A.CLDIFFAMT,0)) 
			WHEN 'CSHCHQPKP' THEN (ISNULL(A.CLAMT,0) + ISNULL(A.CLDIFFAMT,0)) 
			WHEN 'CSHDLY' THEN (ISNULL(A.CLAMT,0) - ISNULL(A.CLDIFFAMT,0))  
			WHEN 'ATMREPLATM' THEN (ISNULL(A.CLAMT,0) + ISNULL(A.CLDIFFAMT,0)) 
			WHEN 'CSHWBNKPKP' THEN (ISNULL(A.CLAMT,0) + ISNULL(A.CLDIFFAMT,0)) 
			WHEN 'CSHDBNKDLY' THEN (ISNULL(A.CLAMT,0) - ISNULL(A.CLDIFFAMT,0)) 
			ELSE 0 
			END  
		END Actual_Amt,  
	ISNULL(A.CLDIFFAMT,0) Diff_Amt,  X.RtCode Route_Code, A.REQSLNO Ack_No, 
	CASE A.CLCALLSTATUS 	WHEN 'OP' THEN 'Open' 
				WHEN 'AS' THEN 'Assigned' 
				WHEN 'AT'THEN 'Attended' 
				WHEN 'SK' THEN 'Skipped'  
				WHEN 'CN' THEN 'Cancelled' 
				WHEN 'CL' THEN 'Closed' 
				END Call_Status, 
	A.LOGINAUTHUSERID Auth1, 
	B.CALLTYPE Call Type_Code, 
	CONVERT(CHAR(10),A.CLGENDATE ,'+ @SQLDateFormat +') Gen_Date,  
	A.CLCALLSTATUS Status, 
	A.CLCALLACTION Call_Action, 
	ABC.CUSTCUSTOMERCODE Client_Code, 
	A.CLBNKLGNO Bank_Login, 
	A.CLBNKFXLGTM Fax_Time, 
	A.CLNOOFCHQS No_Of_Chqs, 
	A.CLCHQNO Chq No,A.CLDDTCNO DDTC_No, 
	A.CLDDTCAMT DDTC_Amt,  
	(SELECT USERNAME FROM USER_HD WHERE USER_HD.USERID = A.LOGINAUTHUSERID) Verified_By, 
	(SELECT USERNAME FROM USER_HD WHERE USER_HD.USERID = A.CLEXECUSERID) Exec_By, 
	A.CLGENTIME Gen_Tm,P.CUSTCODE Bank_Code, 
	A.CLATMID ATM_ID, PQR.LOCDESC ATM_Area, 
	A.CLCALLSTATUS Status_Of_Call, 
	B.CALLTYPE Type_of_Call  , 
	CASE WHEN XYZ.vn_cnt > 0 THEN 'True' 
		else 'False' 
	END MultiAcs, 
	CASE WHEN XYZ.vn_cnt > 0 THEN 'True' 
	else 'False' 
	END MultiAcYN  , 
	A.clOFFCD OFF_Cd FROM CLLOG A  
	INNER JOIN USER_CLTYPE B ON (A.CLCALLTYPE=B.CALLTYPE ) 
	INNER JOIN CALLMASTER C ON (B.CALLNAME=C.CALLNAME AND A.CLCALLTYPE=C.CALLTYPE AND A.CLCALLACTION = C.CALLACTION)  
	LEFT OUTER JOIN (
			SELECT T.ATMID,T.ATMLOCCD,ATMCOMPCD,ATMOFFCD,LOCDESC 
			FROM MATM T
			INNER JOIN MLOCATION K ON T.ATMLOCCD=K.LOCCODE AND T.ATMOFFCD=K.OFFCODE AND T.ATMCITY=K.CITYCODE 
			)PQR  ON ( A.CLATMID=PQR.ATMID AND A.COMPCODE=PQR.ATMCOMPCD AND A.CLOFFCD=PQR.ATMOFFCD) 
	LEFT OUTER JOIN (
			SELECT CUSTCODE,CUSTBRCODE,CUSTCUSTOMERCODE,COMPCODE,E.OFFCODE,E.CITYCODE,E.LOCCODE,LOCDESC,
			CUSTCUSTNAME,CALLTYPECODE 
			FROM MCUSTCUSTOMER E
			INNER JOIN MLOCATION G ON E.LOCCODE =G.LOCCODE AND E.OFFCODE=G.OFFCODE AND E.CITYCODE=G.CITYCODE 
			)ABC ON ( A.CLCUSTCD=  ABC.CUSTCODE AND  A.CLCUSTBRCD=ABC.CUSTBRCODE AND A.COMPCODE=ABC.COMPCODE AND A.CLOFFCD=ABC.OFFCODE  AND A.CLCUSTCUSTCD=ABC.CUSTCUSTOMERCODE)  
	INNER JOIN (
			SELECT F.CITYCODE, F.CITYDESC,CUSTBRNAME,CUSTCODE,CUSTBRCODE,COMPCODE,OFFCODE 
			FROM MCUSTOMERBRANCH D 
			INNER JOIN MCITY F ON D.CITYCODE=F.CITYCODE
		)P  ON (A.CLCUSTCD=P.CUSTCODE AND A.CLCUSTBRCD=P.CUSTBRCODE AND A.COMPCODE=P.COMPCODE AND A.CLOFFCD=P.OFFCODE)
	LEFT OUTER JOIN (
			SELECT COUNT(*) VN_CNT, CUSTCUSTOMERCODE FROM MCUSTCUSTACCOUNT 
			GROUP BY CUSTCUSTOMERCODE 
			) XYZ on XYZ.custcustomercode =ABC.CUSTCUSTOMERCODE 
	LEFT OUTER JOIN (
			SELECT R.RTCODE,Q.FIX,Q.CLCALLNO,Q.CLACTCD,Q.CLCUSTCD,Q.CLCUSTBRCD,Q.CLOFFCD,Q.COMPCODE,
			Q.CLGENDATE,Q.CLCUSTCUSTCD FROM CLEXEC Q
			INNER JOIN MROUTE R ON Q.RTCODE=R.RTCODE AND Q.COMPCODE=R.COMPCODE AND Q.CLOFFCD=R.OFFCODE
			)X ON (A.CLCALLNO=X.CLCALLNO AND A.CLACTCD=X.CLACTCD  AND A.CLCUSTCD=X.CLCUSTCD 
			AND A.CLCUSTBRCD=X.CLCUSTBRCD AND  A.CLOFFCD=X.CLOFFCD 
			AND A.COMPCODE=X.COMPCODE AND A.CLGENDATE=X.CLGENDATE 
			AND (A.CLCUSTCUSTCD=X.CLCUSTCUSTCD OR A.CLCUSTCUSTCD IS NULL)
	) 
	WHERE A.COMPCODE=CompCode 
	AND B.USERID=UserID  
	AND P.CITYCODE IN (CityCode) 
	AND B.CALLNAME IN (CallTypeCode) AND A.CLCUSTBRCD IN (BankCode)
	AND A.CLNATURE IN(Frequency) AND A.CLCALLSTATUS IN (CallStatus) 
	AND RouteCode IS NOT NULL 
	AND A.CLCALLSTATUS <> 'CL' 
	AND A.ClOffCd IN (ClOffCd)
	AND A.ClCustCd IN (ClCustCd)
	ORDER BY CLACTDATE, B.CALLNAME, A.CLCALLNO

(SELECT USERNAME FROM USER_HD WHERE USER_HD.USERID = A.LOGINAUTHUSERID) Verified_By, 
	(SELECT USERNAME FROM USER_HD WHERE USER_HD.USERID = A.CLEXECUSERID) Exec_By,

A.clOFFCD [OFF Cd] FROM CLLOG A 
 LEFT OUTER JOIN USER_HD UH1 ON UH1.USERID = A.LOGINAUTHUSERID
  LEFT OUTER JOIN USER_HD UH2 ON UH2.USERID = A.CLEXECUSERID

LEFT OUTER JOIN (
			SELECT R.RTCODE,Q.FIX,Q.CLCALLNO,Q.CLACTCD,Q.CLCUSTCD,Q.CLCUSTBRCD,Q.CLOFFCD,Q.COMPCODE,Q.CLGENDATE,Q.CLCUSTCUSTCD FROM CLEXEC Q
INNER JOIN MROUTE R ON Q.RTCODE=R.RTCODE AND Q.COMPCODE=R.COMPCODE AND Q.CLOFFCD=R.OFFCODE
			)X

SELECT Q.FIX,Q.CLCALLNO,Q.CLACTCD,Q.CLCUSTCD,Q.CLCUSTBRCD,
Q.CLOFFCD,Q.COMPCODE,Q.CLGENDATE,Q.CLCUSTCUSTCD FROM CLEXEC Q