I am trying to find the number of rows that 2 dates fall between. Basically I have an auth dated 1/1/2018 - 4/1/2018 and I need the count of pay periods those dates fall within.
Here is the data I am looking at:
create table #dates
(pp_start_date date,
pp_end_date date)
insert into #dates (pp_start_date,pp_end_date)
values ('2017-12-28','2018-01-10');
insert into #dates (pp_start_date,pp_end_date)
values ('2018-01-11','2018-01-24');
insert into #dates (pp_start_date,pp_end_date)
values ('2018-01-25','2018-02-07');
insert into #dates (pp_start_date,pp_end_date)
values ('2018-02-08','2018-02-21');
insert into #dates (pp_start_date,pp_end_date)
values ('2018-02-22','2018-03-07');
insert into #dates (pp_start_date,pp_end_date)
values ('2018-03-08','2018-03-21');
insert into #dates (pp_start_date,pp_end_date)
values ('2018-03-22','2018-04-04');
insert into #dates (pp_start_date,pp_end_date)
values ('2018-04-05','2018-04-18');
So when I run this query,
SELECT ad.pp_start_date, ad.pp_end_date, orderby
from (select
row_number()over ( order by pp_start_date) as orderby, * from
#dates) ad
where '2018-01-01' <= ad.pp_end_date
I somehow want to only get 7 rows. Is this even possible? Thanks in advance for any help!