Help with SQL Query - 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 2005 Forums

Transact-SQL (2005)

Help with SQL Query

Author

Topic

enaran
Starting Member

8 Posts

Posted - 2009-10-28 : 07:54:03

Hi all,

I have been asked to extract some data from a rather large databse of bookings the we have entered for our 38 different Caravan Parks situated around the country.
Specifically I have been requested to provide the top 10 most common postcodes of guests who have stayed at each property.
So far I have been able to extract the top 10 most common postcodes, but only for one property at a time.
I would love to be able to do it for all properties at the same time (but as individual entities) so that I dont have to re-run it 38 times.

My query thus far is

SELECT TOP (10) dbo.GUEST.POSTCODE, COUNT(*) AS TotalStays, dbo.Properties.Property_Id
FROM dbo.RES INNER JOIN
dbo.CATEGORY ON dbo.RES.CAT_ID = dbo.CATEGORY.CAT_ID INNER JOIN
dbo.Properties ON dbo.CATEGORY.Property_Id = dbo.Properties.Property_Id INNER JOIN
dbo.GSTRES ON dbo.RES.RES_ID = dbo.GSTRES.RES_ID INNER JOIN
dbo.GUEST ON dbo.GSTRES.GUEST_ID = dbo.GUEST.GUEST_ID
WHERE (dbo.RES.RES_ID <> dbo.RES.GRPRES_ID) AND (dbo.RES.STATUS < 4) AND (dbo.RES.Arrive BETWEEN CONVERT(DATETIME, '2008-10-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-09-30 00:00:00', 102)) AND (dbo.GUEST.POSTCODE <> ' ') AND (dbo.Properties.Property_Id = 1)
GROUP BY dbo.GUEST.POSTCODE, dbo.Properties.Property_Id
ORDER BY TotalStays DESC

Which results in

Postcode,TotalStays,Property_Id
4740 506 1
4680 289 1
4720 273 1
4670 219 1
4810 171 1
4717 161 1
4870 148 1
4814 125 1
4655 115 1
4817 108 1

I would like to achieve

Postcode,TotalStays,Property_Id
4740 506 1
4680 289 1
4720 273 1
4670 219 1
4810 171 1
4717 161 1
4870 148 1
4814 125 1
4655 115 1
4817 108 1
5000 275 2
7000 224 2
7248 74 2
7250 68 2
3001 52 2
4000 51 2
2000 50 2
7310 47 2
1114 44 2
7001 41 2
7250 68 3
7310 59 3
7320 35 3
7908 21 3
7315 19 3
7316 19 3
7307 18 3
7011 18 3
7015 16 3
7248 16 3
etc...

Thank you

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-28 : 08:04:26

SELECT t1.POSTCODE,t1.Property_Id,t1.TotalStays
FROM
(

SELECT dbo.GUEST.POSTCODE
, COUNT(*) AS TotalStays
, dbo.Properties.Property_Id
, [Rank] = RANK() OVER(Partition by dbo.GUEST.POSTCODE, dbo.Properties.Property_Id order by count(*) desc)
FROM dbo.RES INNER JOIN
dbo.CATEGORY ON dbo.RES.CAT_ID = dbo.CATEGORY.CAT_ID INNER JOIN
dbo.Properties ON dbo.CATEGORY.Property_Id = dbo.Properties.Property_Id INNER JOIN
dbo.GSTRES ON dbo.RES.RES_ID = dbo.GSTRES.RES_ID INNER JOIN
dbo.GUEST ON dbo.GSTRES.GUEST_ID = dbo.GUEST.GUEST_ID
WHERE (dbo.RES.RES_ID <> dbo.RES.GRPRES_ID) AND (dbo.RES.STATUS < 4) AND (dbo.RES.Arrive BETWEEN CONVERT(DATETIME, '2008-10-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-09-30 00:00:00', 102)) AND (dbo.GUEST.POSTCODE <> ' ') AND (dbo.Properties.Property_Id = 1)
GROUP BY dbo.GUEST.POSTCODE, dbo.Properties.Property_Id
) t1

WHERE Rank <= 10
ORDER BY Property_Id asc,TotalStays DESC

Jim

Everyday I learn something that somebody else already knew

enaran
Starting Member

8 Posts

Posted - 2009-10-28 : 08:21:11

Hi JimF

Thank you for your help.

I assume I would have to remove the "AND (dbo.Properties.Property_Id = 1)" from the select clause...

However I get a Syntax error from the SQL Mangement studio saying "The OVER SQL construct or statement is not supported."

Any ideas?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-10-28 : 08:50:44

OVER not supported?

What version of SQL SERVER are you running ? Also, is your database compatibility level set to 90(i think) or better?

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.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

enaran
Starting Member

8 Posts

Posted - 2009-10-28 : 09:24:06

My Mistake,

Got it to run but it is returning the top * from each property so I modified the statement to include the Rank in the output and every record seems to be ranked no 1?

SELECT t1.POSTCODE,t1.Property_Id, t1.TotalStays, t1.Rank
FROM
(

SELECT dbo.GUEST.POSTCODE
, COUNT(*) AS TotalStays
, dbo.Properties.Property_Id
, [Rank] = RANK() OVER(Partition by dbo.GUEST.POSTCODE, dbo.Properties.Property_Id order by count(*) desc)
FROM dbo.RES INNER JOIN
dbo.CATEGORY ON dbo.RES.CAT_ID = dbo.CATEGORY.CAT_ID INNER JOIN
dbo.Properties ON dbo.CATEGORY.Property_Id = dbo.Properties.Property_Id INNER JOIN
dbo.GSTRES ON dbo.RES.RES_ID = dbo.GSTRES.RES_ID INNER JOIN
dbo.GUEST ON dbo.GSTRES.GUEST_ID = dbo.GUEST.GUEST_ID
WHERE (dbo.RES.RES_ID <> dbo.RES.GRPRES_ID) AND (dbo.RES.STATUS < 4) AND (dbo.RES.Arrive BETWEEN CONVERT(DATETIME, '2008-10-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-09-30 00:00:00', 102)) AND (dbo.GUEST.POSTCODE <> ' ')
GROUP BY dbo.GUEST.POSTCODE, dbo.Properties.Property_Id
) t1

WHERE Rank <= 10
ORDER BY Property_Id asc,TotalStays DESC

Output
Postcode, property_id, TotalStays, Rank

4740 1 506 1
4680 1 289 1
4720 1 273 1
4670 1 219 1
4810 1 171 1
4717 1 161 1
4870 1 148 1
4814 1 125 1
4655 1 115 1
4817 1 108 1
4350 1 106 1
4715 1 100 1
4745 1 99 1
4702 1 97 1
4744 1 79 1
4211 1 72 1
4718 1 66 1
4551 1 66 1
etc...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-28 : 10:06:01

That would mean that the combination of PropertyID and PostCode is Unique. Remove the Postcode from the Partition by piece of the Rank() function and see if that's what you want.

Jim

Everyday I learn something that somebody else already knew

enaran
Starting Member

8 Posts

Posted - 2009-10-28 : 10:27:28

Thank you - all working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources