x
Solved

# help in stored procedure(reposted)

Posted on 2011-03-04
Medium Priority
245 Views
I have re posted the question since i deleted the previous one as the thread was bit long

i have this procedure from which i need to build the report.

I need to do some calculation in the report
SUch as TotalProjectCost
and SumofTotalCost  and these are the two extra columns that needs to be added. I am using #temptable and  wanted to use the following statement in the procedure

UPDATE #temptable
SET     TOTALProjectPrice = TEMP2.TOTALPRICE
FROM    ( SELECT    SUM(TOTALPRICE) AS TOTALPRICE ,
DIVISIONID ,
ProjectID
FROM      #temptable
GROUP BY  ProjectID ,
DIVISIONID
) TEMP2

UPDATE  #temptable
SET     SUMOFTOTALCOST = TEMP3.TOTalCOST
FROM    ( SELECT    SUM(TOTALCOST) AS TOTALCOST ,
DIVISIONID ,
ProjectID
FROM      #temptable
GROUP BY ProjectID ,
DIVISIONID

)TEMP3

ProjectLettingDate.txt
0
Question by:sqlcurious
• 4
• 4
• 2

LVL 143

Expert Comment

ID: 35038480
you would do this, for example
``````UPDATE t
SET  TOTALProjectPrice =    ( SELECT    SUM(i.TOTALPRICE)
FROM      #temptable i
WHERE i.ProjectID = t.ProjectID
AND i.DivisionID = t.DivisionID
)
,SUMOFTOTALCOST  = ( SELECT    SUM(i.TOTalCOST)
FROM      #temptable i
WHERE i.ProjectID = t.ProjectID
AND i.DivisionID = t.DivisionID
)

FROM #temptable t
``````
0

LVL 143

Expert Comment

ID: 35038483
http://www.experts-exchange.com/A_1517.html
0

Author Comment

ID: 35038541
i want to add TOTALProjectPrice and SUMOFTOTALCOST  as extra column .How to do this?
0

LVL 41

Expert Comment

ID: 35039416
``````ALTER TABLE #temptable
SUMOFTOTALCOST DECIMAL(10,4)
UPDATE t1
SET t1.TOTALProjectPrice = t2.TOTALPRICE,
t1.SUMOFTOTALCOST = t2.TOTALCOST
FROM #temptable t1
JOIN (  SELECT SUM(TOTALCOST)  AS TOTALCOST,
SUM(TOTALPRICE) AS TOTALPRICE,
DIVISIONID,
ProjectID
FROM #temptable
GROUP BY ProjectID,
DIVISIONID) t2
ON t1.ProjectID = t2.ProjectID
AND t1.DIVISIONID = t2.DIVISIONID
``````

Here is the complete SP.
``````--exec DBSP_Report_GetLettingDate '01/01/2011','02/25/2011',1,'Ark Contracting,A.U.I. Contractors','division description1,division description 2'

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

AS

BEGIN
--declare @SQL varchar(8000)
--DECLARE @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.DivisionID,
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, ','))

ALTER TABLE #temptable
SUMOFTOTALCOST DECIMAL(10,4)
UPDATE t1
SET t1.TOTALProjectPrice = t2.TOTALPRICE,
t1.SUMOFTOTALCOST = t2.TOTALCOST
FROM #temptable t1
JOIN (  SELECT SUM(TOTALCOST)  AS TOTALCOST,
SUM(TOTALPRICE) AS TOTALPRICE,
DIVISIONID,
ProjectID
FROM #temptable
GROUP BY ProjectID,
DIVISIONID) t2
ON t1.ProjectID = t2.ProjectID
AND t1.DIVISIONID = t2.DIVISIONID

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

drop TABLE  #temptable
END
GO
``````
0

Author Comment

ID: 35039698
Is there any way that i can modify my stored proc by creating #temptable as below.So that i can add all the extra columns in the temp table that is required for reporting.Here I need to add more columns
like  MARGINPERCENT,  MARKUPFORDIVISION,TotalfreightForQuote and the way is below

UPDATE  #temptable
SET     MARGINPERCENT = CASE WHEN TOTALBIDPRICE > 0
THEN ( ( TOTALBIDPRICE - SUMOFTOTALCOST
- TOTALFREIGHTFORQUOTE )
/ TOTALBIDPRICE ) * 100
ELSE 0
END

UPDATE  #temptable
SET     MARKUPFORDIVISION = CASE WHEN SUMOFTOTALCOST > 0
THEN ( ( TOTALBIDPRICE - SUMOFTOTALCOST
- TOTALFREIGHTFORQUOTE )
/ SUMOFTOTALCOST ) * 100
ELSE 0
END

UPDATE  #temptable
SET     TOTALFREIGHTforQuote = TEMP1.TOTFREIGHT
FROM    ( SELECT    SUM(TotalFreight) AS TOTalFREIGHT ,
PROJID
FROM      #tmp_data1
GROUP BY  PROJID
) TEMP1
WHERE   #tmp_data.PRJID = TEMP1.PROJID

structure of temp table(example)

CREATE  TABLE #temptable
(
PRJID BIGINT ,
PROJECTNAME VARCHAR(50) ,
CONTROLNUMBER VARCHAR(50) ,
COUNTYID BIGINT ,
PROJECTNUMBER VARCHAR(50) ,
STARTDATE DATETIME ,
ITEMNUMBER VARCHAR(50) ,
ITEMDESC VARCHAR(2000) ,
QUANTITY DECIMAL(15, 4) ,
UNIT VARCHAR(50) ,
UNITCOST DECIMAL(15, 4) ,
UNITPRICE DECIMAL(15, 4) ,
TOTALCOST DECIMAL(15, 4) ,
TOTALPRICE DECIMAL(15, 4) ,
MARKUPPERCENT DECIMAL(15, 4) ,
DIVISIONID BIGINT ,
DIVISIONNAME VARCHAR(50) ,
COUNTYNAME VARCHAR(50) ,
QUOTID BIGINT ,
TOTALFREIGHTFORQUOTE DECIMAL(15, 4) ,
TOTALFREIGHT DECIMAL(15, 4) ,
TOTALBIDPRICE DECIMAL(15, 4) ,
SUMOFTOTALCOST DECIMAL(15, 4) ,
MARKUPFORDIVISION DECIMAL(15, 4) ,
MARGINPERCENT DECIMAL(15, 4) ,
PROJECTTOTALBIDPRICE DECIMAL(15, 4) ,
PROJECTTOTALCOST DECIMAL(15, 4) ,
PRJMARGINPERCENT DECIMAL(15, 4) ,
PRJMARKUPPERCENT DECIMAL(15, 4)

and how to proceed furthur for inserting and selecting from the temp table
0

LVL 41

Expert Comment

ID: 35039772
Is the table #tmp_data another temp table? I did not find this table in your SP.
0

LVL 41

Expert Comment

ID: 35039841
You can create the table first and then insert the data. You can have an INSERT statement like this. But make sure the order of columns in SELECT clause. The order (Col1,Col2,Col3...) should be same as the order of columns in VALUES claues (Col1,Col2,Col3,...)

INSERT INTO #temptable (Col1,Col2,Col3,...)
SELECT Col1,Col2,Col3,...
FROM ....

After the INSERT statement, you can have the UPDATE statement to update required columns.
0

Author Comment

ID: 35039982
my bad please consider #tmp_data as #temptable(they are same) .There is no another temp table in the proc
0

LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 35040081
``````--exec DBSP_Report_GetLettingDate '01/01/2011','02/25/2011',1,'Ark Contracting,A.U.I. Contractors','division description1,division description 2'
ALTER PROCEDURE [dbo].[DBSP_REPORT_GETLETTINGDATE](
@LettingDateFrom DATETIME,
@LettingDateTo DATETIME,
@ProjectTxDot INT,
@CustomerName VARCHAR(50)  = '',
@DivisionName VARCHAR(250)  = '')
AS
BEGIN
--declare @SQL varchar(8000)
--DECLARE @country varchar(255)
CREATE TABLE #temptable (
PRJID BIGINT,
PROJECTNAME VARCHAR(50),
CONTROLNUMBER VARCHAR(50),
COUNTYID BIGINT,
PROJECTNUMBER VARCHAR(50),
STARTDATE DATETIME,
ITEMNUMBER VARCHAR(50),
ITEMDESC VARCHAR(2000),
QUANTITY DECIMAL(15,4),
UNIT VARCHAR(50),
UNITCOST DECIMAL(15,4),
UNITPRICE DECIMAL(15,4),
TOTALCOST DECIMAL(15,4),
TOTALPRICE DECIMAL(15,4),
MARKUPPERCENT DECIMAL(15,4),
DIVISIONID BIGINT,
DIVISIONNAME VARCHAR(50),
COUNTYNAME VARCHAR(50),
QUOTID BIGINT,
TOTALFREIGHTFORQUOTE DECIMAL(15,4),
TOTALFREIGHT DECIMAL(15,4),
TOTALBIDPRICE DECIMAL(15,4),
SUMOFTOTALCOST DECIMAL(15,4),
MARKUPFORDIVISION DECIMAL(15,4),
MARGINPERCENT DECIMAL(15,4),
PROJECTTOTALBIDPRICE DECIMAL(15,4),
PROJECTTOTALCOST DECIMAL(15,4),
PRJMARGINPERCENT DECIMAL(15,4),
PRJMARKUPPERCENT DECIMAL(15,4))

INSERT #temptable(... include the columns in the order of SELECT clause below...)
SELECT CU.CustomerID,
CU.CustomerName,
P.ProjectID,
P.Projectname,
p.ProjectNumber,
P.ProjectCountyID,
CONVERT(VARCHAR(10),P.ProjectStartDate,110)                                                                                                                                                                                                                                             AS ProjectStartDate,
P.projectTxdot,
D.DivisionID,
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
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,','));

;WITH CTE
AS (SELECT SUM(TOTALCOST)
OVER(PARTITION BY DIVISIONID,ProjectID ) TOTALCOST,
SUM(TOTALPRICE)
OVER(PARTITION BY DIVISIONID,ProjectID ) TOTALPRICE,
SUM(TotalFreight)
OVER(PARTITION BY ProjectID ) TotalFreight,
MARGINPERCENT = CASE
WHEN TOTALBIDPRICE > 0 THEN ((TOTALBIDPRICE - SUMOFTOTALCOST - TOTALFREIGHTFORQUOTE) / TOTALBIDPRICE) * 100
ELSE 0
END,
MARKUPFORDIVISION = CASE
WHEN SUMOFTOTALCOST > 0 THEN ((TOTALBIDPRICE - SUMOFTOTALCOST - TOTALFREIGHTFORQUOTE) / SUMOFTOTALCOST) * 100
ELSE 0
END
FROM #temptable)
UPDATE t1
SET t1.TOTALProjectPrice = t2.TOTALPRICE,
t1.SUMOFTOTALCOST = t2.TOTALCOST,
t1.TOTALFREIGHTforQuote = t2.TotalFreight,
t1.MARGINPERCENT = t2.MARGINPERCENT,
t1.MARKUPFORDIVISION = t2.MARKUPFORDIVISION
FROM #temptable t1
JOIN CTE c1
ON t1.DIVISIONID = t2.DIVISIONID
AND t1.ProjectID = t2.ProjectID

--select top 1 CustomerName,CountyName from #temp table
SELECT *
FROM #temptable

DROP TABLE #temptable
END

GO
``````
0

Author Comment

ID: 35040385
ya i got it thanks
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
###### Suggested Courses
Course of the Month8 days, 10 hours left to enroll