Select records where code 'A' is used in combination with 'B' or 'C' on the same day

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

SQL Server Development (2000)

Select records where code 'A' is used in combination with 'B' or 'C' on the same day

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-12 : 09:15:50

Vichet writes "Table1 contains fields: user, code, and date

Users can use many codes for any given day. I need all users who used code 'A' in combination with code 'B' or 'C' on the same day from 1/1/2000 to 12/31/2001"

izaltsman
A custom title

1139 Posts

Posted - 2002-02-12 : 09:30:19

I think something like this will work:


SELECT t1.[user] 
FROM 
   Table1 t1
   INNER JOIN Table1 t2 
      ON (t1.[user] = t2.[user] 
          and convert(varchar, t1.[date], 112) = convert(varchar, t2.[date], 112) 
	  AND t1.code = 'A' AND t2.code IN ('B', 'C'))

or this:



SELECT t1.[user] 
FROM 
	Table1 t1
WHERE 
	t1.code = 'A'
	AND EXISTS (SELECT * FROM Table1 t2 WHERE t1.[user] = t2.[user] 
                    and convert(varchar, t1.[date], 112) = convert(varchar, t2.[date], 112) 
		    AND t2.code IN ('B', 'C'))

Edited by - izaltsman on 02/12/2002 09:33:52

shine maker
Starting Member

1 Post

Posted - 2002-02-13 : 13:30:16

Thanks for your help izaltsman.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources