在SQL Server中使用select中的各种组获取最大日期
在SQL Server中使用select中的各种组获取最大日期
提问于 2016-12-22 08:53:00
一直卡在这上面。我有下面的查询和结果集：
DECLARE @bookdate date = cast(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), - 1) AS DATE)
DECLARE @offset int = 1
SELECT distinct @bookdate BookDate
,th.Transactioncd TransactionType
,bp.PolicyNumber PolicyNumber
,cast(bp.EffectiveDt AS DATE) EffectiveDate
,cast(th.TransactionEffectiveDt AS DATE) TransactionEffectiveDate
,NULL
,th.TransactionNumber TransactionNumber
,cast(bp.ExpirationDt AS DATE) ExpirationDate
,UPPER(ni.IndexName) InsuredName
,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
,'0'
,'0'
,'0'
,bd.YearBuilt YrConstruction
,ln.PROPDed + '%' [% loss ded EQ]
,CAST(bd.BldgNumber AS VARCHAR(255)) BldgNumber
,CONVERT(INT,REPLACE(bd.BuildingValue,',','')) BuildingValue
,CONVERT(INT,REPLACE(bd.ContentsBLimit,',','')) ContentsBLimit
,CONVERT(INT,REPLACE(bd.ContentsCLimit,',','')) ContentsCLimit
,CONVERT(INT,REPLACE(bd.TIBLimit,',','')) TIBLimit
,CONVERT(INT,REPLACE(bd.BILimit,',','')) BILimit
,CONVERT(INT,REPLACE(bd.EDPLimit,',','')) EDPLimit
FROM java.basicpolicy bp
INNER JOIN java.nameinfo ni ON ni.SystemId = bp.SystemId
AND ni.CMMContainer = bp.CMMContainer
INNER JOIN java.partyinfo pti on pti.Id=ni.ParentId
AND pti.CMMContainer=bp.CMMContainer
and pti.SystemId=bp.SystemId
INNER JOIN java.line ln ON ln.CMMContainer = bp.CMMContainer
AND bp.SystemId = ln.SystemId
INNER JOIN java.risk r on r.SystemId=bp.SystemId
AND r.CMMContainer=bp.CMMContainer
AND r.ParentId=ln.Id
INNER JOIN java.building bd ON bd.CMMContainer = bp.CMMContainer
AND bd.SystemId = bp.SystemId
AND bd.ParentId=r.id
AND bd.[Status] = 'ACTIVE'
INNER JOIN java.addr ad ON ad.CMMContainer = ni.CMMContainer
AND bp.SystemId = ad.SystemId
AND ad.AddrTypeCd in ('RiskAddr')
AND ad.ParentId = bd.id
INNER JOIN java.transactioninfo 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 = 'Application'
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 th.TransactionEffectiveDt not like '2016-12%'
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))
,bd.YearBuilt
,ln.PROPDed + '%'
,CAST(bd.BldgNumber AS VARCHAR(255))
,CONVERT(INT,REPLACE(bd.BuildingValue,',',''))
,CONVERT(INT,REPLACE(bd.ContentsBLimit,',',''))
,CONVERT(INT,REPLACE(bd.ContentsCLimit,',',''))
,CONVERT(INT,REPLACE(bd.TIBLimit,',',''))
,CONVERT(INT,REPLACE(bd.BILimit,',',''))
,CONVERT(INT,REPLACE(bd.EDPLimit,',',''))
ORDER BY PolicyNumber, transactionnumber, bldgnumber我得到这个结果集：
我想要的是删除重复的事务编号2，如图所示。我尝试使用date执行max和group by函数，但没有成功。我在交易编号上试过了，它起作用了，但引入了其他问题。有没有办法让我得到一个结果集？它不是真正的副本，它有一个不同的日期，但我只想获得最新的日期，这就是为什么我认为max函数可能会起作用，但我必须按所有内容分组，这没有太大帮助。我一直在寻找外部应用，但我从来没有使用过它，我甚至不知道如何在这个特定的问题上使用它。
回答 1
Stack Overflow用户
发布于 2016-12-22 15:38:58
你试过first_value吗？
FIRST_VALUE ( [scalar_expression ] )
OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] ) 它的工作原理是这样的:您需要编写不同的
select **distinct** id,
first_value(transaction_effective_date) over (partition by number (,sup) order by date DESC (latest, ASC for oldest)) as last_trans_date
from table;文档可以在这里找到：https://msdn.microsoft.com/en-us/library/hh213018.aspx
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41274254
复制相关文章
相似问题
腾讯云开发者
