Monday, 30 December 2013

Use Column alias in Where Condition

select row_number() over (order by (select(1))) as SNo,* from (select SM.ADMNNO,           
               convert(varchar(13),SM.ADMNDATE,103) as ADMNDATE,SM.STDFIRSTNAME,             
               Convert(varchar(13),SM.DOB,103) as DOB, SM.F_MOBILE,SM.F_NAME,             
               SM.PRESENT_CLASS,SM.PRESENT_SEC,SM.ACTIVE,SM.FranchiseCode,SM.StudentRegNo,           
               (sum(SM.FeeOpeningBal)+sum(SL.Debit)-sum(SL.Credit)) AS Balance ,           
               convert(varchar(15),(sum(SM.FeeOpeningBal)+sum(SL.Debit)-sum(SL.Credit))) as BalanceS           
               from STUDENTMASTER SM Inner Join StudentLedger SL           
               on SM.ADMNNO=SL.ADMNNO AND SM.FranchiseCode=SL.FranchiseCode           
               where SM.FranchiseCode='DL') DerivedTable where Balance<>0

No comments:

Post a Comment