Solved

# help in stored procedure(reposted)

Posted on 2011-03-04
233 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 142

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 142

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 40

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 40

Expert Comment

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

LVL 40

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 40

Accepted Solution

Sharath earned 500 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

### Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…