Solved

help in stored procedure(reposted)

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

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 142

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
 
LVL 40

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 40

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 40

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 40

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now