Function convert to procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Function convert to procedure?

Author

Topic

silver_surfer21
Starting Member

21 Posts

Posted - 2011-03-08 : 07:06:28

I have a function which I'm using to examine the number of houses sold in a month, and if its less than ten group it in with the next month until the total is greater than ten, before finding an average price across all the months.

USE [MyHousePrice]
GO
/****** Object:  UserDefinedFunction [dbo].[ExtendedMonthsCreation]    Script Date: 03/08/2011 11:33:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[ExtendedMonthsCreation](@NoSoldInMonth int, @InitialMonth int, @InitialYear int,@currentHouseType nvarchar(50),@CurrentPostout nvarchar(50))
RETURNS @table table
(

TotalNoSold INT,
InitialMonth INT,
InitialYear INT,
EndingMonth INT,
EndingYear INT,
Postout nvarchar(50),
HouseType nvarchar(50),
AvgSellingPrice money

)
AS
BEGIN

DECLARE @CurrentMonth int
DECLARE @CurrentYear int
DECLARE @TotalAverage money



SET @CurrentMonth = @InitialMonth
SET @CurrentYear = @InitialYear




WHILE (@NoSoldInMonth < 10)
BEGIN;

SET @CurrentMonth = @CurrentMonth+1

IF @currentMonth=13
     BEGIN
     SET @currentMonth = 1
     SET @currentYear = @currentYear+1
     END


SELECT @NoSoldInMonth = COUNT(*), @TotalAverage = AVG(decPrice) FROM Postcode_Datesplit WHERE PostOut = @CurrentPostout  AND((@InitialMonth <=MONTH(Application_Date) AND MONTH(Application_DATE) <= @CurrentMonth AND YEAR(Application_Date) = @InitialYear) OR (MONTH(Application_Date) <=@CurrentMonth AND @InitialYear < YEAR(Application_Date) AND YEAR(Application_Date) <= @CurrentYear))




END;

INSERT INTO @TABLE(TotalNoSold,
InitialMonth,
InitialYear,
EndingMonth,
EndingYear,
Postout,
HouseType,
AvgSellingPrice)

SELECT @NoSoldInMonth,@InitialMonth,@InitialYear,@CurrentMonth,@CurrentYear,@CurrentPostout,@CurrentHouseType,@TotalAverage

RETURN


END

The code I'm using to call the function is as follows:


DELETE ExtendedPeriodsTable

INSERT INTO ExtendedPeriodsTable([TotalNoSold],Postout,HouseType,[Begin_Period_Year],[Begin_Period_Month],[End_Period_Year],[End_Period_Month],Avg_Monthly_Price)
SELECT query1.TotalNoSold,
query1.Postout,
query1.HouseType,
query1.InitialYear,
query1.InitialMonth,
query1.EndingYear,
query1.EndingMonth,
query1.AvgSellingPrice FROM (select TOP 100
	HouseData.Postout AS Unnecesary, s.*
from 
	HouseData 
cross apply 
dbo.ExtendedMonthsCreation(HouseData.NoOfHouses, HouseData.Month, HouseData.Year,HouseData.HouseType,HouseData.Postout) s WHERE NoOfHouses <10 AND PreviouslyUsedFlag IS NULL) AS query1

The format of the final data in the ExtendedPeriodsTable is:

[TotalNoSold]
      ,[Postout]
      ,[HouseType]
      ,[Begin_Period_Year]
      ,[Begin_Period_Month]
      ,[End_Period_Year]
      ,[End_Period_Month]
      ,[Avg_Monthly_Price]
  FROM [MyHousePrice].[dbo].[ExtendedPeriodsTable]

The problem is that I am getting rows in ExtendedPeriodsTable which begin on say January 2010 and end on March 2010 all within one group, but then the next group begins in February 2010 and ends on March 2010. I want to use some kind of flag to ensure that a month is only put in one group, and that the next group contains only months which have not previously been grouped, i.e April etc...

As this is the first Function I've written I did so without realising I couldn't use an 'UPDATE' from within it, as I had originally intended. I've now discovered that the only way to use an update is to convert the function to a procedure, however I've tried that and that leaves me unable to call the procedure in the way I need.

lappin
Posting Yak Master

182 Posts

Posted - 2011-03-08 : 10:04:05

You can start with a simple GROUP BY to get totals for each month

CREATE View y1 AS

SELECT select month([DateColumn])as [MnthID]

,count(Sales) as Num
FROM [dbo].[ATestTable]

group by month([DateColumn])

Then Join the View to itself by joining the month's Num to the next month's Num, and add the Num from the two months if it is <10

Select y3.*,CASE when Num < 10 then (Num+(Select Num from (SELECT y1.[MnthID]

,count(Sales) as Num
FROM y1

group by [MnthID]) y2 where y3.[MnthID]=y2.[MnthID]+1)) else Num end as combined from (

SELECT y1.[MnthID]

,count(Sales) as Num,
FROM y1

group by [MnthID]

)as y3

you could repeat this logic a few times using more subqueries and CASE statements.

lappin
Posting Yak Master

182 Posts

Posted - 2011-03-08 : 10:06:05

Sorry meant: Join the View to itself by joining the month's MonthID to the next month's MonthID

silver_surfer21
Starting Member

21 Posts

Posted - 2011-03-08 : 10:43:20

Thanks, that looks like a simpler way of going about things than mine...

I've tried it and it almost seems to work but then it comes up with

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Here's the code I'm using

DROP VIEW y1
CREATE View y1 AS

SELECT month as [MnthID],Year,Postout,HouseType

,sum(NoOfHouses) as Num
FROM [dbo].[HouseData]

group by Postout,year,Month,HouseType

/*Then Join the View to itself by joining the month's Num to the next month's Num, and add the Num from the two months if it is <10 */

Select y3.*,CASE when Num < 10 then (Num+(Select Num from (SELECT y1.[MnthID]

,sum(num) as Num
FROM y1

group by Postout,Year,[MnthID],HouseType) y2 where y3.[MnthID]=y2.[MnthID]+1)) else Num end as combined from (

SELECT y1.[MnthID]

,sum(num) as Num
FROM y1

group by Postout,Year,[MnthID],HouseType

)as y3

/*you could repeat this logic a few times using more subqueries and CASE statements. */

I suspect the problem is something to do with the fact that I've had to add a few more fields to the 'group by's' but I could be wrong

lappin
Posting Yak Master

182 Posts

Posted - 2011-03-09 : 06:42:16

I suspect the problem is in this line:
group by Postout,Year,[MnthID],HouseType) y2 where y3.[MnthID]=y2.[MnthID]+1

Possibly change to:
group by Postout,Year,[MnthID],HouseType) y2 where y3.[MnthID]=y2.[MnthID]+1 and y3.HouseType=y2.HouseType and y3.Year=y2Year

this is not going to work across years - i.e. Dec to Jan

Probably better to use "Month(DATEADD(month,-1,DateColumn))" to to join view to previous month's data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources