Multi Criteria Same Record - 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

General SQL Server Forums

New to SQL Server Programming

Multi Criteria Same Record

Author

Topic

matixsnow
Starting Member

6 Posts

Posted - 2009-09-10 : 23:12:48

This is sooo easy, but I cannot figure it out. How do I write the SQL code to satisfy the criteria below.

Criteria: A Customer should only be returned if the Customer has at least 1 Account Date < 2007 and no other Account Dates > 2007.

Customer, Account Date
1, 2008
2, 2006
1, 2005
2, 2001
3, 2003
3, 2006

The query should return:
Customer, Account Date
2, 2006
2, 2001
3, 2003
3, 2006

Customer 1 does have an Account < 2007, but has an Account > 2007. Therefore, Customer 1 is not returned. The other Customers are returned b/c they satisfy the criteria.

Any thoughts?

Thanks,
Jay

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-10 : 23:19:13

using SQL 2005 ?


declare @sample table
(
	[Customer]	int, 
	[Account Date]	int
)
insert into @sample
select	1, 2008	union all
select	2, 2006	union all
select	1, 2005	union all
select	2, 2001	union all
select	3, 2003	union all
select	3, 2006

select	*
from	@sample s
where	2007 > all (select [Account Date] from @sample x where x.[Customer] = s.[Customer])

/*
Customer    Account Date 
----------- ------------ 
2           2006
2           2001
3           2003
3           2006

(4 row(s) affected)
*/

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

matixsnow
Starting Member

6 Posts

Posted - 2009-09-10 : 23:41:48

I am using a back end program called AQT off a DB2 database. I would imagine there is a simpler way of doing this though...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-10 : 23:50:07

then you should be posting over at a DB2 forum not a MS SQL Server forum.

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

matixsnow
Starting Member

6 Posts

Posted - 2009-09-10 : 23:53:29

well how bout access then. i can make it work from there.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-11 : 00:04:53

[code]
declare @sample table
(
[Customer] int,
[Account Date] int
)
insert into @sample
select 1, 2008 union all
select 2, 2006 union all
select 1, 2005 union all
select 2, 2001 union all
select 3, 2003 union all
select 3, 2006

select *
from @sample s
where exists
(select * from @sample x where x.[Customer] = s.[Customer] and x.[Account Date] < 2007)
and not exists
(select * from @sample x where x.[Customer] = s.[Customer] and x.[Account Date] > 2007)
[/code]

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

matixsnow
Starting Member

6 Posts

Posted - 2009-09-11 : 00:30:54

My table is called Customers and I wrote the code following your response as below:

SELECT *
FROM Customers
WHERE EXISTS
(SELECT * FROM Customers x WHERE x .[Cust_ID] = s.[Cust_ID] and x.[Close_Date] <2007)
AND NOT EXISTS
(SELECT * FROM Customers x WHERE x .[Cust_ID] = s.[Cust_ID] and x.[Close_Date] >2007)

I still get an error. Any suggestions please? Thanks for your help so far!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-11 : 00:31:57

what's the error message ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-11 : 00:32:32

[code]
SELECT *
FROM Customers s
WHERE EXISTS
(SELECT * FROM Customers x WHERE x.[Cust_ID] = s.[Cust_ID] and x.[Close_Date] <2007)
AND NOT EXISTS
(SELECT * FROM Customers x WHERE x.[Cust_ID] = s.[Cust_ID] and x.[Close_Date] >2007)
[/code]

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

matixsnow
Starting Member

6 Posts

Posted - 2009-09-11 : 00:38:51

Still giving me invalid use of '.''!'or'()' in expression x .[Cust_ID] = s.[Cust_ID] and x.[Close_Date] <200

Strange.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-11 : 00:41:20

i am not familiar with access syntax. is it because of the space after the x before the dot ?

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

matixsnow
Starting Member

6 Posts