CODEWITHSUNDEEP
sqlsql-servert-sqlduplicates
RustyHamster
RustyHamster

Reputation: 359

Remove old records based on date SQL

Here is my query

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()

This query returns 990 records.

Some clients will have multiple profiles (ProfileID) what I want to do is only show me the ProfileID with the latest DateCompleted.

So, for instance, I have a clientID of 5566 this client has 3 profiles each with there own Datecompleted i only want to see the last profiles information based on Datecompleted.

Any help would be much appreciated.

Cheers

Upvotes: 0

Views: 50

Answers (1)

Adam
Adam

Reputation: 2440

One option for you is to partition your data by client, profile, then check which datecompleted is the greatest value (aka whichever ones have a DateRank of 1). Then just select those that have that value as 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

Upvotes: 1

Related Questions