comparison two tables - 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)

comparison two tables

Author

Topic

inversions
Starting Member

5 Posts

Posted - 2007-12-16 : 17:41:39

Hi,

i need some help with creating a query that would let me select the top 10 between a specific date period and then the top 10 from a previous date range and then displays those that have risen in rank equal to or grater than 2 places.

for example:


table search

SearchTerm	NumSearches	Date

dog		20		2007-10-01	
food		23		2007-10-01
music		56		2007-10-02
dog		30		2007-10-02
ball		85		2007-10-03
calender	69		2007-10-05
heart		87		2007-10-09
baby		45		2007-10-09
money		52		2007-10-09
fish		74		2007-10-10
smile		85		2007-10-10
heart		30		2007-10-10
dog		50		2007-10-10
ball		96		2007-10-11


calender	20		2007-11-11
food		23		2007-11-12
fish		74		2007-11-13
smile		85		2007-11-10
money		52		2007-11-16
heart		87		2007-11-14
baby		45		2007-11-13
money		46		2007-11-19
fish		78		2007-11-18
smile		40		2007-11-19
dog		50		2007-11-20
ball		114		2007-11-21
music		186		2007-11-17
doctor		205		2007-11-29

select the top 10 by num of searches (numSearches) for two date ranges e.g. 2007-10-01 to 2007-10-31 (month of october) and 2007-11-01 to 2007-11-30(month of november) and identify those that have risen in rank by 2 or more places. i.e. comparing the month of november to that of october.

so the output would be as follows:

ie for top 10 for month 10 (oct . 2007-10-xx )


Rank	SearchTerm	NumSearches	
	
1	ball		181
2	heart		117
3	dog		100
4	smile		85
5	fish		74
6	calender	69
7	music		56
8	money		52
9	baby		45
10	food		23

with the top 10 for month 11 (nov. 2007-11-xx)
	
Rank	SearchTerm	NumSearches

1	doctor		205
2	Music		186
3	fish		152	
4	smile		133
5	ball		114		
6	money		98
7	heart		87
8	dog		50
9	baby		45
10	food 		23

	
so the output showing those that have risen in rank by => 2 is

Rank	SearchTerm	NumSearches

2	music		186
3	fish		152
6	money		98

any help you could give would be most welcome.

thanks,
-Inversions

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-16 : 18:22:56

TOp 10 from a month (in this case October)
[Code]
Select a.[Rank],a.SearchTerm,a.NumSearches
From (Select Year([datecolumn]) as YR,month,[datecolumn]),SearchTerm, NumSearches over (partition by NumSearches order by NumSearches Desc as [Rank]
FROM [Table] ) a
WHERE [Rank] <=10 and YR = 2007 and MO = 10

Something like this for the last part, perhaps.


Select a.[Rank],a.SearchTerm,a.NumSearches
From  (Select Year([datecolumn]) as YR,month,[datecolumn]),SearchTerm, NumSearches over (partition by NumSearches order by NumSearches Desc as [Rank]
       FROM [Table] WHERE [datecolumn] between '11/1/2007' and '11/30/2007') a 
INNER JOIN
Select b.[Rank] as THisRank, a.Rank as LastRank,b.SearchTerm,b.NumSearches as ThisMonthSearches, a.NumSearches as LastMonthSearches
From  (Select Year([datecolumn]) as YR,month,[datecolumn]),SearchTerm, NumSearches over (partition by NumSearches order by NumSearches Desc as [Rank]
       FROM [Table] WHERE [datecolumn] between '10/1/2007' and '10/31/2007') b
on a.SearchTerm = b.SearchTerm

WHERE b.[Rank] - a.[Rank] >=2
Order by b.[Rank] desc

[/code]


[Code]


Poor planning on your part does not constitute an emergency on my part.

matty
Posting Yak Master

161 Posts

Posted - 2007-12-16 : 23:42:18

SELECT B.Rank,B.SearchTerm,B.NumSearches
FROM
(SELECT TOP 10 SearchTerm,SUM(NumSearches) AS NumSearches,DENSE_RANK() OVER(ORDER BY SUM(NumSearches) DESC )AS Rank
FROM Search
WHERE Date between '2007-10-01' and '2007-10-31'
GROUP BY SearchTerm) A
JOIN
(SELECT TOP 10 SearchTerm,SUM(NumSearches) AS NumSearches,DENSE_RANK() OVER(ORDER BY SUM(NumSearches) DESC )AS Rank
FROM Search
WHERE Date between '2007-11-01' and '2007-11-30'
GROUP BY SearchTerm)B
ON A.SearchTerm = B.SearchTerm
WHERE A.Rank-B.Rank >=2
ORDER BY 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources