SQL get users within range

Question

I've got a database that stores when users subscribed and unsubscribed from a service. What I want to do is see who unsubscribed each month, and then see how many of those people had unsubscribed within 30 days of subscribing. I have two fields, DateJoined_ and DateUnsub_ that both return a smalldatetime. How would I be able to find these people using DateJoined and DateUnsub? I know I have to do some sort of calculation, and I could do this easily if I wasn't using SQL - any suggestions?

What have you tried? What *can* you do in SQL? What particular difficulties do you have implementing this in SQL? — Andriy M, Jun 23 '11 at 16:45
To be honest my biggest problem is that I don't have much knowledge of the library and didn't even know what to look for >.> — Hani Honey, Jun 23 '11 at 16:56
What you will need apart from DATEDIFF is: [how to truncate a timestamp by a specific date/time part](http://stackoverflow.com/questions/2639051/what-is-the-best-way-to-truncate-a-date-in-sql-server) (because you'll need to group data by month); [how to group and how to aggregate data](http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server); [how to aggregate conditionally](http://stackoverflow.com/questions/1288058/conditional-count-on-a-field) (for it seems like you want to know the total number of users unsubscribed as well as the number of those unsubscribed within 30 days). — Andriy M, Jun 23 '11 at 17:26

Dustin Laine · Accepted Answer · 2011-06-23T16:46:18.810

2

SELECT *
FROM UserTable
WHERE DATEDIFF(day, DateJoined, DateUnSub) <= 30

http://msdn.microsoft.com/en-us/library/ms189794.aspx

edited Jun 23 '11 at 16:46

answered Jun 23 '11 at 16:41

Dustin Laine

37,935
10
86
125

score 1 · Answer 2 · answered Jun 23 '11 at 16:40

1

What DBMS are you using? For MySQL:

select * from table where DATEDIFF(DateUnsub_, DateJoined_) <= 30

answered Jun 23 '11 at 16:40

Christopher Armstrong

7,907
2
26
28

I believe the `datepart` argument is required. – Dustin Laine Jun 23 '11 at 16:42
1

In MySQL there are only two parameters: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff – Christopher Armstrong Jun 23 '11 at 16:43
1

Ahh, OP did not specify. My apologies. – Dustin Laine Jun 23 '11 at 16:46
I'm not entirely sure which i'm using...sounds silly, I know. I'm working in a .aspx file, using C#, and connecting to Lyris. – Hani Honey Jun 23 '11 at 16:48
@Dustin - No apology necessary.. I didn't know what DBMS he was using either. – Christopher Armstrong Jun 23 '11 at 17:07

score 1 · Answer 3 · answered Jun 23 '11 at 16:50

As for getting the number of users who unsubscribed each month, you could GROUP BY DATEPART(year, DateUnsub_), DATEPART(month, DateUnsub_) or instead limit on those dateparts to get the list of users.

http://msdn.microsoft.com/en-us/library/ms174420.aspx

SQL get users within range

3 Answers3