How not to repeat data in this query

Question

I did this query, which will compose a view.

SELECT DISTINCT 
     US.EMPLOYEESTATUS
    ,US.EMPLOYEEID
    ,US.EMPLOYEEFIRSTNAME + ' ' + US.EMPLOYEEMIDDLENAME + ' ' + US.EMPLOYEELASTNAME AS FULLNAME
    ,US.GRADE
    ,SO.SOLICITATIONID
    ,SO.DATEFINANCING
    ,SO.MANUFACTURER
    ,SO.MODEL
    ,SO.CHASSI
    ,SO.VALUEPROPERTY
    ,SO.VALUEGRANTED
    ,SO.STATUSSOLICITATION
    ,PY.PAYMENTSTATUS
    ,PY.VALUEPAYMENT
    ,PY.DATEPAYMENT
    ,MAX(PY.NUMBERSEQUENCE) AS SEQUENCE
    ,MAX(PY.DATEPAYMENT) AS DATEPEYMENT


FROM
    [dbo].[USER] US 
    INNER JOIN SOLICITATION SO ON US.USERID = SO.USERID
    INNER JOIN PAYMENTS PY ON US.USERID = PY.USERID AND PY.SOLICITATIONID = SO.SOLICITATIONID

WHERE
    PY.PAYMENTSTATUS IN('QT', 'PG')

GROUP BY
     US.EMPLOYEESTATUS
    ,US.EMPLOYEEID
    ,US.EMPLOYEEFIRSTNAME
    ,US.EMPLOYEEMIDDLENAME
    ,US.EMPLOYEELASTNAME
    ,US.GRADE
    ,SO.SOLICITATIONID
    ,SO.DATEFINANCING
    ,SO.MANUFACTURER
    ,SO.MODEL
    ,SO.CHASSI
    ,SO.VALUEPROPERTY
    ,SO.VALUEGRANTED
    ,SO.STATUSSOLICITATION
    ,PY.PAYMENTSTATUS
    ,PY.VALUEPAYMENT
    ,PY.DATEPAYMENT

This is the result of select

Note that by the screenshot above, the solicitationid number 7378 and 7380, if repeated 3 times, this is because it has 3 different payment dates. I tried to give a MAX(PY.DATEPAYMENT), but that didn’t solve, as you can see in the query. The question is:

As I do to bring in this query, a record of each and in the Payments table I always trzer the last paid date(PG) or quitada(QT). The latter will be only one. The result should be 7 records and not 11, as is.

Answer 1

There in your script you used MAX(PY.DATEPAYMENT) but did not remove the same from the GROUP BY...

And there’s another column that could be a problem SEQUENCE, but with this field you did right, used MAX(PY.NUMBERSEQUENCE) AS SEQUENCE and pulled him out of the GROUPBY.