ACU - GL tran with convert, joins and substrings

select gl.CompanyID,

gl.BranchID,

gl.Module,

CONVERT(VARCHAR(20), gl.TranDate, 103) AS TranDate,

LEFT(gl.FinPeriodID, 4) AS FinYear,

RIGHT(gl.FinPeriodID, 2) AS PeriodNbr,

SUBSTRING(gl.FinPeriodID, 1, 4) as FinYear1,

SUBSTRING(gl.FinPeriodID, 5, 2) as PeriodNbr1,

gl.RefNbr,

gl.BatchNbr,

gl.OrigBatchNbr,

gl.TranDesc,

gl.AccountID,

a.AccountCD as GLAccount,

a.Description as GLAccDescrip,

gl.SubID as SubAccount,

s.subcd,

s.Description as SubAccDescrip,

gl.CuryInfoID,

gl.CuryDebitAmt,

gl.CuryCreditAmt,

gl.DebitAmt,

gl.CreditAmt,

convert (varchar(20),c.CuryEffDate,103) as CuryDate,

c.RateReciprocal,

gl.Released,

gl.Posted,

gl.CreatedDateTime

from dbo.GLTran as gl

inner join dbo.Account as a

on a.accountid = gl.accountid

and gl.CompanyID = a.CompanyID

inner join dbo.sub as s

on s.companyid = gl.CompanyID

and gl.subid = s.SubID

left outer join (select Companyid, FromCuryid, ToCuryID, CuryRateType,CuryEffDate,CuryRate, RateReciprocal

from dbo.CurrencyRate

where fromcuryid = 'usd'

and tocuryid = 'tzs'

) as c

on gl.companyid = c.CompanyID

and gl.TranDate = c.CuryEffDate

where gl.companyid = 10

and gl.branchid = 27

order by gl.CompanyID,

gl.BranchID,

gl.CreatedDateTime;