Solved

stored proc help

Posted on 2011-03-03
15
263 Views
Last Modified: 2012-06-27

hi experts

 i need to create a report based on the attached stored proc.My requirement in the project is i need the Report in following format


customer name:primus        
county :blackwelder

item# item desc  unit unitCost  UnitPrice Totalcost Total Price
 1       fsf       1    2          3         2          3



customer name:HP      
county :alexender

item# item desc  unit unitCost  UnitPrice Totalcost Total Price
2        fd           2     6         4              12        

:
:
:
and so on depending on input

here  the fields are repeating.How to make changes in the proc.IF i need to implement the proc in temp table,please let me know how to do that in the proc






GetLettingdate.txt
0
Comment
Question by:sqlcurious
  • 8
  • 7
15 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35028130
--exec DBSP_Report_GetLettingDate '01/01/2006','01/01/2011','1','all',''

--exec DBSP_Report_GetLettingDate '01/01/2011','02/25/2011',1,'Global solutions,dcc,spinsci','division description1,division description 2,division description 4,division description 5'

CREATE  PROCEDURE [dbo].[DBSP_Report_GetLettingDate]
(      
      @LettingDateFrom datetime,  
      @LettingDateTo datetime,  
      @ProjectTxDot int,  
      @CustomerName varchar(50)='',  
      @DivisionName varchar(250)=''
 )

AS

BEGIN
declare @SQL varchar(8000)
decalre @country varchar(255)

   
SELECT      CU.CustomerID,  
            CU.CustomerName,  
            P.ProjectID ,  
            P.Projectname,  
            p.ProjectNumber,  
            P.ProjectCountyID,  
            convert(varchar(10),P.ProjectStartDate,110) AS ProjectStartDate,  
            P.projectTxdot,  
            D.Divisionname As DivisionName,  
            --P.projectCountyID As CountyID,  
            C.CountyName As CountyName,  
            --CU.CustomerName As CustomerName,  
            --CU.customerID,  
            QI.QuoteItemQuantity as Quantity,  
            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.QuoteItemWeight*QI.QuoteItemQuantity) AS TotalWeight,  
            QI.QuoteItemWeight AS ItemWeight,  
            QI.QuoteItemMarkupPercent AS MarkupPercent,  
            Q.QuoteFreightRate as FreightRate,  
            Q.QuoteNumberOfTrucks,  
            Q.QuoteSurcharge1,  
            Q.QuoteSurcharge2,  
            Q.QuoteSurcharge3,  
            ((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)) AS TotalFreight,
            (((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice)-(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice)-((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)))/((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice))*100) AS MARGIN
into #temptable
FROM      TBL_Project P INNER JOIN  TBL_Quotes1 Q
            ON P.ProjectID = Q.ProjectID  inner   JOIN  TBL_County C
            ON C.CountyID = P.ProjectCountyID inner JOIN  TBL_QuoteItems QI
            ON Q.QuoteID = QI.QuoteID INNER JOIN  TBL_Division D
            ON D.createdby = Q.QuoteCreatedby --INNER JOIN  TBL_CustomerQuotes CU
            -- ON CU.QuoteID = Q.QuoteID  
            inner join tbl_customer cu on cu.customercreatedby=Q.QuoteCreatedby
WHERE            p.projectstartdate >= ISNULL(@LettingDateFrom,p.projectstartdate)
            and p.projectstartdate <= ISNULL(@LettingDateTo,p.projectstartdate)
            and D.DivisionName IN (SELECT Item FROM dbo.fneBid_SplitValue(CASE WHEN @DivisionName = '' OR @DivisionName IS NULL Then D.DivisionName Else @DivisionName END, ','))
            and p.ProjectTxDot = CASE WHEN @projectTxDot = 1 Then p.ProjectTxDot Else @projectTxDot END
            and CU.CustomerName IN (SELECT Item FROM dbo.fneBid_SplitValue(CASE WHEN @CustomerName = 'all' Then CU.CustomerName Else @CustomerName END, ','))


select top 1 CustomerName,CountyName from #temp table
select * from #temptable
drop #temptable


END

GO


0
 

Author Comment

by:sqlcurious
ID: 35028280
what about for this
 item# item desc  unit unitCost  UnitPrice Totalcost Total Price
0
 

Author Comment

by:sqlcurious
ID: 35028345

i corrected spelling mistakes for declare  and corrected #temptable but still got following message
Msg 156, Level 15, State 1, Procedure DBSP_Report_GetLettingDate, Line 87
Incorrect syntax near the keyword 'END'.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:sqlcurious
ID: 35028362
select top 1 CustomerName,CountyName from #temptable
instead of above can i give following?
select * from #temptable
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35028645
hi

my bad : at the end of the script change " drop @temptable " to : drop table #temptable

you can query the #temptable any way you want.
0
 

Author Comment

by:sqlcurious
ID: 35028986
yes the proc executed .I have another question


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.

i have attached two files
rpteBidProjectDetailCosting1.pdf
0
 

Author Comment

by:sqlcurious
ID: 35028997
to be more clear

My total bid price=sum(total project)=24.96+35.04+10+21=91
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35029037
add
select sum (column) from #temptable
0
 

Author Comment

by:sqlcurious
ID: 35029243
i want to add extra column in the result set as total bid price

select sum ((QuoteItemQuantity  * QuoteItem_ItemTotalPrice ) as Total BID price from #temptable
but throwing me error in QuoteItemQuantity andQuoteItem_ItemTotalPrice  as invalid column name since there is column

0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35029411
add these coloumns to the main query and then you can query them.

id they allready exists then try removing the brakets from query
0
 

Author Comment

by:sqlcurious
ID: 35029547
tried to put like this in the main query but its not working can u help?


SELECT      CU.CustomerID,  
            CU.CustomerName,  
            P.ProjectID ,  
            P.Projectname,  
            p.ProjectNumber,  
            P.ProjectCountyID,  
            convert(varchar(10),P.ProjectStartDate,110) AS ProjectStartDate,  
            P.projectTxdot,  
            D.Divisionname As DivisionName,  
            C.CountyName As CountyName,  
            QI.QuoteItemQuantity as Quantity,  
            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.QuoteItemWeight*QI.QuoteItemQuantity) AS TotalWeight,  
            QI.QuoteItemWeight AS ItemWeight,  
            QI.QuoteItemMarkupPercent AS MarkupPercent,  
            Q.QuoteFreightRate as FreightRate,  
            Q.QuoteNumberOfTrucks,  
            Q.QuoteSurcharge1,  
            Q.QuoteSurcharge2,  
            Q.QuoteSurcharge3,  
            ((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)) AS TotalFreight,
            (((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice)-(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice)-((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)))/((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice))*100) AS MARGIN,
            sum (QI.QuoteItemQuantity  * QI.QuoteItem_ItemTotalPrice )) as TotalBIDprice
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35029932
hi

you cant do that.

sum is an aggregate function and requiers you to use Groupby on all the rest of the columns.

you should use this function on the #temptable and retrive the value.

start with this.

select    sum (Quantity * UnitPrice)) as TotalBIDprice
from #temptable
0
 

Author Comment

by:sqlcurious
ID: 35030153
it works, but it is showing the result in different rows .actually i want to add extra column and show the result.how to put  select    sum (Quantity * UnitPrice)) as TotalBIDprice
from #temptable
in the main query


SELECT      CU.CustomerID,  
            CU.CustomerName,  
            P.ProjectID ,  
            P.Projectname,  
            p.ProjectNumber,  
            P.ProjectCountyID,  
            convert(varchar(10),P.ProjectStartDate,110) AS ProjectStartDate,  
            P.projectTxdot,  
            D.Divisionname As DivisionName,  
            C.CountyName As CountyName,  
            QI.QuoteItemQuantity as Quantity,  
            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.QuoteItemWeight*QI.QuoteItemQuantity) AS TotalWeight,  
            QI.QuoteItemWeight AS ItemWeight,  
            QI.QuoteItemMarkupPercent AS MarkupPercent,  
            Q.QuoteFreightRate as FreightRate,  
            Q.QuoteNumberOfTrucks,  
            Q.QuoteSurcharge1,  
            Q.QuoteSurcharge2,  
            Q.QuoteSurcharge3,  
            ((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)) AS TotalFreight,
            (((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice)-(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice)-((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)))/((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice))*100) AS MARGIN
     
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35030805
add this at the end

    group by   CU.CustomerID,  
            CU.CustomerName,  
            P.ProjectID ,  
            P.Projectname,  
            p.ProjectNumber,  
            P.ProjectCountyID,  
            convert(varchar(10),P.ProjectStartDate,110) AS ProjectStartDate,  
            P.projectTxdot,  
            D.Divisionname As DivisionName,  
            C.CountyName As CountyName,  
            QI.QuoteItemQuantity as Quantity,  
            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.QuoteItemWeight*QI.QuoteItemQuantity) AS TotalWeight,  
            QI.QuoteItemWeight AS ItemWeight,  
            QI.QuoteItemMarkupPercent AS MarkupPercent,  
            Q.QuoteFreightRate as FreightRate,  
            Q.QuoteNumberOfTrucks,  
            Q.QuoteSurcharge1,  
            Q.QuoteSurcharge2,  
            Q.QuoteSurcharge3,  
            ((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)) AS TotalFreight,
            (((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice)-(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice)-((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)))/((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice))*100) AS MARGIN
0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 500 total points
ID: 35030929
what you need to do is add a GROUP BY clause at the end of the query
and add all the columns. but not the column with the SUM function.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

777 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