Retrieve Last Date subscribed - 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)

Retrieve Last Date subscribed

Author

Topic

Shamee_1321
Starting Member

15 Posts

Posted - 2008-02-28 : 07:35:15

Hello,
I have a table that keeps track of users subscription and unsubscription. If a user has unsubscribed, i need to retrieve the last time the user has subscribed given the field creationdate??????
e.g
User1 Subscribed 2008/02/20
User2 Subscribed 2008/02/20
User1 Unsubscribed 2008/02/23
User1 Subscribed 2008/02/27
User2 Subscribed 2008/02/27
User1 Unsubscribed 2008/02/28
Please help!!!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-28 : 07:39:37

[code]Select max(CreationDate) as LastSubscriptionDate
from Table
Where user = @user and status = 'Subscribed' and creationdate < @creationdate
[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-28 : 07:43:14

If you want it for each user

Select username,max(CreationDate) as LastSubscriptionDate
from Table
group by username

Madhivanan

Failing to plan is Planning to fail

Shamee_1321
Starting Member

15 Posts

Posted - 2008-02-28 : 08:03:45

thx a lot guys... it has helped me to find a soln to my query..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources