Solved

stored proc help

Posted on 2011-03-03
15
266 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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…

791 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