CODEWITHSUNDEEP
sqlsql-servert-sql
Anand Shrestha
Anand Shrestha

Reputation: 61

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

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: [![enter image description here][1]][1]

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.

Upvotes: 0

Views: 198

Answers (3)

Anand Shrestha
Anand Shrestha

Reputation: 61

This was what I wanted, thanks to @MTO for the direction.

with cte
as(
select  gc.AdviserBusinessId,gc.ClientContactGuid,gc.PlanStartDate,gc.PlanEndDate,gc.ClientEngagementGuid, gc.RenewalGuid,
ROW_NUMBER() over(partition by gc.adviserbusinessid,gc.clientcontactguid order by planenddate desc) 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
)
select *,ROW_NUMBER() over(partition by adviserbusinessid,clientcontactguid order by planenddate asc) as rn,
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_ends into #temp2  from cte 
where rownum <=2

select AdviserBusinessId,ClientContactGuid,PlanStartDate,PlanEndDate,ClientEngagementGuid,RenewalGuid from #temp2 where 
(  (rn = 1 AND planEndDate > next_start)
      OR (rn = 2 AND prev_ends > planStartDate) )

      drop table #temp2

Upvotes: 0

MT0
MT0

Reputation: 167774

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

Upvotes: 1

Vinay Bishnoi
Vinay Bishnoi

Reputation: 46

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

Upvotes: 0

Related Questions