sqlcurious
asked on
stored proc help
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
ASKER
what about for this
item# item desc unit unitCost UnitPrice Totalcost Total Price
item# item desc unit unitCost UnitPrice Totalcost Total Price
ASKER
i corrected spelling mistakes for declare and corrected #temptable but still got following message
Msg 156, Level 15, State 1, Procedure DBSP_Report_GetLettingDate
Incorrect syntax near the keyword 'END'.
ASKER
select top 1 CustomerName,CountyName from #temptable
instead of above can i give following?
select * from #temptable
instead of above can i give following?
select * from #temptable
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.
my bad : at the end of the script change " drop @temptable " to : drop table #temptable
you can query the #temptable any way you want.
ASKER
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
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=
how to implement this in the above procedure.
i have attached two files
rpteBidProjectDetailCosting1.pdf
ASKER
to be more clear
My total bid price=sum(total project)=24.96+35.04+10+21 =91
My total bid price=sum(total project)=24.96+35.04+10+21
add
select sum (column) from #temptable
select sum (column) from #temptable
ASKER
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_ItemTotalPric e as invalid column name since there is column
select sum ((QuoteItemQuantity * QuoteItem_ItemTotalPrice ) as Total BID price from #temptable
but throwing me error in QuoteItemQuantity andQuoteItem_ItemTotalPric
add these coloumns to the main query and then you can query them.
id they allready exists then try removing the brakets from query
id they allready exists then try removing the brakets from query
ASKER
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.Proj ectStartDa te,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_ItemTotalPric e As UnitPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,
(QI.QuoteItemQuantity*QI.Q uoteItem_I temTotalPr ice) AS TotalPrice,
(QI.QuoteItemWeight*QI.Quo teItemQuan tity) 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.Quot eSurcharge 2+Q.QuoteS urcharge3) ) AS TotalFreight,
(((QI.QuoteItemQuantity*QI .QuoteItem _ItemTotal Price)-(QI .QuoteItem Quantity * QI.QuoteItemUnitPrice)-((Q .QuoteFrei ghtRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.Quot eSurcharge 2+Q.QuoteS urcharge3) ))/((QI.Qu oteItemQua ntity*QI.Q uoteItem_I temTotalPr ice))*100) AS MARGIN,
sum (QI.QuoteItemQuantity * QI.QuoteItem_ItemTotalPric e )) as TotalBIDprice
SELECT CU.CustomerID,
CU.CustomerName,
P.ProjectID ,
P.Projectname,
p.ProjectNumber,
P.ProjectCountyID,
convert(varchar(10),P.Proj
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_ItemTotalPric
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,
(QI.QuoteItemQuantity*QI.Q
(QI.QuoteItemWeight*QI.Quo
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.Quot
(((QI.QuoteItemQuantity*QI
sum (QI.QuoteItemQuantity * QI.QuoteItem_ItemTotalPric
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
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
ASKER
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.Proj ectStartDa te,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_ItemTotalPric e As UnitPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,
(QI.QuoteItemQuantity*QI.Q uoteItem_I temTotalPr ice) AS TotalPrice,
(QI.QuoteItemWeight*QI.Quo teItemQuan tity) 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.Quot eSurcharge 2+Q.QuoteS urcharge3) ) AS TotalFreight,
(((QI.QuoteItemQuantity*QI .QuoteItem _ItemTotal Price)-(QI .QuoteItem Quantity * QI.QuoteItemUnitPrice)-((Q .QuoteFrei ghtRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.Quot eSurcharge 2+Q.QuoteS urcharge3) ))/((QI.Qu oteItemQua ntity*QI.Q uoteItem_I temTotalPr ice))*100) AS MARGIN
from #temptable
in the main query
SELECT CU.CustomerID,
CU.CustomerName,
P.ProjectID ,
P.Projectname,
p.ProjectNumber,
P.ProjectCountyID,
convert(varchar(10),P.Proj
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_ItemTotalPric
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,
(QI.QuoteItemQuantity*QI.Q
(QI.QuoteItemWeight*QI.Quo
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.Quot
(((QI.QuoteItemQuantity*QI
add this at the end
group by CU.CustomerID,
CU.CustomerName,
P.ProjectID ,
P.Projectname,
p.ProjectNumber,
P.ProjectCountyID,
convert(varchar(10),P.Proj ectStartDa te,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_ItemTotalPric e As UnitPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,
(QI.QuoteItemQuantity*QI.Q uoteItem_I temTotalPr ice) AS TotalPrice,
(QI.QuoteItemWeight*QI.Quo teItemQuan tity) 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.Quot eSurcharge 2+Q.QuoteS urcharge3) ) AS TotalFreight,
(((QI.QuoteItemQuantity*QI .QuoteItem _ItemTotal Price)-(QI .QuoteItem Quantity * QI.QuoteItemUnitPrice)-((Q .QuoteFrei ghtRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.Quot eSurcharge 2+Q.QuoteS urcharge3) ))/((QI.Qu oteItemQua ntity*QI.Q uoteItem_I temTotalPr ice))*100) AS MARGIN
group by CU.CustomerID,
CU.CustomerName,
P.ProjectID ,
P.Projectname,
p.ProjectNumber,
P.ProjectCountyID,
convert(varchar(10),P.Proj
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_ItemTotalPric
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,
(QI.QuoteItemQuantity*QI.Q
(QI.QuoteItemWeight*QI.Quo
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.Quot
(((QI.QuoteItemQuantity*QI
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
--exec DBSP_Report_GetLettingDate
CREATE PROCEDURE [dbo].[DBSP_Report_GetLett
(
@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.Proj
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_ItemTotalPric
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,
(QI.QuoteItemQuantity*QI.Q
(QI.QuoteItemWeight*QI.Quo
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.Quot
(((QI.QuoteItemQuantity*QI
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.Quo
WHERE p.projectstartdate >= ISNULL(@LettingDateFrom,p.
and p.projectstartdate <= ISNULL(@LettingDateTo,p.pr
and D.DivisionName IN (SELECT Item FROM dbo.fneBid_SplitValue(CASE
and p.ProjectTxDot = CASE WHEN @projectTxDot = 1 Then p.ProjectTxDot Else @projectTxDot END
and CU.CustomerName IN (SELECT Item FROM dbo.fneBid_SplitValue(CASE
select top 1 CustomerName,CountyName from #temp table
select * from #temptable
drop #temptable
END
GO