Select duplicate charges made on same card same day 1

[lifelineamerica]

I am trying to locate all records paid by the same card (last 4 digits were stored and column name is last_4) on the same date (column is called date_made, and is in the date_time format)

SELECT *
FROM res_reservations a
INNER JOIN res_reservations b
ON a.last_4=b.last_4
WHERE a.date_made LIKE 'b.date_made%'

... and sadly I get zero results when there should be thousands..

Please help!

 

[feherke]

Hi

If I understand you correctly :

[b]select[/b]
last_4[teal],[/teal]date[teal]([/teal]date_made[teal]),[/teal]count[teal](*)[/teal]

[b]from[/b] res_reservations

[b]group[/b] [b]by[/b] last_4[teal],[/teal]date[teal]([/teal]date_made[teal])[/teal]

[b]having[/b] count[teal](*)>[/teal][purple]1[/purple][teal];[/teal]

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]

 

[lifelineamerica]

Thanks a lot Feherke.
I guess I have to add some conditions in there, I can't just go by that group.
Many people's last 4 digits of cards match up. So additionally, I detect real last_4 duplicates then either by their last names being the same, or by their phone number being the same or by their ip address being the same. So How do I add these conditions in there?

 

[feherke]

Hi

So How do I add these conditions in there?

Just enumerate them in the [tt]group by[/tt] clause.

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]

 

[lifelineamerica]

Like how? please post an example.
last_4 and date have to be the same.
But then ONLY ONE not all of the others have to be the same.
So if the names and the phone numbers then don't match but the ipaddress does match good enough for me.
Or if the ipaddress and the phone numbers don't match but the last names do match good enough for me

You get the idea.

 

[feherke]

Hi

But then ONLY ONE not all of the others have to be the same.

Oops, I misunderstood you. Then [tt]group by[/tt] will not help.

Try this, it seems to work for my sample data :

[b]select[/b]
last_4[teal],[/teal]date[teal]([/teal]date_made[teal]),[/teal]count[teal](*)[/teal]

[b]from[/b] res_reservations

[b]group[/b] [b]by[/b] last_4[teal],[/teal]date[teal]([/teal]date_made[teal])[/teal]

[b]having[/b] count[teal](*)>[/teal][purple]1[/purple]
[b]and[/b] [teal]([/teal]count[teal]([/teal][b]distinct[/b] lastname[teal])=[/teal][purple]1[/purple] [b]or[/b] count[teal]([/teal][b]distinct[/b] phone[teal])=[/teal][purple]1[/purple] [b]or[/b] count[teal]([/teal][b]distinct[/b] ipaddress[teal])=[/teal][purple]1[/purple][teal])[/teal]

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]

 

[lifelineamerica]

Thanks that did it. Another star for you!