Solved

help in stored proc

Posted on 2011-02-27
13
177 Views
Last Modified: 2012-06-21
i have  created temp table  called #temp_data and #tmp_data1  to store and have also written insert statement to insert the value.

In the database there are two tables called tmp_data and tmp_data1.

But when i run the attached procedure i am not geeting any value i am just getting the output as shown in attachments.I am only getting the headers not values

Any experts please help
stored-prjdetcost.txt
projdetailoutput.txt
0
Comment
Question by:sqlcurious
  • 6
  • 5
  • 2
13 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 357 total points
ID: 34992516
Mainly I see that this line is commented

--EXEC sp_executesql @SQL,

?
Raj
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 357 total points
ID: 34992524
All depends on the INSERT statement.

Make sure the select statement in the insert query has results

Raj
0
 

Author Comment

by:sqlcurious
ID: 34992660
i got following error if i not commenting

EXEC sp_executesql @SQL, N'ProjectID INT, @ProjectNumber varchar(50),@ProjectControlNumber varchar(50), @ProjectLettingDateFrom DATETIME, @ProjectLettingDateTo DATETIME,@DivisionList varchar(50)', @projectID, @ProjectNumber ,@ProjectControlNumber, @ProjectLettingDateFrom,@projectLettingDateTo



Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

0
 

Author Comment

by:sqlcurious
ID: 34992671
the select statement has the results.
what could be the solution?
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 143 total points
ID: 34993364
AND    
('+ CONVERT(VARCHAR,ISNULL(@PROJECTID,0))
+ ' = 0 OR P.ProjectID = ' + CONVERT(VARCHAR,@PROJECTID)
+ ')    
( ' +

appears to be incorrect...

have you printed and debugged your dynamica sql?

WHY ON EARTH ARE YOU USING DYNAMIC SQL IN THIS PROCEDURE ANYWAY?
0
 

Author Comment

by:sqlcurious
ID: 34993598
Now i removed the dynamic sql and use following procedure still the result is same


USE [eBidManager]
GO

/****** Object:  StoredProcedure [dbo].[DBSP_Report_GetProjectDetailCost]    Script Date: 02/27/2011 12:42:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



     
   
   
   
 --select *from #tmp_data    
   
--exec DBSP_Report_GetProjectDetailCost @ProjectLettingDateFrom='01/01/2006',@ProjectLettingDateTo='01/01/2011'    
-- =============================================    
-- Create date: <01/21/2011>    
-- Description: <stored procedure to find total Project Costs>    
-- =============================================    
ALTER   PROCEDURE [dbo].[DBSP_Report_GetProjectDetailCost]    
(    
 --@ProjectID int = 0,    
 @ProjectNumber VARCHAR(50) = NULL,    
 @ProjectControlNumber VARCHAR(50)= NULL,    
 @ProjectLettingDateFrom DATETIME,    
 @ProjectLettingDateTo DATETIME,
 @projectName varchar(50)=null,  
 @DivisionName VARCHAR(250) = NULL    
)    
AS    
DECLARE @SQL VARCHAR(8000)    
     
create  TABLE  #tmp_data    
 (    
   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)    
       
   )    
 CREATE TABLE #tmp_data1    
(    
 PROJID BIGINT,    
 DIVID BIGINT,    
 TFREIGHTFORQUOTE DECIMAL(15,4),    
 TBIDPRICE DECIMAL(15,4),    
 TCOST DECIMAL(15,4)    
)    
   
   
  Set @SQL= 'INSERT INTO #tmp_data    
(    
 PRJID,    
 PROJECTNAME,    
 CONTROLNUMBER,    
 COUNTYID,    
 PROJECTNUMBER,    
 STARTDATE,    
 ITEMNUMBER,    
 ITEMDESC,    
 QUANTITY,    
 UNIT,    
 --UNITCOST,    
 UNITPRICE,    
 TOTALCOST,    
 TOTALPRICE,    
 MARKUPPERCENT,    
 DIVISIONID,    
 DIVISIONNAME,    
 COUNTYNAME,    
 QUOTID,    
 TOTALFREIGHTFORQUOTE,    
     
)    
             
     
   
       
   SELECT    
P.ProjectID AS PRJID,    
P.Projectname AS PROJECTNAME,    
P.ProjectControlNumber AS CONTROLNUMBER,    
P.ProjectCountyID as countyID,    
P.ProjectNumber,    
P.ProjectStartDate as startdate,    
QI.QuoteItem_ItemNumber AS ItemNumber,    
QI.QuoteItemDescription As ItemDesc,    
QI.QuoteItemQuantity As Quantity,    
QI.QuoteItemUnit As Unit,    
QI.QuoteItemUnitprice As UnitPrice,    
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,      
--QI.QuoteItem_ItemTotalPrice As TotalPrice,    
(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS TotalPrice,    
QI.QuoteItemMarkupPercent AS MarkupPercent,    
Q. DivisionId AS DIVISIONID,    
D.Divisionname AS DIVISIONNAME,    
C.CountyName AS COUNTYNAME,    
Q.QuoteID AS QUOTEID,    
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3) AS TOTALFREIGHTFORQUOTE    
   
FROM TBL_Project P    
   
inner    join   TBL_Quotes1 Q ON P.ProjectID = Q.ProjectID    
inner    JOIN TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID    
left    join    TBL_Division D ON D.createdby = Q.quotecreatedby    
inner    join TBL_County C ON P.ProjectCountyID = C.CountyID    
   
where P.projectid>0
and p.projectstartdate>=@Projectlettingdatefrom
andp.projectstartdate<=@projectlettingdateto'    
   
   

 
 
 
UPDATE #tmp_data SET TOTALBIDPRICE = TEMP2.TOTPRICE FROM (    
SELECT SUM(TOTALPRICE) AS TOTPRICE, DIVISIONID, PRJID FROM #tmp_data GROUP BY PRJID,DIVISIONID) TEMP2    
WHERE #tmp_data .DIVISIONID = TEMP2.DIVISIONID AND #tmp_data.PRJID = TEMP2.PRJID    
   
UPDATE #tmp_data SET SUMOFTOTALCOST = TEMP3.TOTCOST FROM (    
SELECT SUM(TOTALCOST) AS TOTCOST, DIVISIONID, PRJID FROM #tmp_data GROUP BY PRJID,DIVISIONID) TEMP3    
WHERE #tmp_data .DIVISIONID = TEMP3.DIVISIONID AND #tmp_data.PRJID = TEMP3.PRJID    
   
UPDATE #tmp_data SET MARGINPERCENT =    
 CASE WHEN TOTALBIDPRICE > 0 THEN    
  ((TOTALBIDPRICE - SUMOFTOTALCOST - TOTALFREIGHTFORQUOTE) / TOTALBIDPRICE ) * 100    
 ELSE    
  0    
 END    
   
UPDATE #tmp_data SET MARKUPFORDIVISION =    
 CASE WHEN SUMOFTOTALCOST > 0 THEN    
  ((TOTALBIDPRICE - SUMOFTOTALCOST - TOTALFREIGHTFORQUOTE) / SUMOFTOTALCOST ) * 100    
 ELSE    
  0    
 END    
   
INSERT INTO #tmp_data1    
(    
 PROJID,    
 DIVID,    
 TFREIGHTFORQUOTE,    
 TBIDPRICE,    
 TCOST    
)    
SELECT DISTINCT PRJID,DIVISIONID,TOTALFREIGHTFORQUOTE,TOTALBIDPRICE,SUMOFTOTALCOST FROM #tmp_data    
--SELECT * FROM #tmp_data1    
   
UPDATE #tmp_data SET TOTALFREIGHT = TEMP1.TOTFREIGHT FROM (    
SELECT SUM(TFREIGHTFORQUOTE) AS TOTFREIGHT,PROJID FROM #tmp_data1 GROUP BY PROJID) TEMP1    
WHERE #tmp_data .PRJID = TEMP1.PROJID    
   
UPDATE #tmp_data SET PROJECTTOTALBIDPRICE = TEMP4.TOTBIDPRICE FROM (    
SELECT SUM(TBIDPRICE) AS TOTBIDPRICE,PROJID FROM #tmp_data1 GROUP BY PROJID) TEMP4    
WHERE #tmp_data .PRJID = TEMP4.PROJID    
   
UPDATE #tmp_data SET PROJECTTOTALCOST = TEMP5.PRJTOTCOST FROM (    
SELECT SUM(TCOST) AS PRJTOTCOST,PROJID FROM #tmp_data1 GROUP BY PROJID) TEMP5    
WHERE #tmp_data .PRJID = TEMP5.PROJID    
   
UPDATE #tmp_data SET PRJMARGINPERCENT =    
 CASE WHEN PROJECTTOTALBIDPRICE > 0 THEN    
  ((PROJECTTOTALBIDPRICE - PROJECTTOTALCOST - TOTALFREIGHT) / PROJECTTOTALBIDPRICE ) * 100    
 ELSE    
  0    
 END    
   
UPDATE #tmp_data SET PRJMARKUPPERCENT =    
 CASE WHEN PROJECTTOTALCOST > 0 THEN    
  ((PROJECTTOTALBIDPRICE - PROJECTTOTALCOST - TOTALFREIGHT) / PROJECTTOTALCOST ) * 100    
 ELSE    
  0    
 END    
   
SELECT * FROM #tmp_data    
   
   
   
   
   

   
   
   
   


GO

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 357 total points
ID: 34994437
Try this correct Stored Procedure
/****** Object:  StoredProcedure [dbo].[DBSP_Report_GetProjectDetailCost]    Script Date: 02/27/2011 12:42:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



      
    
    
    
 --select *from #tmp_data    
    
-- exec DBSP_Report_GetProjectDetailCost @ProjectLettingDateFrom='01/01/2006',@ProjectLettingDateTo='01/01/2011'    
-- =============================================    
-- Create date: <01/21/2011>    
-- Description: <stored procedure to find total Project Costs>    
-- =============================================    
alter   PROCEDURE [dbo].[DBSP_Report_GetProjectDetailCost]
    (
      @ProjectID INT = 0 ,
      @ProjectNumber VARCHAR(50) = NULL ,
      @ProjectControlNumber VARCHAR(50) = NULL ,
      @ProjectLettingDateFrom DATETIME ,
      @ProjectLettingDateTo DATETIME ,
      @DivisionList NVARCHAR(250) = NULL    
    )
AS 
    DECLARE @SQL nVARCHAR(4000)    
     
    CREATE  TABLE #tmp_data
        (
          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)
        )    
    CREATE TABLE #tmp_data1
        (
          PROJID BIGINT ,
          DIVID BIGINT ,
          TFREIGHTFORQUOTE DECIMAL(15, 4) ,
          TBIDPRICE DECIMAL(15, 4) ,
          TCOST DECIMAL(15, 4)
        )    
    
    
    SET @SQL = 'INSERT INTO #tmp_data    
(    
 PRJID,     
 PROJECTNAME,    
 CONTROLNUMBER,    
 COUNTYID,    
 PROJECTNUMBER,    
 STARTDATE,    
 ITEMNUMBER,    
 ITEMDESC,    
 QUANTITY,    
 UNIT,    
 --UNITCOST,    
 UNITPRICE,    
 TOTALCOST,    
 TOTALPRICE,    
 MARKUPPERCENT,    
 DIVISIONID,    
 DIVISIONNAME,    
 COUNTYNAME,    
 QUOTID,    
 TOTALFREIGHTFORQUOTE,    
     
)    
              
      
    
       
   SELECT     
P.ProjectID AS PRJID,    
P.Projectname AS PROJECTNAME,    
P.ProjectControlNumber AS CONTROLNUMBER,    
P.ProjectCountyID as countyID,    
P.ProjectNumber,    
P.ProjectStartDate as startdate,    
QI.QuoteItem_ItemNumber AS ItemNumber,    
QI.QuoteItemDescription As ItemDesc,    
QI.QuoteItemQuantity As Quantity,    
QI.QuoteItemUnit As Unit,    
QI.QuoteItemUnitprice As UnitPrice,    
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,       
--QI.QuoteItem_ItemTotalPrice As TotalPrice,    
(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS TotalPrice,    
QI.QuoteItemMarkupPercent AS MarkupPercent,    
Q. DivisionId AS DIVISIONID,    
D.Divisionname AS DIVISIONNAME,    
C.CountyName AS COUNTYNAME,    
Q.QuoteID AS QUOTEID,    
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3) AS TOTALFREIGHTFORQUOTE    
    
FROM TBL_Project P     
    
inner    join   TBL_Quotes1 Q ON P.ProjectID = Q.ProjectID    
inner    JOIN TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID    
left    join    TBL_Division D ON D.createdby = Q.quotecreatedby    
inner    join TBL_County C ON P.ProjectCountyID = C.CountyID    
    
where P.projectNumber>0    
    
    
AND    
(' + CONVERT(VARCHAR, ISNULL(@PROJECTID, 0)) + ' = 0 OR P.ProjectID = '
        + CONVERT(VARCHAR, @PROJECTID) + ')    
( ' + '''' + ISNULL(@PROJECTCONTROLNUMBER, ' ') + '''' + ' = ' + '''' + ' '
        + '''' + ' OR p.PROJECTCONTROLNUMBER LIKE ' + ''''
        + @PROJECTCONTROLNUMBER + '''' + ')    
AND    
( ' + '''' + ISNULL(@PROJECTNUMBER, ' ') + '''' + ' = ' + '''' + ' ' + ''''
        + ' OR P.PROJECTNUMBER LIKE' + '''' + @PROJECTNUMBER + ''''
        + ')    
AND    
 CONVERT(VARCHAR,P.PROJECTSTARTDATE,112) BETWEEN CONVERT(VARCHAR,@PROJECTLETTINGDATEFROM,112) AND CONVERT(VARCHAR,@PROJECTLETTINGDATETO,112)    
AND     
 Q.DIVISIONID IN ( ' + @DIVISIONLIST + ')'      
      
    EXEC sp_executesql @SQL,
        N'@ProjectID INT, @ProjectNumber varchar(50), @ProjectControlNumber varchar(50), @ProjectLettingDateFrom DATETIME, @ProjectLettingDateTo DATETIME,@DivisionList varchar(50)',
        @projectID, @ProjectNumber, @ProjectControlNumber,
        @ProjectLettingDateFrom, @projectLettingDateTo 
 
-- @LettingDateTo,@DivisionList    
    UPDATE  #tmp_data
    SET     TOTALBIDPRICE = TEMP2.TOTPRICE
    FROM    ( SELECT    SUM(TOTALPRICE) AS TOTPRICE ,
                        DIVISIONID ,
                        PRJID
              FROM      #tmp_data
              GROUP BY  PRJID ,
                        DIVISIONID
            ) TEMP2
    WHERE   #tmp_data.DIVISIONID = TEMP2.DIVISIONID
            AND #tmp_data.PRJID = TEMP2.PRJID    
    
    UPDATE  #tmp_data
    SET     SUMOFTOTALCOST = TEMP3.TOTCOST
    FROM    ( SELECT    SUM(TOTALCOST) AS TOTCOST ,
                        DIVISIONID ,
                        PRJID
              FROM      #tmp_data
              GROUP BY  PRJID ,
                        DIVISIONID
            ) TEMP3
    WHERE   #tmp_data.DIVISIONID = TEMP3.DIVISIONID
            AND #tmp_data.PRJID = TEMP3.PRJID    
    
    UPDATE  #tmp_data
    SET     MARGINPERCENT = CASE WHEN TOTALBIDPRICE > 0
                                 THEN ( ( TOTALBIDPRICE - SUMOFTOTALCOST
                                          - TOTALFREIGHTFORQUOTE )
                                        / TOTALBIDPRICE ) * 100
                                 ELSE 0
                            END    
    
    UPDATE  #tmp_data
    SET     MARKUPFORDIVISION = CASE WHEN SUMOFTOTALCOST > 0
                                     THEN ( ( TOTALBIDPRICE - SUMOFTOTALCOST
                                              - TOTALFREIGHTFORQUOTE )
                                            / SUMOFTOTALCOST ) * 100
                                     ELSE 0
                                END    
    
    INSERT  INTO #tmp_data1
            ( PROJID ,
              DIVID ,
              TFREIGHTFORQUOTE ,
              TBIDPRICE ,
              TCOST    
            )
            SELECT DISTINCT
                    PRJID ,
                    DIVISIONID ,
                    TOTALFREIGHTFORQUOTE ,
                    TOTALBIDPRICE ,
                    SUMOFTOTALCOST
            FROM    #tmp_data    
--SELECT * FROM #tmp_data1    
    
    UPDATE  #tmp_data
    SET     TOTALFREIGHT = TEMP1.TOTFREIGHT
    FROM    ( SELECT    SUM(TFREIGHTFORQUOTE) AS TOTFREIGHT ,
                        PROJID
              FROM      #tmp_data1
              GROUP BY  PROJID
            ) TEMP1
    WHERE   #tmp_data.PRJID = TEMP1.PROJID    
    
    UPDATE  #tmp_data
    SET     PROJECTTOTALBIDPRICE = TEMP4.TOTBIDPRICE
    FROM    ( SELECT    SUM(TBIDPRICE) AS TOTBIDPRICE ,
                        PROJID
              FROM      #tmp_data1
              GROUP BY  PROJID
            ) TEMP4
    WHERE   #tmp_data.PRJID = TEMP4.PROJID    
    
    UPDATE  #tmp_data
    SET     PROJECTTOTALCOST = TEMP5.PRJTOTCOST
    FROM    ( SELECT    SUM(TCOST) AS PRJTOTCOST ,
                        PROJID
              FROM      #tmp_data1
              GROUP BY  PROJID
            ) TEMP5
    WHERE   #tmp_data.PRJID = TEMP5.PROJID    
    
    UPDATE  #tmp_data
    SET     PRJMARGINPERCENT = CASE WHEN PROJECTTOTALBIDPRICE > 0
                                    THEN ( ( PROJECTTOTALBIDPRICE
                                             - PROJECTTOTALCOST - TOTALFREIGHT )
                                           / PROJECTTOTALBIDPRICE ) * 100
                                    ELSE 0
                               END    
    
    UPDATE  #tmp_data
    SET     PRJMARKUPPERCENT = CASE WHEN PROJECTTOTALCOST > 0
                                    THEN ( ( PROJECTTOTALBIDPRICE
                                             - PROJECTTOTALCOST - TOTALFREIGHT )
                                           / PROJECTTOTALCOST ) * 100
                                    ELSE 0
                               END    
    
    SELECT  *
    FROM    #tmp_data    
    
    
    
    
    

    
    
    
    


GO

Open in new window



Raj
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 357 total points
ID: 34994442
Two corrections made
1. Changed
 DECLARE @SQL VARCHAR(8000)    
to
 DECLARE @SQL nVARCHAR(4000)    
Sql string should be always either ntext/nvarchar/nchar.

2. Changed
 EXEC sp_executesql @SQL,
        N'ProjectID INT,
to
 EXEC sp_executesql @SQL,
        N'@ProjectID INT,

Raj
0
 

Author Comment

by:sqlcurious
ID: 34994570
still its not giving me any results


(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 357 total points
ID: 34994624
This could be because that SELECT statement is not having any result

PRINT that statement and execute the SELECT statement and check whether any results is there

Raj
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 143 total points
ID: 34996502
try this...

1) you weren't populating #tmp_data
2) the dynamic sql statement had several errors in it ... missing space, extra commas....
3) combine the update statements
4) what is the purpose of #tmp_data1?


5 .... tell us what errors are generated ....
     tell us what you are trying to do...

   show some sample data and results
USE [achtest]
GO

/****** Object:  StoredProcedure [dbo].[DBSP_Report_GetProjectDetailCost]    Script Date: 02/27/2011 12:42:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
    
 --select *from #tmp_data    
    
--exec DBSP_Report_GetProjectDetailCost @ProjectLettingDateFrom='01/01/2006',@ProjectLettingDateTo='01/01/2011'    
-- =============================================    
-- Create date: <01/21/2011>    
-- Description: <stored procedure to find total Project Costs>    
-- =============================================    
ALTER   PROCEDURE [dbo].[DBSP_Report_GetProjectDetailCost]    
(     
 --@ProjectID int = 0,    
 @ProjectNumber VARCHAR(50) = NULL,    
 @ProjectControlNumber VARCHAR(50)= NULL,    
 @ProjectLettingDateFrom DATETIME,    
 @ProjectLettingDateTo DATETIME, 
 @projectName varchar(50)=null,   
 @DivisionName VARCHAR(250) = NULL    
)     
AS     
set nocount on   
     
create  TABLE  #tmp_data    
 (    
   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)    
    ) 
       
 CREATE TABLE #tmp_data1    
(    
 PROJID BIGINT,    
 DIVID BIGINT,    
 TFREIGHTFORQUOTE DECIMAL(15,4),    
 TBIDPRICE DECIMAL(15,4),    
 TCOST DECIMAL(15,4)    
)    
  
    
 INSERT INTO #tmp_data    
(    
 PRJID,     
 PROJECTNAME,    
 CONTROLNUMBER,    
 COUNTYID,    
 PROJECTNUMBER,    
 STARTDATE,    
 ITEMNUMBER,    
 ITEMDESC,    
 QUANTITY,    
 UNIT,    
 --UNITCOST,    
 UNITPRICE,    
 TOTALCOST,    
 TOTALPRICE,    
 MARKUPPERCENT,    
 DIVISIONID,    
 DIVISIONNAME,    
 COUNTYNAME,    
 QUOTID,    
 TOTALFREIGHTFORQUOTE     
)      
   SELECT     
P.ProjectID AS PRJID,    
P.Projectname AS PROJECTNAME,    
P.ProjectControlNumber AS CONTROLNUMBER,    
P.ProjectCountyID as countyID,    
P.ProjectNumber,    
P.ProjectStartDate as startdate,    
QI.QuoteItem_ItemNumber AS ItemNumber,    
QI.QuoteItemDescription As ItemDesc,    
QI.QuoteItemQuantity As Quantity,    
QI.QuoteItemUnit As Unit,    
QI.QuoteItemUnitprice As UnitPrice,    
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,       
--QI.QuoteItem_ItemTotalPrice As TotalPrice,    
(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS TotalPrice,    
QI.QuoteItemMarkupPercent AS MarkupPercent,    
Q. DivisionId AS DIVISIONID,    
D.Divisionname AS DIVISIONNAME,    
C.CountyName AS COUNTYNAME,    
Q.QuoteID AS QUOTEID,    
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3) AS TOTALFREIGHTFORQUOTE    
    
FROM TBL_Project P     
    
inner    join   TBL_Quotes1 Q ON P.ProjectID = Q.ProjectID    
inner    JOIN TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID    
left outer  join    TBL_Division D ON D.createdby = Q.quotecreatedby    
inner    join TBL_County C ON P.ProjectCountyID = C.CountyID    
    
where P.projectid>0
and p.projectstartdate>=@Projectlettingdatefrom
and p.projectstartdate<=@projectlettingdateto
    
    
UPDATE #tmp_data 
   SET TOTALBIDPRICE = TEMP2.TOTPRICE 
      ,SUMOFTOTALCOST = TEMP2.TOTCOST 
 FROM #tmp_data
 inner join
      (     
SELECT SUM(TOTALPRICE) AS TOTPRICE
      ,SUM(TOTALCOST) AS TOTCOST
     , DIVISIONID, PRJID 
  FROM #tmp_data 
 GROUP BY PRJID,DIVISIONID) TEMP2     
on #tmp_data .DIVISIONID = TEMP2.DIVISIONID 
 AND #tmp_data.PRJID = TEMP2.PRJID 
 
  
UPDATE #tmp_data SET 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       
 
    
INSERT INTO #tmp_data1    
(    
 PROJID,    
 DIVID,    
 TFREIGHTFORQUOTE,    
 TBIDPRICE,    
 TCOST    
)    
SELECT DISTINCT PRJID,DIVISIONID,TOTALFREIGHTFORQUOTE,TOTALBIDPRICE,SUMOFTOTALCOST 
  FROM #tmp_data    
--SELECT * FROM #tmp_data1    
    
UPDATE #tmp_data 
   SET TOTALFREIGHT = TEMP1.TOTFREIGHT 
      ,PROJECTTOTALBIDPRICE = TEMP1.TOTBIDPRICE
      ,PROJECTTOTALCOST = TEMP1.PRJTOTCOST
  FROM #tmp_data
 inner join (SELECT SUM(TFREIGHTFORQUOTE) AS TOTFREIGHT
              ,SUM(TBIDPRICE) AS TOTBIDPRICE
              ,SUM(TCOST) AS PRJTOTCOST
              ,PROJID 
          FROM #tmp_data1 
         GROUP BY PROJID) TEMP1    
on #tmp_data.PRJID = TEMP1.PROJID      
 
    
UPDATE #tmp_data 
   SET PRJMARGINPERCENT =     
     CASE WHEN PROJECTTOTALBIDPRICE > 0
          THEN ((PROJECTTOTALBIDPRICE - PROJECTTOTALCOST - TOTALFREIGHT) / PROJECTTOTALBIDPRICE ) * 100     
          ELSE   0
          end 
 , PRJMARKUPPERCENT =     
     CASE WHEN PROJECTTOTALCOST > 0 
          THEN ((PROJECTTOTALBIDPRICE - PROJECTTOTALCOST - TOTALFREIGHT) / PROJECTTOTALCOST ) * 100     
          ELSE 0    
          end    
    
SELECT * FROM #tmp_data    

return    
GO

Open in new window

0
 

Author Comment

by:sqlcurious
ID: 34997877
the main problem is my #temp_data is not populating.I tried to print the query inside the select statement.It is giving me the results.
Whatever columns that i have created in the #temp_data ,i want to populate it all.How to do that.

Based on the results of #tmp_Data and tmp_data1,i need to prepare the reports
0
 

Author Closing Comment

by:sqlcurious
ID: 35112423
All tried but couldnot get the exact solution
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

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…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

12 Experts available now in Live!

Get 1:1 Help Now