How not to repeat data in this query

Question

How not to repeat data in this query

Navigation

#1 by (2 votes)

1

I made 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

Please note that from the screenshot above, the request numbers for 7378 and 7380 are repeated 3 times, this is because you have 3 different payment dates. I tried to give MAX(PY.DATEPAYMENT) , but that did not work, 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 get the last paid (PG) or removed (QT) date. The latter will be only one. The result should be 7 records, not 11, as it is.

sql sql-server

asked by anonymous 13.09.2018 / 13:39

1 answer

Additions and Deletions per file in commit shell_exec php with accent

score 2 · Accepted Answer

In your script you used MAX(PY.DATEPAYMENT) but did not remove it from GROUP BY ...

And there is another column that can give problem that is SEQUENCE , but with that field you did right, used MAX(PY.NUMBERSEQUENCE) AS SEQUENCE and took it out of GROUPBY .