Hi friends,
I am doing a project which has calculations. In my front end, I have to display the result in a grid. So I thought I ll complete the steps in SQL and bind it with the grid.
I have two SP's which are calculations and I have to merge both of them in a single SP using for loop.
The SP's I have are:
for Onsite:
Alter procedure usp_PyramidCalculationOnsite
as
begin
select
A.ServiceID,Service,
(FTECnt*C.PT/100*D.Onsite/100) as PT,
Round((FTECnt*C.PAT/100*D.Onsite/100),1) as PAT,
Round((FTECnt*C.P/100*D.Onsite/100),1) as P,
Round((FTECnt*C.PA/100*D.Onsite/100),1) as PA,
Round((FTECnt*C.A/100*D.Onsite/100),1) as A,
Round((FTECnt*C.SA/100*D.Onsite/100),1) as SA,
Round((FTECnt*C.M/100*D.Onsite/100),1) as M,
Round((FTECnt*[SM+]/100*D.Onsite/100),1) as SM
from
(select
A.ServiceID,B.Service,A.IsScope,
10 as FTECnt
from
tblEstimateServices A,
tblServices B
where
IsScope > 0 and
EstimateID=827 and
VersionNo=1 and
A.ServiceID = B.ServiceID)A,tblDesignationReference C,tblOnsiteOffshoreRatio D
where
A.ServiceID = C.ServiceID and
Location='Onsite' and
ReferenceModel='Aggressive' and
A.ServiceID = D.ServiceID and
C.ServiceID = D.ServiceID
End
and for offshore its :
Alter procedure usp_PyramidCalculationOffshore
as
begin
select
A.ServiceID,Service,
(FTECnt*C.PT/100*D.Offshore/100) as PT,
Round((FTECnt*C.PAT/100*D.Offshore/100),1) as PAT,
Round((FTECnt*C.P/100*D.Offshore/100),1) as P,
Round((FTECnt*C.PA/100*D.Offshore/100),1) as PA,
Round((FTECnt*C.A/100*D.Offshore/100),1) as A,
Round((FTECnt*C.SA/100*D.Offshore/100),1) as SA,
Round((FTECnt*C.M/100*D.Offshore/100),1) as M,
Round((FTECnt*[SM+]/100*D.Offshore/100),1) as SM
from
(select
A.ServiceID,B.Service,A.IsScope,
10 as FTECnt
from
tblEstimateServices A,
tblServices B
where
IsScope > 0 and
EstimateID=827 and
VersionNo=1 and
A.ServiceID = B.ServiceID)A,tblDesignationReference C,tblOnsiteOffshoreRatio D
where
A.ServiceID = C.ServiceID and
Location='Offshore' and
ReferenceModel='Aggressive' and
A.ServiceID = D.ServiceID and
C.ServiceID = D.ServiceID
end
In my front end, I have to show them like this,
ServiceID FTE Offshore Onsite
P PAT PA PT A SA M SM P PAT PA PT A SA M SM
I have to show the result under this grid.
Please help me on how to proceed further with the for loop query.
Thanks in advance.