Click here to Skip to main content
13,141,576 members (54,479 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
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


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.
Posted 20-Dec-12 18:47pm
Updated 21-Dec-12 5:47am
v3

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web03 | 2.8.170915.1 | Last Updated 21 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100