Solved

Help needed in stored proc

Posted on 2011-02-26
2
277 Views
Last Modified: 2012-06-27
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
Comment
Question by:sqlcurious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 34988536
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
 

Author Comment

by:sqlcurious
ID: 34992569
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question