Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    7

    Unanswered: Calculating value for stored procedure based on values from another sp

    I am writing a stored procedure that returns one row per week. One of the columns I need to return is a value that is calculated based on several columns returned in a stored procedure that has a date as an input parameter. I wrote a lovely function that takes in a date parameter, calls the stored procedure, does the calculation and returns the value, only to find out that I cannot do an EXEC within a function. I need other ideas.

    Example of main stored procedure result set:
    Division varchar,
    Date datetime,
    Amount float
    Sample Data:
    Location1, 1/4/09, 4000
    Location1, 1/11/09, 5000
    Location1, 1/18/09, 3400
    Location2, 1/4/09, 2000
    Location2, 1/11/09, 2500
    Location2, 1/18/09, 4300
    I need to add a column for the calculated value.

    Calculated value comes from stored procedure 2:
    Input parameters - Division, date
    Output result set
    Division varchar,
    JobNumber varchar,
    ContractAmount float,
    HistoricalContractAmount float,
    ForecastAmount float,
    HistoricalForecastAmount float,
    JobToDateAmount float,
    HistoricalJobToDateAmount

    Based on that result set, I need to calculate a value for each JobNumber and then sum over Division (which is not the same as calculating the value over the sums by Division). That value is what I need on each row of my Main sp's result set.

    Any ideas on how I can do this? Let me know if more information is need.

    TIA!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I had a similar experience. So instead of being able to use my function, I rewrote it as a lovely stored procedure. Or is this solution too easy?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Really you want sprocs to be the end, not one step in the chain of objects. Best path depends on what the sproc does - wanna post the code?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2006
    Posts
    7
    I would turn the function into a sproc but I can't really figure out how to then call it for every row of the main sproc.

    I realize having a chain of sprocs is not the best solution. I have a whole bunch of sprocs for a whole bunch of reports that I am creating. On this particular report, I have to show a value that is calculated on another report (from a different dataset). Thus, I am trying to use that other report's sproc to calculate the value.

    Sproc that has values need to calculate my required value:
    Code:
    create procedure rpt_HistoricalGrossProfitByJob 
    	@Division varchar(25) = null,
    	@SalesPerson varchar(15) = null,
    	@HistoricalDate datetime = null,
    	@CurrentDate datetime = null
    as
    
    declare @lDivision varchar(25);
    declare @lSalesPerson varchar(15);
    declare @lHistoricalDate datetime;
    declare @lCurrentDate datetime;
    
    set @lDivision = @Division;
    set @lSalesPerson = @SalesPerson;
    set @lHistoricalDate = @HistoricalDate;
    set @lCurrentDate = @CurrentDate
    
    if @lDivision = '' set @lDivision = null;
    
    if @lSalesPerson = '' set @lSalesPerson = null;
    
    if @lHistoricalDate is null 
    	Set @lHistoricalDate = (select dateadd(d,-1,cast(cast(month(max(monthenddate)) as varchar) + '/01/' + cast(year(max(monthenddate)) as varchar) as datetime)) from lcf_historicalwip);
    
    if @lCurrentDate is null
    	Set @lCurrentDate = (select max(monthenddate) from lcf_historicalwip);
    
    select
    	Crnt.Division as Divisions,
    	Crnt.Salesrep as Salesperson,
    	Crnt.JobNumber,
    	Crnt.JobName,
    	Crnt.Contract,
    	Crnt.ForecastCost,
    	Crnt.JTDCost,
    	Crnt.ProgressBillings,
    	isnull(Hist.Contract,0) as HistContract,
    	isnull(Hist.ForecastCost,0) as HistForecastCost,
    	isnull(Hist.JTDCost,0) as HistJTDCost,
    	isnull(Hist.ProgressBillings,0) as HistProgressBillings
    from LCF_HistoricalWIP Crnt
    	left join LCF_HistoricalWIP Hist on Crnt.JobNumber = Hist.JobNumber and Hist.MonthEndDate = @lHistoricalDate
    where Crnt.MonthEndDate = @lCurrentDate
    and Crnt.Division = coalesce(@lDivision,Crnt.Division)
    and Crnt.Salesrep = coalesce(@lSalesPerson,Crnt.Salesrep)
    Code for function that won't work:
    Code:
    create function rpt_GPEarnedAmount (
    	@Division varchar(25),
    	@Date datetime
    ) returns float
    as
    begin
    
    declare @returnValue float;
    set @returnValue = 0;
    
    declare @GPTable TABLE (
    	Division varchar(25),
    	Salesperson varchar(25),
    	JobNumber varchar(25),
    	JobName varchar(50),
    	Contract float,
    	ForecastCost float,
    	JTDCost float,
    	ProgressBillings float,
    	HistContract float,
    	HistForecastCost float,
    	HistJTDCost float,
    	HistProgressBillings float
    )
    
    declare @HistoricalDate datetime
    set @HistoricalDate = dateadd(d,-1,cast(cast(month(@CurrentDate) as varchar) + '/01/' + cast(year(@CurrentDate) as varchar) as datetime));
    
    insert into @GPTable
    exec rpt_HistoricalGrossProfitByJob @Division, null, @HistoricalDate, @Date
    
    declare @GPPercentTable TABLE (
    	Division varchar(25),
    	Contract float,
    	PercentComplete float,
    	HistContract float,
    	HistPercentComplete float
    )
    
    insert into @GPPercentTable
    select Division,
    	Contract,
    	case when JTDCost < 0 and JTDCost <> ForecastCost then 0
    		when ForecastCost = 0 and JTDCost = 0 then 1
    		when ForecastCost = 0 then 1
    		else JTDCost/ForecastCost
    	end,
    	HistContract,
    	case when HistJTDCost < 0 and HistJTDCost <> HistForecastCost then 0
    		when HistForecastCost = 0 and HistJTDCost = 0 then 1
    		when HistForecastCost = 0 then 1
    		else HistJTDCost/HistForecastCost
    	end
    from @GPTable
    
    declare @RevenueTable TABLE (
    	Division varchar(25),
    	Earned float
    )
    	
    insert into @RevenueTable
    select Division,
    	(Contract * PercentComplete) - (HistContract * HistPercentComplete)
    from @GPPercentTable
    
    set @returnValue = (select sum(Earned) from @RevenueTable group by Division)
    
    return @returnValue
    end
    And here is the code for the new report sproc:
    Code:
    create procedure rpt_FlashWeekly2 (
    	@pDivision varchar(25) = null,
    	@pStartDate datetime = null,
    	@pEndDate datetime = null
    ) as
    
    declare @Division varchar(25);
    declare @StartDate datetime;
    declare @EndDate datetime;
    
    set @Division = @pDivision;
    set @StartDate = @pStartDate;
    set @EndDate = @pEndDate;
    
    if @StartDate is null set @StartDate = cast('01/01/' + cast(year(getdate()) as varchar) as datetime)
    if @EndDate is null set @EndDate = dbo.rpt_GetDatePart(getdate())
    
    select Division, case when datepart(dw, Date) <> 1 then 0 else datepart(wk,Date) - 1 end as Week, Date, BookingAmount, PISalesAmount, PIBudgetAmount, MaintAmount, MaintBudgetAmount, BLExpectedContract, BLBilledAmount, BLBacklog from (
    	select d.Division as Division, d.Date as Date,
    		sum(isnull(b.Amount,0)) over (partition by d.Division, case when dbo.rpt_GetEndofWeekDate(d.Date) < dbo.rpt_GetMonthEndDate(d.Date) then dbo.rpt_GetEndofWeekDate(d.Date) else dbo.rpt_GetMonthEndDate(d.Date) end) as BookingAmount,
    		sum(isnull(p.Amount,0)) over (partition by d.Division, case when dbo.rpt_GetEndofWeekDate(d.Date) < dbo.rpt_GetMonthEndDate(d.Date) then dbo.rpt_GetEndofWeekDate(d.Date) else dbo.rpt_GetMonthEndDate(d.Date) end) as PISalesAmount,
    		bd.PIBudget as PIBudgetAmount,
    		sum(isnull(m.Amount,0)) over (partition by d.Division, case when dbo.rpt_GetEndofWeekDate(d.Date) < dbo.rpt_GetMonthEndDate(d.Date) then dbo.rpt_GetEndofWeekDate(d.Date) else dbo.rpt_GetMonthEndDate(d.Date) end) as MaintAmount,
    		bd.MaintBudget as MaintBudgetAmount,
    		isnull(bl.ExpectedContract,0) as BLExpectedContract, 
    		isnull(bl.BilledAmount,0) as BLBilledAmount, 
    		isnull(bl.Backlog,0) as BLBacklog
    	from
    		( select d.Division, dt.Date 
    		from (select distinct Division from LCF_BacklogHistory) as d
    			cross join LCF_DateList as dt
    		where dt.Date between @StartDate and @EndDate) as d
    	left join
    		(	SELECT	dbo.rpt_LCFCentralDivision(JC00102.Divisions) as Division,
    					dbo.rpt_GetDatePart(LCF_JobContractHistory.Date) as Date,
    					SUM(LCF_JobContractHistory.ContractAmount) as Amount
    			FROM	LCF_JobContractHistory
    					INNER JOIN JC00102 ON LCF_JobContractHistory.WS_Job_Number = JC00102.WS_Job_Number
    			GROUP BY	dbo.rpt_LCFCentralDivision(JC00102.Divisions),dbo.rpt_GetDatePart(LCF_JobContractHistory.Date)
    		) as b on d.Division = b.Division and d.Date = b.Date
    	left join
    		(	SELECT	dbo.rpt_LCFCentralDivision(JC00102.Divisions) as Division,
    				dbo.rpt_GetDatePart(JC20501.GLPOSTDT) as Date,
    				sum(case when JC20501.DOCTYPE = '1' then JC20501.Billed_Amount_TTD else JC20501.Billed_Amount_TTD * -1 end) as Amount
    			FROM JC20501
    				JOIN JC00102 on JC20501.WS_Job_Number = JC00102.WS_Job_Number
    			GROUP BY dbo.rpt_LCFCentralDivision(JC00102.Divisions), dbo.rpt_GetDatePart(JC20501.GLPOSTDT)
    		) as p on d.Division = p.Division and d.Date = p.Date
    	left join 
    		(	select Division, Date, Sum(Amount) as Amount
    			from (
    				SELECT 	dbo.rpt_LCFCentralDivision(SV00300.Divisions) as Division,
    						dbo.rpt_GetDatePart(RM20101.GLPOSTDT) as Date,
    						SUM(SV00701.Billable_All- SV00701.Billable_Tax) as Amount
    				from SV00701
    					LEFT OUTER JOIN SV00300 ON SV00701.Service_Call_ID = SV00300.Service_Call_ID
    					LEFT OUTER JOIN RM20101 ON SV00701.Call_Invoice_Number = RM20101.DOCNUMBR
    				group by dbo.rpt_LCFCentralDivision(SV00300.Divisions),dbo.rpt_GetDatePart(RM20101.GLPOSTDT)
    				union
    				select 	dbo.rpt_LCFCentralDivision(SV00500.Divisions) as Division,
    						dbo.rpt_GetDatePart(RM20101.GLPOSTDT) as Date,
    						sum(CASE SV00564.RMDTYPAL WHEN '7' THEN SV00564.Billable_Subtotal*-1 ELSE SV00564.Billable_Subtotal END)  as Amount
    				from SV00564
    					LEFT OUTER JOIN SV00500 ON SV00564.Contract_Number = SV00500.Contract_Number and SV00564.ADRSCODE = SV00500.ADRSCODE
    					LEFT OUTER JOIN RM20101 ON SV00564.RMDNUMWK = RM20101.DOCNUMBR
    				group by dbo.rpt_LCFCentralDivision(SV00500.Divisions),dbo.rpt_GetDatePart(RM20101.GLPOSTDT)
    			) as t
    			group by Division, Date
    		 ) as m on d.Division = m.Division and d.Date = m.Date
    	left join 
    		(	SELECT Division as Division,
    				dbo.rpt_GetDatePart(PostDate) as Date,
    				ExpectedContract,
    				BilledAmount,
    				BackLog
    			FROM LCF_BacklogHistory
    		) as bl on d.Division = bl.Division and d.Date = bl.Date
    	join 
    		(	SELECT Division,
    			PIBudget,
    			MaintBudget
    			from LCF_Budget
    			where Year = year(@EndDate)
    		) as bd on d.Division = bd.Division
    ) as main
    where Division = coalesce(@Division,Division)
    	and (datepart(dw,Date) = 1 or datepart(d,dateadd(d,1,Date)) = 1)
    These sprocs are doing a lot of other stuff, so hopefully you can figure out the relevent parts.
    Last edited by scarlett; 09-15-09 at 10:51.

  5. #5
    Join Date
    Sep 2006
    Posts
    7
    I was able to turn my function into a sproc using a while loop over a date range. But then I got a Cannot nest INSERT EXEC error when I tried to use that sproc in the Main sproc. I ended up just putting all of the calculations in the main sproc.

    Thanks for looking!

  6. #6
    Join Date
    Dec 2007
    Posts
    11
    I am not quite sure is it correct. But why dont you consider creating a table valued function for your rpt_HistoricalGrossProfitByJob instaed of a procedure so as to call it from your second function rpt_GPEarnedAmount ?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by scarlett
    I was able to turn my function into a sproc using a while loop over a date range. But then I got a Cannot nest INSERT EXEC error when I tried to use that sproc in the Main sproc. I ended up just putting all of the calculations in the main sproc.

    Thanks for looking!
    Looping over a date range?

    Sounds like you could use a calendar table...
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •