SQL Server 2005 Query remove duplicates via date

I searched and searched and can't seem to figure out this issue: We have three tables which have data I need to collect and show in a view.

SELECT
    C.FirstName, C.LastName,
    aspnet_Membership.LoweredEmail,
    MAX(Bill.Code) AS BCodes,
    MAX(Bill.BillDate)
FROM 
    dbo.Client C
INNER JOIN 
    dbo.Bill ON C.Id = Bill.BId
INNER JOIN 
    dbo.aspnet_Membership ON aspnet_Membership.UserId = C.UserGUID
WHERE
   ((Bill.Code='ASDF'
     OR Bill.Code='XYZ'
     OR Bill.Code='QWE'
     OR Bill.Code='JKL')
    AND C.LastName!='Unassigned')
GROUP BY 
    LastName, FirstName, LoweredEmail, Code, BDate

Client table has: FirstNam开发者_开发技巧e LastName and UserGuid

Bill table has: BCode, BillDate

aspnet_Membership table has: E-mail, UserId

RESULTS:

FirstName   LastName   E-mail              BCode    BillDate
FName1      Lname1      fname@isp.com       XYZ      2010-05-13 00:00:00.000
Fname2      Lname2      fname2@isp2.com     XYZ      2010-06-05 00:00:00.000
Fname2      Lname2      fname2@isp2.com     ASD      2008-09-17 12:01:45.407

As you can see Fname2 shows up twice, only difference is in the BCode and BillDate.

How can I make this go with the latest date so I get Fname2 record with Bcode of XYZ with date of 2010-06-05.

Any help would be appreciated, thank you in advance.

Seeing that you're using SQL Server 2005, I would probably use a CTE (Common Table Expression) to do this - something like:

;WITH MyData AS
(
    SELECT
        c.FirstName, c.LastName,
        asp.LoweredEmail,
        b.Code AS BCodes, b.BillDate,
        ROW_NUMBER() OVER (PARTITION BY c.LastName,c.FirstName 
                           ORDER BY BillDate DESC) AS 'RowNum'
FROM 
    dbo.Client c
INNER JOIN 
    dbo.Bill b ON C.Id = b.BId
INNER JOIN 
    dbo.aspnet_Membership asp ON asp.UserId = c.UserGUID
WHERE
    b.Code IN ('ASDF', 'JKL', 'QWE', 'XYZ')
    AND c.LastName != 'Unassigned'
)
SELECT 
    FirstName, LastName, LoweredEmail, BCodes, BillDate
FROM
    MyData
WHERE
    RowNum = 1

This CTE with the ROW_NUMBER() clause will:

• "partition" your data by (FirstName,LastName) - each pair of those values gets a new sequential "row number"
• order those values within each partition by descending BillDate

So the resulting set of data has each newest entry for any (FirstName,LastName) group with RowNum = 1 - and that's the data I'm selecting from that CTE.

Does that work for you??

Perform a second join (using a LEFT JOIN) to find a later row in Bill table, and then filter any results where that join succeeds:

SELECT
C.FirstName, C.LastName,
aspnet_Membership.LoweredEmail,
MAX(Bill.Code) AS BCodes,
MAX(Bill.BillDate)

FROM dbo.Client C

INNER JOIN dbo.Bill
ON C.Id=Bill.BId

INNER JOIN dbo.aspnet_Membership
ON aspnet_Membership.UserId=C.UserGUID

LEFT JOIN dbo.Bill b2
ON Bill.BId = b2.BId and
   b2.Code in ('ASDF','XYZ','QWE','JKL') and
   b2.BDate > Bill.BDate

WHERE
b2.BId is null and
((Bill.Code='ASDF'
OR Bill.Code='XYZ'
OR Bill.Code='QWE'
OR Bill.Code='JKL')
AND C.LastName!='Unassigned')
GROUP BY LastName, FirstName, LoweredEmail, Code, BDate