tring to figure this query out

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 2000 Forums

Transact-SQL (2000)

tring to figure this query out

Author

Topic

gmerideth
Starting Member

10 Posts

Posted - 2004-07-06 : 18:21:24

I have a table holding item preference data with a uniqueID and a last access time. Like

table myTable ( id uniqueidentifier, lastdate datetime )

Now. I'm trying to get a query where I get back a list of id's and a day value (using datediff) only where the date is past a certain point.

Something like this:
select id, datediff(day,lastdate ,getdate()) AS days from myTable

however I do not want back the whole list ( some 200k items ) what I want back is only the items that are past 30 days since last access.

Something like:
select id, datediff(day,lastdate ,getdate()) AS days from myTable WHERE days>30

but that doesn't work of course. I've tried making this a procedure using a #temp table but I get back an error indicating that: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

So whats an easy way to do this? Here's the procedure I'm using just in case I'm on the right path but got something very wrong in execution.

CREATE PROCEDURE ftSelectOldPreferences ( @pNumberofDays numeric(9,0) )
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #tmpHolder ( GUID varchar(40), days numeric(9,0) )
INSERT INTO #tmpHolder ( GUID, days )
SELECT (SELECT uniqueID FROM ftPreferences),
(SELECT datediff(day,lastaccess,getdate()) AS days FROM ftPreferences)
SELECT GUID, days FROM #tmpHolder WHERE days>30 ORDER BY GUID ASC
END

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-06 : 18:25:42

[code]select id, datediff(day,lastdate ,getdate()) AS days from myTable WHERE lastdate > getdate() - 30[/code]