I think I have resolved this issue but I am not sure whether it is the efficient way of doing it. Please look at the code below and let me know if you think this can be improved.
declare @startdate datetime
set @startdate = '2016-10-17 00:00:00.000'
declare @result table
(
fromdate datetime,
todate datetime,
totalhours decimal(18, 2)
)
while @startdate <= '2020-12-31 00:00:00.000'
begin
insert into @result
select @startdate, dateadd(D, 13, @startdate), sum(wh.Hours)
from dbo.UserWorkingHours wh
where wh.WorkingDate between @startdate and dateadd(D, 13, @startdate)
and wh.UserID = 1209
set @startdate = dateadd(D, 14, @startdate)
end
declare @fresult table
(
fromdate datetime,
todate datetime,
totalhours decimal(18, 2),
id int
)
declare @id int
set @id = 1
declare @fd datetime
declare @td datetime
declare @th decimal(18, 2)
declare rc cursor for
select r.fromdate, r.todate, r.totalhours from @result r order by r.fromdate
open rc
fetch next from rc into @fd, @td, @th
while @@FETCH_STATUS <> -1
begin
if not exists (select 1 from @fresult)
begin
insert into @fresult
select @fd, @td, @th, @id
end
else
begin
if (select top 1 totalhours from @fresult order by fromdate desc) <> @th
begin
set @id = @id + 1
insert into @fresult
select @fd, @td, @th, @id
end
else
begin
insert into @fresult
select @fd, @td, @th, @id
end
end
fetch next from rc into @fd, @td, @th
end
close rc
deallocate rc
select distinct
(select min(fromdate) from @fresult where id = fr.id) as FROMDATE,
(select max(todate) from @fresult where id = fr.id) as TODATE,
fr.totalhours
from @fresult fr
The above SQL query gives me the following output;
FROMDATE TODATE HOURS_PER_FORTNIGHT
2016-10-17 00:00:00.000 2016-10-30 00:00:00.000 6.00
2016-10-31 00:00:00.000 2016-12-11 00:00:00.000 75.00
2016-12-12 00:00:00.000 2016-12-25 00:00:00.000 10.00
2016-12-26 00:00:00.000 2020-12-20 00:00:00.000 75.00
2020-12-21 00:00:00.000 2021-01-03 00:00:00.000 67.50
Please let me know if this can be improved.