如何使用“for循环”在sp [英] how to use the "for loop" in sp
问题描述
朋友们,
我正在做一个有计算的项目。在我的前端,我必须在网格中显示结果。所以我想我将完成SQL中的步骤并将其与网格绑定。
我有两个SP'是计算,我必须使用for循环将它们合并在一个SP中。
我有的SP是:
for Onsite:
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,D.Offshore,
(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,D.Offshore,
(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
在我的前端,我必须向他们展示l ike this,
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
我必须在此网格下显示结果。
请帮助我进一步了解for循环查询。
提前致谢。
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.
推荐答案
嗨Anusha,
根据您的要求,我不认为您需要使用循环。
我们也应尽量减少使用循环。
Hi Anusha,
As per your requirement, i don''t think that you need to use loop.
Also we should try to minimize use of loop.
CREATE PROCEDURE PROC_PYRAMIDCALCULATION
As
begin
SELECT
A.SERVICEID,SERVICE,--,FTECNT,C.PT,D.OFFSHORE,
CASE WHEN LOCATION ='OFFSHORE' THEN (FTECNT*C.PT/100*D.OFFSHORE/100)
WHEN LOCATION = 'ONSITE' THEN (FTECNT*C.PT/100*D.ONSITE/100) END AS PT,
CASE WHEN LOCATION = 'OFFSHORE' THEN ROUND((FTECNT*C.PAT/100*D.OFFSHORE/100),1)
WHEN LOCATION ='ONSITE' THEN ROUND((FTECNT*C.PAT/100*D.ONSITE/100),1) END AS PAT,
CASE WHEN LOCATION = 'OFFSHORE' THEN ROUND((FTECNT*C.P/100*D.OFFSHORE/100),1)
WHEN LOCATION = 'ONSITE' THEN ROUND((FTECNT*C.P/100*D.ONSITE/100),1) END P,
CASE WHEN LOCATION = 'OFFSHORE' THEN ROUND((FTECNT*C.PA/100*D.OFFSHORE/100),1)
WHEN LOCATION = 'ONSITE' THEN ROUND((FTECNT*C.PA/100*D.ONSITE/100),1) END PA,
CASE WHEN LOCATION = 'OFFSHORE' THEN ROUND((FTECNT*C.A/100*D.OFFSHORE/100),1)
WHEN LOCATION = 'ONSITE' THEN ROUND((FTECNT*C.A/100*D.ONSITE/100),1) END A,
CASE WHEN LOCATION = 'OFFSHORE' THEN ROUND((FTECNT*C.SA/100*D.OFFSHORE/100),1)
WHEN LOCATION = 'ONSITE' THEN ROUND((FTECNT*C.SA/100*D.ONSITE/100),1) END SA,
CASE WHEN LOCATION = 'OFFSHORE' THEN ROUND((FTECNT*C.M/100*D.OFFSHORE/100),1)
WHEN LOCATION = 'ONSITE' THEN ROUND((FTECNT*C.M/100*D.ONSITE/100),1) END M,
CASE WHEN LOCATION = 'OFFSHORE' THEN ROUND((FTECNT*[SM+]/100*D.OFFSHORE/100),1)
WHEN LOCATION = 'ONSITE' THEN ROUND((FTECNT*[SM+]/100*D.ONSITE/100),1) END SM
FROM
(SELECT
A.SERVICEID,B.SERVICE,A.ISSCOPE,
10 AS FTECNT
FROM
TBLESTIMATESERVICES A
JOIN TBLSERVICES B ON A.SERVICEID = B.SERVICEID
WHERE
ISSCOPE > 0 AND
ESTIMATEID=827 AND
VERSIONNO=1
)A,
JOIN TBLDESIGNATIONREFERENCE C ON A.SERVICEID = C.SERVICEID
JOIN TBLONSITEOFFSHORERATIO D ON A.SERVICEID = D.SERVICEID
WHERE
LOCATION IN ('OFFSHORE','ONSITE') AND
REFERENCEMODEL='AGGRESSIVE'
end
请为我的解决方案评分
Please rate my Solution
这篇关于如何使用“for循环”在sp的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!