Solved

Help needed in stored proc

Posted on 2011-02-26
2
270 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
2 Comments
 
LVL 40

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

839 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