问select语句中的重复记录，但复杂连接中的一列除外

EN

SELECT distinct (SELECT DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,MAX(GreatestDate)) + 1, 0))
                 FROM 
                (VALUES (max(th.TransactionEffectiveDt)),(max(bp.EffectiveDt)),(max(th.TransactionDt))) AS Value(GreatestDate)) AS BookDate
    ,th.Transactioncd TransactionType
    ,bp.PolicyNumber PolicyNumber
    ,cast(bp.EffectiveDt AS DATE) EffectiveDate
    ,max(cast(th.TransactionEffectiveDt AS DATE)) TransactionEffectiveDate
    ,NULL WINS_TransactionNumber
    ,th.TransactionNumber TransactionNumber
    ,cast(bp.ExpirationDt AS DATE) ExpirationDate
    ,replace(UPPER(ni.IndexName), ',', '') InsuredName
    ,replace(isnull(UPPER(ad.Addr1), '') + ' ' + isnull(UPPER(ad.Addr2), '') + ' ' + isnull(UPPER(ad.Addr3), '') + ' ' + isnull(UPPER(ad.Addr4), ''), ',', '') InsuredStreetAddress
    ,UPPER(ad.City) InsuredCity
    ,UPPER(ad.StateProvCd) InsuredState
    ,ad.PostalCode InsuredZipCode
    ,i.EntityTypeCd InsuredType
    ,isnull(tr.ReasonCd, '') ReasonCode
    ,cast(datediff(mm, th.transactioneffectivedt, bp.expirationdt) / cast(datediff(mm, bp.effectivedt, bp.expirationdt) AS DECIMAL(15, 4)) AS DECIMAL(15, 4)) ProrateFactor
    ,isnull(cast(th.writtenpremiumamt as int), '0') APRP
    ,isnull(cast(th.inforcepremiumamt as int), '0') AnnualPremium
    ,CONVERT(INT,REPLACE(REPLACE(REPLACE(ln.DWELLLimit, '.00', ''), '$', ''),',','')) AggreLimit
    ,'0' YrConstruction
    ,ln.PROPDed +'%' [% loss ded EQ]
    ,CASE 
        WHEN BD.bldgnumber > 0
        THEN '0' END as Ind
    ,'0' CovA
    ,'0' CovB
    ,'0' CovC
    ,'0' CovD
    ,'0' CovE
    ,'0' CovF
FROM java.basicpolicy bp
INNER JOIN java.nameinfo ni ON ni.SystemId = bp.SystemId
    AND ni.CMMContainer = bp.CMMContainer
INNER JOIN java.line ln ON ln.CMMContainer = bp.CMMContainer
    AND bp.SystemId = ln.SystemId
INNER JOIN java.building bd ON bd.CMMContainer = bp.CMMContainer
    AND bd.SystemId = bp.SystemId
INNER JOIN java.addr ad ON ad.CMMContainer = ni.CMMContainer
    AND bp.SystemId = ad.SystemId
    AND ad.AddrTypeCd = 'InsuredMailingAddr'
INNER JOIN java.transactionhistory th ON th.CMMContainer = bp.CMMContainer
    AND th.SystemId = bp.SystemId
LEFT JOIN java.transactionreason tr ON tr.CMMContainer = bp.CMMContainer
    AND TR.SystemId = bp.SystemId
    AND TR.ParentId = th.ID
INNER JOIN java.insured i ON i.CMMContainer = bp.CMMContainer
    AND i.SystemId = bp.SystemId
WHERE bp.CMMContainer = 'policy'
    AND ni.NameTypeCd = 'INSUREDNAME'
    AND (
        th.TransactionCd IN (
            'new business'
            ,'endorsement'
            ,'cancellation'
            ,'rewrite-new'
            )
        OR (
            th.WrittenPremiumAmt IS NOT NULL
            AND th.WrittenPremiumAmt <> 0
            AND th.TransactionCd IN ('reinstatement')
            )
        )
    AND bp.CarrierCd = 'ENIC'
    AND bp.PolicyNumber = '84EQ000006'
GROUP BY
    th.Transactioncd
    ,bp.PolicyNumber 
    ,cast(bp.EffectiveDt AS DATE) 
    ,cast(th.TransactionEffectiveDt AS DATE)
    ,th.TransactionNumber 
    ,cast(bp.ExpirationDt AS DATE)
    ,UPPER(ni.IndexName)
    ,(isnull(UPPER(ad.Addr1), '') + ' ' + isnull(UPPER(ad.Addr2), '') + ' ' + isnull(UPPER(ad.Addr3), '') + ' ' + isnull(UPPER(ad.Addr4), ''))
    ,UPPER(ad.City) 
    ,UPPER(ad.StateProvCd) 
    ,ad.PostalCode 
    ,i.EntityTypeCd 
    ,isnull(tr.ReasonCd, '') 
    ,cast(datediff(mm, th.transactioneffectivedt, bp.expirationdt) / cast(datediff(mm, bp.effectivedt, bp.expirationdt) AS DECIMAL(15, 4)) AS DECIMAL(15, 4))
    ,isnull(cast(th.writtenpremiumamt as int), '0')
    ,isnull(cast(th.inforcepremiumamt as int), '0')
    ,CONVERT(INT,REPLACE(REPLACE(REPLACE(ln.DWELLLimit, '.00', ''), '$', ''),',',''))
    ,ln.PROPDed +'%'
    ,CASE 
        WHEN BD.bldgnumber > 0
        THEN '0' END 
ORDER BY PolicyNumber
        ,transactionnumber
        ,Ind
        ,BOOKDATE