qry help - - 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)

qry help -

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-09-20 : 06:41:59

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

32 Posts

Posted - 2010-09-20 : 07:45:30

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 07:49:18

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources