时间戳

首页 > 解决方案 > 根据日期 SQL 删除旧记录

sql - 根据日期 SQL 删除旧记录

问题描述

这是我的查询

SELECT 
    cf.CLIENTID, p.Id as ProfileID, t.TITLEDESC as Title,
    cf.ClntForenme as Name, cf.CLNTSURNME as Surname, 
    pm.Lender, pm.Product, pm.LenderReference,
    pm.AmountRequested as LoanAmount,
    pm.DateCompleted, 
    CASE 
       WHEN pm.DateCompleted BETWEEN (SELECT DATEADD(YEAR, -1, GETDATE())) AND GETDATE() 
          THEN 'Completed under a year ago'
       WHEN pm.DateCompleted BETWEEN (SELECT DATEADD(YEAR, -2, GETDATE())) AND (SELECT DATEADD(YEAR, -1, GETDATE())) 
          THEN 'Backlog WOM 1 Year'
       WHEN pm.DateCompleted BETWEEN (SELECT DATEADD(YEAR, -3, GETDATE())) AND (SELECT DATEADD(YEAR, -2, GETDATE())) 
          THEN 'Backlog WOM 2 Year'
       WHEN pm.DateCompleted BETWEEN (SELECT DATEADD(YEAR, -4, GETDATE())) AND (SELECT DATEADD(YEAR, -3, GETDATE())) 
          THEN 'Backlog WOM 3 Year'
       ELSE ''
    END Source,
    CASE 
       WHEN pm.Id > 0 THEN 'Check Perspectiove for ERC'
       ELSE ''
    END ERC,
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID
FROM 
    tbl_Profile as p
INNER JOIN 
    tbl_Profile_Mortgage as pm ON p.id = pm.FK_ProfileId
LEFT JOIN 
    tbl_ClientFile as cf ON p.ClientId = cf.CLIENTID
LEFT JOIN 
    [dbo].tbl_DDTitles as t ON cf.CLNTTITLE = t.titleid
WHERE 
    pm.MortgageStatus = 7 
    AND p.CaseTypeId = 1
    AND pm.DateCompleted BETWEEN (SELECT DATEADD(YEAR, -4, GETDATE())) AND GETDATE()

此查询返回 990 条记录。

一些客户将有多个配置文件 ( ProfileID) 我想做的只是向我展示ProfileID最新的DateCompleted。

因此，例如，我的 clientID 为 5566，该客户端有 3 个配置文件，每个配置文件都有自己的 Datecompleted 我只想查看基于 Datecompleted 的最后一个配置文件信息。

任何帮助将非常感激。

干杯

标签： sqlsql-servertsqlduplicates

解决方案

您的一个选择是按 , 对数据进行分区client，profile然后检查哪个datecompleted是最大值（也就是 aDateRank为 1 的那个）。然后只需选择具有该值为 1 的那些。

select * from
(
    SELECT cf.CLIENTID ,p.Id as ProfileID,t.TITLEDESC as Title,cf.ClntForenme as Name,cf.CLNTSURNME as Surname,pm.Lender,pm.Product,pm.LenderReference,pm.AmountRequested as LoanAmount,
    pm.DateCompleted, 
     CASE 
    WHEN pm.DateCompleted BETWEEN (select dateadd(year, -1, getdate())) AND getdate() THEN 'Compelted Under a year ago'
    WHEN pm.DateCompleted BETWEEN (select dateadd(year, -2, getdate())) AND (select dateadd(year, -1, getdate())) THEN 'Backlog WOM 1 Year'
    WHEN pm.DateCompleted BETWEEN (select dateadd(year, -3, getdate())) AND (select dateadd(year, -2, getdate())) THEN 'Backlog WOM 2 Year'
    WHEN pm.DateCompleted BETWEEN (select dateadd(year, -4, getdate())) AND (select dateadd(year, -3, getdate())) THEN 'Backlog WOM 3 Year'
    ELSE ''
    END Source,
    CASE 
    WHEN pm.Id > 0 THEN 'Check Perspectiove for ERC'
    ELSE ''
    END ERC,
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID,
    ROW_NUMBER() OVER (Partition by CLIENTID, ProfileID order by DateCompleted desc) as DateRank --Changes here
    FROM tbl_Profile as p
    INNER JOIN tbl_Profile_Mortgage as pm
    ON p.id = pm.FK_ProfileId
    LEFT JOIN tbl_ClientFile as cf
    ON p.ClientId = cf.CLIENTID
    LEFT JOIN [dbo].tbl_DDTitles as t on cf.CLNTTITLE = t.titleid
    WHERE pm.MortgageStatus = 7 and p.CaseTypeId = 1
    AND pm.DateCompleted between (select dateadd(year, -4, getdate())) AND getdate()
) clientinfo
where clientinfo.DateRank = 1

推荐阅读