Return rows from table - 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 2012 Forums

Transact-SQL (2012)

Return rows from table

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-06-08 : 05:57:24

Hi,

I have a table


CREATE TABLE [dbo].[Payments](
	[id] [int] NULL,
	[paymentsum] [int] NULL,
	[fromdate] [date] NULL,
	[untildate] [date] NULL,
	[customerid] [int] NULL
) ON [PRIMARY]. 

id	paymentsum	fromdate	untildate	customerid
1	0	2009-02-20	2014-02-20	90345
2	0	2010-04-12	2013-02-20	90345
3	100	2011-08-24	2011-09-24	90345
4	100	2008-09-30	2013-02-09	90345

I need to find customers that have paymentsum=100 and that have no untildate>getdate. If a customer does have one row for 100 where untildate>100 then I don't need to show that.

Any thoughts? Should I do it with EXISTS?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-08 : 13:57:47

do you mean this?


SELECT *
FROM Payments p
WHERE paymentsum = 100
AND untildate < getdate()
AND NOT EXISTS(SELECT 1
               FROM payments
               WHERE customerid = p.customerid
               AND paymentsum = 100
               AND untildate > getdate()          
              )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources