Compare Column A of Row 1 with Column B of Row 2 in Same table-CodePudding

I have a table name 'Table A' where I need to get the all values based on two columns 'AdviserBusinessId' and 'ClientcontactGuid' having count > 1. I am able to achieve this using self join as below query.

select gc.AdviserBusinessId,gc.ClientContactGuid,gc.PlanStartDate,gc.PlanEndDate,gc.ClientEngagementGuid, gc.RenewalGuid,
ROW_NUMBER() over(partition by gc.adviserbusinessid,gc.clientcontactguid order by gc.planenddate asc) as rownum from GENIUS_ClientEngagement gc
inner join(
select AdviserBusinessId,ClientContactGuid from GENIUS_ClientEngagement
group by AdviserBusinessId,ClientContactGuid having count(*) > 1) B
on gc.AdviserBusinessId = b.AdviserBusinessId and gc.ClientContactGuid = b.ClientContactGuid

And this is what the table looks like:

Now my main point is that, I want to compare PlanEndDate of row 1 with PlanStartDate of row 2 and get the rows if PlanEndDate > PlanStartDate. Let's take an example of above two rows, if suppose the planstartdate was < planenddate then I just want to populate those above two rows.

Will cursor or loop be helpful in this ?

Thanks in advance. Any suggestions will be appreciated.

CodePudding user response：

Use analytic functions:

SELECT AdviserBusinessId,
       ClientContactGuid,
       PlanStartDate,
       PlanEndDate,
       ClientEngagementGuid,
       RenewalGuid,
       rn
FROM   (
  SELECT AdviserBusinessId,
         ClientContactGuid,
         PlanStartDate,
         PlanEndDate,
         ClientEngagementGuid,
         RenewalGuid,
         ROW_NUMBER() OVER (
           PARTITION BY adviserbusinessid, clientcontactguid
           ORDER BY planEndDate asc
         ) AS rn,
         COUNT(*) OVER (
           partition by adviserbusinessid, clientcontactguid
         ) AS num_rows,
         LEAD(planStartDate) OVER (
           PARTITION BY adviserbusinessid, clientcontactguid
           ORDER BY planEndDate asc
         ) AS next_start,
         LAG(planEndDate) OVER (
           PARTITION BY adviserbusinessid, clientcontactguid
           ORDER BY planEndDate asc
         ) AS prev_end
  FROM   GENIUS_ClientEngagement
) gce
WHERE num_rows > 1
AND   (  (rn = 1 AND planEndDate > next_start)
      OR (rn = 2 AND prev_end > planStartDate) )

CodePudding user response：

You can use self join to achieve this. Something like this:

SELECT * FROM TableA A
LEFT JOIN TableA B ON A.ClientContactGuid = B.ClientContactGuid AND (A.RowNum 1) = B.RowNum
WHERE A.PlanEndDate>B.PlanStartDate OR B.PlanStartDate IS NULL