Click here to Skip to main content
15,888,239 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear experts,

I have the following kind of structure in the database as rows;

Three columns - User ID | Date | Working hours
Data is filled in for each user (with IDs), each date (from Jan 2010 to Dec 2020) and working hours for each day.

Now, the business process is that a user can change working hours. For example, I normally work 75 hours per fortnight. But, I want to change my hours from January 2017 to March 2017 as working only 40 hours per fortnight. In that case, the system will edit hours between those dates only and the remaining hours will be left as it is up till Dec 2020.

Now I want to pull out a report on how many hours I work in the organisation. Because of the dynamic nature and the business process about changing hours any time, I want to get the output as the following;

Test user 01/01/2010 to 31/12/2016 75 hours per fortnight
Test user 01/01/2017 to 31/03/2017 40 hours per fortnight
Test user 01/04/2017 to 31/12/2020 75 hours per fortnight

Can anyone please help me with this?

Thanks.

What I have tried:

I tried using cursors for any particular employee and using forward addition to dates and comparing previous fortnight hours to see if there was a difference to break the lines as required by the output. But I am confused as it did not serve the purpose.

Please help me.

Thanks.
Posted
Updated 16-Oct-16 16:00pm
Comments
Tomas Takac 14-Oct-16 2:37am    
Do you have any possibility to change how the data is stored? Because right now it doesn't make any sense to store data per day while you manage and report it by fortnight. Also you should post your code (via Improve question).
Nayan Ambaliya 16-Oct-16 19:16pm    
Despite us reporting on fortnightly basis, I do have to store it on day to day basis because we can modify working hours for any particular day.

You can try this code:
SQL
Select
	(Select MIN(frmDate.Date) From WorkingLogs frmDate Where frmDate.WorkingHours = wh.WorkingHours) [From Date],
	(Select MAX(toDate.Date) From WorkingLogs toDate Where toDate.WorkingHours = wh.WorkingHours) [To Date],
	wh.WorkingHours
From (
select distinct [WorkingHours] 
from [WorkingLogs] Order by [WorkingHours]) wh --get the lists of unique working hours, from this we can filter the min and max date of working hours
Order By 1,2,3
 
Share this answer
 
Comments
Nayan Ambaliya 16-Oct-16 19:49pm    
Thanks Keviniano, I think you are probably in the right direction, but, I would like the end result on the SUM of working hours and its comparison on fortnightly basis. So if the SUM of the working hours in the current fortnight and the next fortnight changes then there should be two entries in the end result table. For example,

Test user 01/01/2010 to 31/12/2016 75 hours per fortnight -- i.e. the test user works 75 hours per fortnight in between the dates 01/01/2010 to 31/12/2016
Test user 01/01/2017 to 31/03/2017 40 hours per fortnight -- i.e. the test user works 40 hours per fotnight in between the dates 01/01/2017 to 31/03/2017
Test user 01/04/2017 to 31/12/2020 75 hours per fortnight -- i.e. the test user has changed back to working 75 hours per fortnight from 01/04/2017 to 31/12/2020
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.

SQL
declare @startdate datetime
set @startdate = '2016-10-17 00:00:00.000' --starting point date

declare @result table 
(
	fromdate datetime,
	todate datetime,
	totalhours decimal(18, 2)
)

while @startdate <= '2020-12-31 00:00:00.000'
begin
        --insert a row for the current fortnight
    	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 the start date as the next fortnight
	set @startdate = dateadd(D, 14, @startdate)
		
end

--declare the final result table
declare @fresult table 
(
	fromdate datetime,
	todate datetime,
	totalhours decimal(18, 2),
	id int
)

--declare a variable to set ID in the final result table rows which are having the same fortnightly hours as the previous one
declare @id int
set @id = 1

--declare cursor and related variables
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 there are no rows in the final result table then insert the first from from the previous result table 
        --it will be the starting row because the cursor is ordered by from date ascending
	if not exists (select 1 from @fresult)
	begin

		insert into @fresult
			select @fd, @td, @th, @id
	end
	else
	begin
		--there are rows in the final result table 
                --so fetch the last row total hours and compare it with the current row total hours in the cursor
		if (select top 1 totalhours from @fresult order by fromdate desc) <> @th
		begin
                        --the hours comparison failed i.e. current fortnightly hours are different than the previous fortnight
                        --increase the ID and insert the row in the final table
			set @id = @id + 1
			insert into @fresult
				select @fd, @td, @th, @id
		end
		else
		begin
                        --current fortnightly hours are same as the previous fortnightly hours 
                        --insert the row in the final result table with the previous ID
			insert into @fresult
				select @fd, @td, @th, @id
		end

	end
	fetch next from rc into @fd, @td, @th
end
close rc
deallocate rc

--select * from @fresult

--select the distinct result along with MIN 'from date' and MAX 'to date' and the hours
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;


SQL
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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900