[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

Help needed in stored proc

hi experts

Hi want to build a report and need to add extra column called margin %by using  following formula in the stored proc.I have attached the sample output of the proc and stored proc


the markup percent is taken directly from the column .Now i want to implement it using procedure similarly for total freight too.

i want additional margin column which has following formula

  margin=(total bid price)-(sum of total cost)-(total base freight)/(total bid price)*100

where total base freight=total freight
and sum of total costs=total costs
sum of total price=total bid price

sample-output-project-detail-cos.txt
reportdetailcost.txt
0
sqlcurious
Asked:
sqlcurious
1 Solution
 
SharathData EngineerCommented:
try this.
USE [eBidManager]
GO

/****** Object:  StoredProcedure [dbo].[DBSP_Report_GetProjectDetailCost1]    Script Date: 02/25/2011 09:05:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO











-- select divisionName from TBL_Division
-- exec DBSP_Report_GetProjectDetailCost1 @ProjectName='March,Ebids',@ProjectLettingDateFrom='01/01/2006',@ProjectLettingDateTo='01/01/2011',@DivisionName=''
-- exec DBSP_Report_GetProjectDetailCost1 @ProjectLettingDateFrom='01/01/2006',@ProjectLettingDateTo='01/01/2011',@DivisionName='division description 5'
-- =============================================
-- Create date: <01/21/2011>
-- Description:	<stored procedure to find total Project Costs>
-- =============================================



ALTER     PROCEDURE [dbo].[DBSP_Report_GetProjectDetailCost1]
(	
	--@projectID int =null,
	@ProjectNumber VARCHAR(50)=Null,
	@ProjectControlNumber VARCHAR(50)= Null,
	@ProjectLettingDateFrom DATETIME,
	@ProjectLettingDateTo DATETIME,
	@ProjectName varchar(50) = Null,
	@DivisionName VARCHAR(Max) = Null
)	
AS	
begin
DECLARE @SQL VARCHAR(8000)
  

  set @SQL= 
  'SELECT *,((TotalPrice - TotalCost - TotalFreight)/TotalPrice)*100 as Margin
  FROM ( SELECT 
P.ProjectID,
 P.Projectname,
P.ProjectControlNumber,
P.ProjectCountyID,
P.ProjectNumber,
 convert(varchar(10),p.ProjectStartDate,110)as ProjectStartDate, 
QI.QuoteItem_ItemNumber AS Item#,
QI.QuoteItemDescription As ItemDescription,
QI.QuoteItemQuantity As ItemQuantity,
QI.QuoteItemUnit As ItemUnit,
QI.QuoteItemUnitprice As UnitCost,
QI.QuoteItem_ItemTotalPrice As UnitPrice,
QI.QuoteItemQuantity * QI.QuoteItemUnitPrice As TotalCost,    
QI.QuoteItemQuantity * QI.QuoteItem_ItemTotalPrice As TotalPrice ,
QI.QuoteItemMarkupPercent AS MarkupPercent,
D. DivisionID,
D.Divisionname,
C.CountyName,
Q.QuoteID,
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3) AS TotalFreight

FROM TBL_Project P 
inner  join TBL_Quotes1 Q ON Q.ProjectID = P.ProjectID
inner  JOIN TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID
inner  join TBL_Division D ON D.CreatedBy = Q.QuoteCreatedBy
inner  join TBL_County C ON P.ProjectCountyID = C.CountyID
where P.projectID>=1'




if(@projectnumber is not null and @projectnumber <> '')                
 Begin                
   Set @SQL =  @SQL + ' and p.projectnumber =  '''+ convert(varchar(5),@projectnumber ) +''''               
 End             
          
if(@Projectcontrolnumber is not null and @Projectcontrolnumber <> '')                
 Begin                
   Set @SQL =  @SQL + ' and p.projectcontrolnumber =  '''+ convert(varchar(5),@Projectcontrolnumber) + ''''               
 End            


--if @projectname  <> '' 
--begin
--    set @SQL= @Sql + ' And p.projectname LIKE ''%'+@projectname+'%''' 
--end

if @projectname  <> ''
 begin
 
 SET @SQL= @Sql + ' AND P.ProjectName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @ProjectName + ''','',''))'
 end
 



If @ProjectLettingDateFrom Is Not Null
	Begin
		Set @SQL = @SQL + ' And p.projectstartdate >='''+Cast(@ProjectLettingDateFrom As Varchar(30))+''''
	End

If @ProjectLettingDateTo Is Not Null
	Begin
		Set @SQL = @SQL + ' And p.projectstartdate <='''+Cast(@ProjectLettingDateTo As Varchar(30))+''''
	End

if @DivisionName <> ''

-- --begin 
-- --      set @SQL= @Sql + ' And D.DivisionName LIKE ''%'+@DivisionName+'%''' 
-- --end

 begin
 
 SET @SQL= @Sql + ' AND D.DivisionName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
 end
 
 
-- begin
-- SET @SQL= @Sql + ' AND D.DivisionName in (SELECT DivisionName FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
--end 
 

 
-- --set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue('''+@DivisionName+''','','')) '
 
 
-- --end
 
-- --set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue(''%'+@DivisionName+'%'','',''))'

----set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue('+@DivisionName+','',''))'  

-- --SET @SQL= @Sql + ' AND (exists(SELECT * FROM dbo.fneBid_SplitValue('+@DivisionName))'
----end 
-- --set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue(''%'+@DivisionName+'%'',",")) '
-- --set @SQL= @Sql + ' And D.DivisionID in (select  *from dbo.fneBid_SplitValue(@DivisionName)) '
----end 


--select sum(QuoteItemQuantity * QuoteItem_ItemTotalPrice ) AS TotalBidPrice from tbl_QuoteItems


SET @SQL = @SQL + ') t1'

print @sql
exec (@sql)

END
GO

Open in new window

0
 
sqlcuriousAuthor Commented:
hi expert

i need to develop the report based on the stored proc. I have attached the report sample .
My question is
i have to calculate total bid price=sum of all total price in the project.

For example in my attached report
My total bid price=total project=24.96+35.04+10+21=91

how to implement this in the above procedure.

rpteBidProjectDetailCosting1.pdf
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now