help with 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 query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-27 : 05:20:35

Hi,

I have 2 tables, one called "tblUserDetails" and one called "tblMessages"

I'm trying to run a query that returns all users that havent been members for longer than a 10 days, and have high message counts.

I'm not sure the best way to incorporate "WHERE count(*) FROM tblMessage > 50" into this query. Any help is much appreicated.

Thanks!
mike123



select top 100 * from tbluserdetails UD 
....
WHERE (DateDiff(dd, date, GetDate()) < 10 )



CREATE TABLE [dbo].[tblMessage](
	[MessageID] [int] IDENTITY(1,1) NOT NULL,
	[MessageFrom] [int] NOT NULL,
	[MessageTo] [int] NOT NULL,
	[Message] [varchar](1500) NULL,
	[prevMessage] [varchar](500) NULL,
	[Subject] [varchar](50) NULL,
	[date] [smalldatetime] NULL,
	[Checked] [tinyint] NULL
	)

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 05:26:56

[code]
SELECT TOP 100 UD.UserIDColumn, [MessageCount] = COUNT(*)
FROM tbluserdetails AS UD
JOIN tblMessage AS M
ON M.MessageFrom = UD.UserIDColumn
WHERE DateDiff(Day, DateJoined, GetDate()) < 10
GROUP BY UD.UserIDColumn
ORDER BY [MessageCount] DESC
[/code]
or you could add a

HAVING COUNT(*) > 100

to get all folk who have sent more than 100 messages

Edit: Typo
Re-Edit: Another Typo

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-27 : 05:31:17

[code]where Datejoined > dateadd(day, datediff(day, 0, getdate()), -10)[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-27 : 05:34:08

quote:
Originally posted by Kristen

SELECT 100 UD.UserIDColumn, [MessageCount] = COUNT(*)
FROM tbluserdetails AS UD
JOIN tblMessage AS M
ON M.MessageFrom = UD.UserIDColumn
WHERE DateDiff(Day, DateJoined, GetDate()) < 10
GROUP BY UD.UserIDColumn
ORDER BY [MessageCount] DESC

or you could add a

HAVING COUNT(*) > 100

to get all folk who have sent more than 100 messages

Kristen

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 05:41:27

Thanks Madhi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 06:37:21

SELECT 100 what?

SELECT TOP 50	m.MessageFrom AS SenderID
		COUNT(*) AS Messages
FROM		tblMessage AS m
INNER JOIN	tblUserDetails AS ud ON ud.UserID = m.MessageFrom
WHERE		ud.Date >= DATEADD(DAY, DATEDIFF(DAY, 10, CURRENT_TIMESTAMP), 0)
GROUP BY	m.MessageFrom
ORDER BY	COUNT(*) DESC

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 06:41:02

"SELECT 100 what?"

Well, that will be the TOP ones

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-27 : 06:44:48

Looks like you better off at testing than coding

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts