Solved

help in stored procedure(reposted)

Posted on 2011-03-04
10
238 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
migrate a SQL 2008 to 2016, 2 45
Sql Server group by 10 51
SQL Sum of items in two tables not equal. 5 47
Assigning Database Principals to Database Roles 3 19
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

749 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