Select only the records with same values

I am working on a SQL statement that will become a part of a view. What I need is to extract only the records that have the same unique key twice. The query looks like below right now.

select distinct 
    rscmaster_no_in, rsc_no_in, calendar_year, calendar_month, 
    Wstat_Abrv_Ch,
    h.Wstat_no_in, Staffing_Calendar_Date, payhours,
    l.OTStatus
from 
    vw_all_ts_hire h
left join 
    MCFRS_OTStatus_Lookup l on l.wstat_no_in = h.Wstat_no_in
where 
    rscmaster_no_in in (select rscmaster_no_in from vw_rsc_ECC_splty) 
    and Wstat_Abrv_Ch <> ''
    and h.Wstat_no_in in (103, 107)
    and l.OTStatus in ('ECCOTRemove', 'ECCOTSignup')
    and Staffing_Calendar_Date = '2020-11-01' -- only for the testing purposes. Will be removed later.
order by 
    RscMaster_no_in

The result I get from the query above is:

I need to modify the SQL statement so that the end result is like below:

How can I modify the above statement to spit out the end result like that?

Answer

Use the analytic count(*) over () function.

with cte as (
  select
      count(*) over (partition by YourUniqueKey) as MyRowCount
  {rest of your query}
)
select *
from cte
where MyRowCount = 2;

Advertisement

Answer