CODEWITHSUNDEEP
sqldateemail
matrix8369
matrix8369

Reputation: 71

SQL - Group by email and most current date

I have a SQL query that is supposed to show me each customers last payment but it's showing me every payment all customer have made. I have spent a day on this and I am about to give up. I want to try and group my report by the customer email and then out of each group of customerpayments return each groups most recent date. This should show me each customers most recent payment in a single report.

I am pulling in information from multiple tables. Customers could in theory have the same name, number, or be paying the same amount. But the unique field in this would be the email. A customer could upgrade their account as well, so a group of payments made by a customer might not all be the same values. So if we return the most recent date it would show their current last payment.

Help me stackoverflow, your my only hope.

SELECT 
    AccountTypes.Name AS AccountType, 
    CONVERT(nvarchar, PaymentHistory.DateCreated, 23) AS Prev_Billing_Date,
    PaymentHistory.Amount AS Amount, 
    Users.FirstName AS FName, 
    Users.LastName AS LName, 
    Users.Phone AS Phone, 
    Users.Email AS Email
FROM 
    AccountTypes 
INNER JOIN 
    Accounts 
INNER JOIN 
    AccountStatus ON Accounts.StatusID = AccountStatus.ID 
INNER JOIN 
    PaymentHistory ON Accounts.ID = PaymentHistory.AccountID 
            ON AccountTypes.ID = Accounts.AccountTypeID 
INNER JOIN 
    Users 
INNER JOIN 
    UserAccounts ON Users.ID = UserAccounts.UserID 
            ON Accounts.ID = UserAccounts.AccountID
WHERE 
    (UserAccounts.IsOwner = 1) AND 
    (AccountStatus.Name = 'Paid') AND 
    (Accounts.AccountTypeID = 2 OR 
     Accounts.AccountTypeID = 3 OR 
     Accounts.AccountTypeID = 4 OR 
     Accounts.AccountTypeID = 5)
GROUP BY
    AccountTypes.Name, 
    PaymentHistory.DateCreated,
    PaymentHistory.Amount, 
    Users.FirstName, 
    Users.LastName, 
    Users.Phone, 
    Users.Email
ORDER BY 
    AccountType DESC, Email, Prev_Billing_Date DESC;

Upvotes: 1

Views: 111

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13016

You can use row_number() and partition by to achieve what you want. You don't need to use GROUP BY because you are not using any aggregate functions.

SELECT t1.* FROM 
    (SELECT 
        AccountTypes.Name AS AccountType, 
        CONVERT(nvarchar, PaymentHistory.DateCreated, 23) AS Prev_Billing_Date,
        PaymentHistory.Amount AS Amount, 
        Users.FirstName AS FName, 
        Users.LastName AS LName, 
        Users.Phone AS Phone, 
        Users.Email AS Email,
        ROW_NUMBER() over (PARTITION BY Users.Email ORDER BY PaymentHistory.DateCreated DESC) as rn
    FROM AccountTypes 
    INNER JOIN Accounts ON AccountTypes.ID = Accounts.AccountTypeID 
    INNER JOIN AccountStatus ON Accounts.StatusID = AccountStatus.ID 
    INNER JOIN PaymentHistory ON Accounts.ID = PaymentHistory.AccountID                     
    INNER JOIN UserAccounts ON Accounts.ID = UserAccounts.AccountID
    INNER JOIN Users ON Users.ID = UserAccounts.UserID      
    WHERE 
        UserAccounts.IsOwner = 1 AND 
        AccountStatus.Name = 'Paid' AND 
        Accounts.AccountTypeID IN (2,3,4,5)
    ) t1
WHERE t1.rn = 1
ORDER BY 
    t1.AccountType DESC, t1.Email, t1.Prev_Billing_Date DESC;

Upvotes: 1

Related Questions