Last active
March 19, 2016 00:25
-
-
Save xuru/afc372b143c4f859416f to your computer and use it in GitHub Desktop.
sql shit
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
This is just an example of our current db and number of dups | |
*/ | |
SELECT | |
MobilePhone, count(MobilePhone) | |
FROM TerminalMemberTable | |
WHERE IsActive = 1 AND LastTransaction IS NOT NULL | |
GROUP BY MobilePhone | |
HAVING count(MobilePhone) > 1 | |
ORDER BY count(MobilePhone) DESC | |
/* | |
512xxxxxxx 4 | |
... | |
5125555555 3 // Bob Anderson | |
*/ | |
/* | |
So... | |
For row in rows | |
Any other rows with row[MobilePhone]? | |
Consolidate user information from those rows | |
Any other rows with row[email]? | |
Consolidate user information from those rows | |
I now should have all user information available and the card numbers that user has. | |
*/ | |
select MemberCardNumber, MemberName, MemberSurname, MobilePhone, HomePhone, EmailAddress | |
from TerminalMemberTable | |
where IsActive = 1 and LastTransaction is not NULL | |
AND TerminalMemberTable.MobilePhone = '5125555555' | |
/* | |
99999990005278 bob anderson 5125555555 [email protected] | |
99999900136201 Bob Anderson 5125555555 5125556666 | |
99999999005269 BOB ANDERSON 5125555555 | |
*/ | |
select MemberCardNumber, MemberName, MemberSurname, MobilePhone, HomePhone, EmailAddress | |
from TerminalMemberTable | |
where IsActive = 1 and LastTransaction is not NULL | |
AND TerminalMemberTable.EmailAddress = '[email protected]' | |
/* | |
99999990005278 bob anderson 5125555555 [email protected] | |
99999990005561 [email protected] | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment