Need help with Query

[ScubaAddict]

I have a membership database.  Some people have signed up twice (by accident).  They get billed every 30 days.  If they signed up twice, they are charged twice.  If they signed up twice, they will have done it in the same 24 hour period.

 

The SQL I have figured out so far is this (payments.total is not important):

 

SELECT payments.date, payments.total, users.username from payments INNER JOIN users ON payments.user_id = users.id WHERE (payments.membership_id = 7 or payments.membership_id = 6) ORDER BY payments.user_id, payments.date

 

Gives me a table like below:

 

2016-12-20 05:29:13   4.99   user1

2017-01-19 13:27:12   4.99   user1

2016-12-20 14:34:49   4.99   user2

2017-01-19 13:28:01   4.99   user2

2017-01-19 13:59:40   4.99   user2

2016-12-21 16:28:24   4.99   user3

2017-01-20 13:43:15   4.99   user3

2017-02-19 15:25:46   4.99   user3

2016-12-21 16:38:12   4.99   user4

2017-01-20 13:49:59   4.99   user4

2017-02-20 15:38:52   4.99   user4

2017-02-20 13:49:59   4.99   user4      

 

Notice most dates for each user are 30 days(ish) apart, except for user2 on 1-19 and user2 on 2-20 - they have both been charged twice on the same day.  I need to alter the SQL to only select records where a single user has 2 dates (payments.date) within 24 hours of each other.  

 

So I am looking for it to show:

 

2017-01-19 13:28:01   4.99   user2

2017-01-19 13:59:40   4.99   user2

2017-02-20 15:38:52   4.99   user4

2017-02-20 13:49:59   4.99   user4

 

I am killing myself here - can someone help me?

 

THANK YOU!!

Edited February 23, 2017 by ScubaAddict

[Barand]

Work with just the date parts of the fields using DATE(payments.date)

SELECT DATE(date) as date
  , userid
  , COUNT(*) as tot
FROM payments
GROUP BY user_id, date
HAVING tot > 1

[benanamen]

You need to create a unique constraint on whatever identifies the users. They should not be allowed to sign up more than once with the same parameters.

[ScubaAddict]

MY FIRST POST may not have been clear, but I can't edit it.  I am looking to identify users already in my database that have more then one payment record within a 24 hour time period.

 

 

Hi Barand - thank you for the reply!  I am a newbie - but I think I am not understanding your reply.  I have altered my SQL (incorporating what I think you were implying) - as your exact SQL produced no results: 

 

SELECT DATE(payments.date) as date, users.username, COUNT(*) as tot from payments INNER JOIN users ON payments.user_id = users.id WHERE (payments.membership_id = 7 or payments.membership_id = 6) GROUP BY payments.user_id, date HAVING tot > 1

 

Produced no results.  If I remove the HAVING clause, I get exactly what I had with a tot column that has '1' in every cell.

 

I didn't see anything in your SQL that deciphered records within 24 hours from those outside 24 hours apart for the same user.  I am a newbie, so I assume I am missing something you were trying to get me to do.

 

Benanamen - I am looking for a way to identify those who have already signed up, not preventing future ones.  But thank you for the reply.

Edited February 23, 2017 by ScubaAddict

[Barand]

Sorry, careless with my column aliases

 

Try

SELECT DATE(payments.date) as dateportion
, users.username
, COUNT(*) as tot 
from payments 
INNER JOIN users ON payments.user_id = users.id 
WHERE payments.membership_id IN (6,7) 
GROUP BY payments.user_id, dateportion 
HAVING tot > 1

[ScubaAddict]

Barand - that looks like it worked!  Thank you so much for helping me!  Thank you! thank you! thank you!

[Barand]

Benanamen - I am looking for a way to identify those who have already signed up, not preventing future ones.  But thank you for the reply.

Benanamen's point was that you need to stop this happening future, so you don't have to keep repeating this exercise.

[ScubaAddict]

Oh yes, I understand what he was telling me - and I totally agree!  I was just looking to identify the ones that already passed through.  That is, solving both situations is necessary, this post was to help me with one of those problems.