?
Solved

help in stored procedure(reposted)

Posted on 2011-03-04
10
Medium Priority
?
240 Views
Last Modified: 2012-05-11
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
Comment
Question by:sqlcurious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
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

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35038483
btw, I had written an article about  update with join:
http://www.experts-exchange.com/A_1517.html
0
 

Author Comment

by:sqlcurious
ID: 35038541
i want to add TOTALProjectPrice and SUMOFTOTALCOST  as extra column .How to do this?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 41

Expert Comment

by:Sharath
ID: 35039416
Add these two steps in your SP.
ALTER TABLE #temptable 
ADD TOTALProjectPrice DECIMAL(10,4), 
    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    

Open in new window


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 
ADD TOTALProjectPrice DECIMAL(10,4), 
    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

Open in new window

0
 

Author Comment

by:sqlcurious
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

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

Expert Comment

by:Sharath
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

by:sqlcurious
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

by:
Sharath earned 2000 total points
ID: 35040081
Please try this.
--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

Open in new window

0
 

Author Comment

by:sqlcurious
ID: 35040385
ya i got it thanks
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

762 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