Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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
Edited 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
0 Sergey Alexandrovich Kryukov 503
1 OriginalGriff 384
2 George Jonsson 258
3 Animesh Datta 130
4 Shemeemsha RA 128
0 OriginalGriff 6,099
1 Sergey Alexandrovich Kryukov 5,411
2 CPallini 4,770
3 George Jonsson 3,400
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 21 Dec 2012
Copyright © CodeProject, 1999-2014
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