help select query.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help select query..

Author

Topic

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-30 : 02:11:46

hi all....
i have this query...

SELECT
SalMst.UserId,
ULogin.UserName,
StaMonAtten.MonthId,
StaMonAtten.DaysInMonth,
StaMonAtten.Present,
StaMonAtten.Absent,
ActualBasic=SalMst.BASIC ,
CalcuateBasic=round(((SalMst.BASIC*StaMonAtten.Present)/StaMonAtten.DaysInMonth),2,0),--as 'CalcuateBasic',
SalMst.HRA,
SalMst.Child_Edu,
SalMst.Other_Allowances,
SalMst.Conveyance_Allowances,
round(((SalMst.BASIC*StaMonAtten.Present)/StaMonAtten.DaysInMonth),2,0) + isnull(SalMst.Child_Edu,0) + SalMst.HRA + isnull(SalMst.Other_Allowances,0)+ isnull(Conveyance_Allowances,0) as 'GrossTotal',
SalMst.PFNumber,
SalMst.ESINumber,
SalMst.TDS,
SalMst.PF

--Select TotalAdvance from Staff_AdvanceDetails where Advance

FROM STAFF_SALARYMASTER AS SalMst
RIGHT JOIN Staff_Salary_UserLink as SalULink on SalMst.SMId=SalULink.SMId
Left Join User_Login as ULogin on SalMst.UserId=ULogin.UserId
--Left Join Staff_AdvanceDetails as StaAdvDetails on SalMst.UserId=StaAdvDetails.UserId
Left Join Staff_MonthAttendence as StaMonAtten on StaMonAtten.UserId=SalMst.UserId
where StaMonAtten.MonthId=12 and YearId=2008

Can i have something so that i can make use of 'CalcuateBasic' column further in my query...

i.e.
how to
select calculatebasic+SalMst.HRA+SalMst.Child_Edu+SalMst.Other_Allowances+SalMst.Conveyance_Allowances

I did went through CTE and derived tables but still have some confusion...

Please help..

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-31 : 09:29:35

Using derived table:

Select * from (

SELECT
SalMst.UserId,
ULogin.UserName,
StaMonAtten.MonthId,
StaMonAtten.DaysInMonth,
StaMonAtten.Present,
StaMonAtten.Absent,
ActualBasic=SalMst.BASIC ,
CalcuateBasic=round(((SalMst.BASIC*StaMonAtten.Present)/StaMonAtten.DaysInMonth),2,0),--as 'CalcuateBasic',
SalMst.HRA,
SalMst.Child_Edu,
SalMst.Other_Allowances,
SalMst.Conveyance_Allowances,
round(((SalMst.BASIC*StaMonAtten.Present)/StaMonAtten.DaysInMonth),2,0) + isnull(SalMst.Child_Edu,0) + SalMst.HRA + isnull(SalMst.Other_Allowances,0)+ isnull(Conveyance_Allowances,0) as 'GrossTotal',
SalMst.PFNumber,
SalMst.ESINumber,
SalMst.TDS,
SalMst.PF

--Select TotalAdvance from Staff_AdvanceDetails where Advance

FROM STAFF_SALARYMASTER AS SalMst
RIGHT JOIN Staff_Salary_UserLink as SalULink on SalMst.SMId=SalULink.SMId
Left Join User_Login as ULogin on SalMst.UserId=ULogin.UserId
--Left Join Staff_AdvanceDetails as StaAdvDetails on SalMst.UserId=StaAdvDetails.UserId
Left Join Staff_MonthAttendence as StaMonAtten on StaMonAtten.UserId=SalMst.UserId
where StaMonAtten.MonthId=12 and YearId=2008

) as D
--can access calcualatebasic as any normal column, e.g.
where CalcuateBasic = '10'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources