Loop through query results - SQL Server Forums

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)

Loop through query results

Author

Topic

silver_surfer21
Starting Member

21 Posts

Posted - 2011-03-03 : 09:19:17

Hi,

I'm trying to construct a query which will take the results of a query which provides the number of houses of a particular type, in a particular postcode sold in a particular month, of a particular year - see below

SELECT @NoSoldInMonth = NoOFHouses, @currentMonth = Query1.Month FROM (SELECT Postout, HouseType, Year(Application_Date) AS Year, MONTH(Application_Date) AS Month, AVG(decPrice) AS AveragePrice, MIN(decPrice) AS MinPrice, MAX(decPrice) AS MaxPrice, COUNT(*) AS NoOfHouses FROM dbo.Postcode_Datesplit GROUP BY YEAR(Application_Date),MONTH(Application_Date), Postout, HouseType) AS Query1 WHERE Query1.NoOFHouses <10

I then want to run through a loop for each line of results from the above query, because if a particular group does not contain 10 or more houses sold I want to merge it with the following group and so on until the merged groups *do* contain 10 or more houses sold, in order to provide a more accurate average.

Here's the code for the loop

WHILE (@NoSoldInMonth < 10)
BEGIN;


    INSERT INTO [MyHousePrice].[dbo].[TempTable]
           ([ID]
           ,[decPrice]
           ,[Application_Date]
           ,[Postcode]
           ,[HouseType]
           ,[NewBuild]
           ,[LeaseStatus]
           ,[SAON]
           ,[PAON]
           ,[Thoroughfare]
           ,[Locality]
           ,[Town]
           ,[District]
           ,[County]
           ,[Easting]
           ,[Northing]
           ,[strAddress]
           ,[PostOut]
           ) SELECT [ID]
           ,[decPrice]
           ,[Application_Date]
           ,[Postcode]
           ,[HouseType]
           ,[NewBuild]
           ,[LeaseStatus]
           ,[SAON]
           ,[PAON]
           ,[Thoroughfare]
           ,[Locality]
           ,[Town]
           ,[District]
           ,[County]
           ,[Easting]
           ,[Northing]
           ,[strAddress]
           ,[PostOut] FROM Postcode_DateSplit WHERE (MONTH(Application_Date) = @currentMonth) AND @currentHouseType = HouseType AND (YEAR(Application_Date) = @currentYear OR (YEAR(Application_DATE) = (@CurrentYear+1) AND MONTH(Application_Date)=1)) AND PreviouslyUsedFlag IS NULL

SELECT @NoSoldInMonth = COUNT(*) FROM Postcode_DateSplit WHERE (MONTH(Application_Date) = @currentMonth) AND @currentHouseType = HouseType AND (YEAR(Application_Date) = @currentYear OR (YEAR(Application_DATE) = (@CurrentYear+1) AND MONTH(Application_Date)=1)) AND PreviouslyUsedFlag IS NUll
SELECT @currentPostout = [postout] FROM Postcode_DateSplit WHERE (MONTH(Application_Date) = @currentMonth) AND @currentHouseType = HouseType AND (YEAR(Application_Date) = @currentYear OR (YEAR(Application_DATE) = (@CurrentYear+1) AND MONTH(Application_Date)=1)) AND PreviouslyUsedFlag IS NUll

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


UPDATE Postcode_DateSplit SET PreviouslyUsedFlag = 'Yes'  WHERE (MONTH(Application_Date) = @currentMonth) AND @currentHouseType = HouseType AND (YEAR(Application_Date) = @currentYear OR (YEAR(Application_DATE) = (@CurrentYear+1) AND MONTH(Application_Date)=1))

END;

I'm concerned that the current code is essentially running two seperate unrelated queries and not doing what I'm trying to achieve.
I'm only really used to using simple SELECT statements in SQL.

Any help is greatly appreciated.
Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-03-03 : 09:53:27

Any chance of sample input & sample matching results?
See the FAQ's on how to post something we can use to re-create your problem.

silver_surfer21
Starting Member

21 Posts

Posted - 2011-03-03 : 10:39:15

Here is a sample record from the view Postcode_Datesplit

ID	decPrice	Application_Date	Postcode	HouseType	NewBuild	LeaseStatus	SAON	PAON	Thoroughfare	Locality	Town	District	County	Easting	Northing	strAddress	PostOut	PreviouslyUsedFlag
2	76995.00	2000-06-26	CV16 6GD	D	Y	F	10	NULL	BADMINTON CLOSE	NULL	WALSGRAVE	NULL	WALSGRAVE	518655	468981	8 BADMINTON CLOSE,  WALSGRAVE,  COVENTRY, YO16 6GD	YO16	NULL

This data gets aggregated into a table called HouseData using the following code

SELECT Postout, HouseType, Year(Application_Date) AS Year, MONTH(Application_Date) AS Month, AVG(decPrice) AS AveragePrice, MIN(decPrice) AS MinPrice, MAX(decPrice) AS MaxPrice, COUNT(*) AS NoOfHouses INTO HouseData FROM dbo.Postcode_Datesplit GROUP BY YEAR(Application_Date),MONTH(Application_Date), Postout, HouseType ORDER BY HouseType, PostOut, YEAR, Month

giving a sample record as so:

Postout	HouseType	Year	Month	AveragePrice	MinPrice	MaxPrice	NoOfHouses
AB1	NULL	2006	1	30000.00	30000.00	30000.00	1

What I want to do is say - for reach row of HouseData where NoOfHouses <10 then include all the houses in the same group from the following month as well, and check if the NoOfHouses is then >= 10. If so then find the average price of houses sold during those two months rather than just the first one, and if not then go on to look at the 3rd following month and so on.

Apologies for my previous explanation not being very clear, I hope this is better

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-03-04 : 10:16:11

Follow the "How to ask" link in my sig for help in providing DDL & sample data and output.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources