SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 qry help -
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1392 Posts

Posted - 09/20/2010 :  06:41:59  Show Profile  Reply with Quote
I have a table

uservisits

id
name
datetime
visit


now i want to select all users where visit=1 and there was no visit=2 after that visit of 1 for todays date

what's the best way to do this?

basically i want the result to return me all visits for today where tehre is a 1 and not a 2 - but there can be mulitple 1 and 2's on the same date. (and in that case if the last 1 doesn't have a 2 then it should come up in the results)

PavanKK
Starting Member

India
32 Posts

Posted - 09/20/2010 :  07:45:30  Show Profile  Reply with Quote
Hope this helps

---------

DECLARE @T TABLE (ID INT IDENTITY(1,1),Name CHAR(1),VDate DATETIME DEFAULT GETDATE(),Visit INT)
INSERT @t (Name,Visit) VALUES('a','1')
INSERT @t (Name,Visit) VALUES('b','1')
INSERT @t (Name,Visit) VALUES('a','2')
INSERT @t (Name,Visit) VALUES('c','1')
--INSERT @t VALUES('c','loc1')
INSERT @t (Name,Visit) VALUES('c','2')
INSERT @t (Name,Visit) VALUES('d','1')
INSERT @t (Name,Visit) VALUES('e','1')
INSERT @t (Name,Visit) VALUES('f','1')


SELECT T.Name,T.VDate,T.Visit
FROM @T T
JOIN (
SELECT Name,CAST(VDate AS DATE) VD
FROM @T
GROUP BY Name,CAST(VDate AS DATE)
HAVING COUNT(*)=1
)S ON T.Name = S.Name AND CAST(T.VDate AS DATE) = S.VD AND T.Visit = 1

-----


KK
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 09/20/2010 :  07:49:18  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Maybe this:

SELECT
	uv.[name] AS [Visitor Name]
	, MAX(uv.[dateTime]) AS [Visit Date]
	, 1 AS [visit]
FROM
	userVisits AS uv
WHERE
	uv.[visit] = 1
	AND NOT EXISTS (
		SELECT 1
		FROM
			userVisits AS uv2
		WHERE
			uv2.[name] = uv.[name]
			AND uv2.[dateTime] > uv.[dateTime]
			AND uv2.[visit] = 2
		)
GROUP BY
	uv.[name]

But a few points......

1) Doesn't look like a good normalised table. [name] should probably be a foreign key to some unique person identity value somewhere else

2) WHY WHY WHY do you have a column CALLED dateTime ??????

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Edited by - Transact Charlie on 09/20/2010 07:50:27
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000