Nayan Ambaliya Ответов: 2

Как агрегировать строки в базе данных SQL на основе даты-раз в две недели

Уважаемые эксперты,

У меня есть следующий вид структуры в базе данных в виде строк;

Три столбца-идентификатор пользователя | дата | часы работы
Данные заполняются для каждого пользователя (с идентификаторами), каждой даты (с января 2010 года по декабрь 2020 года) и рабочего времени за каждый день.

Теперь бизнес-процесс заключается в том, что пользователь может изменить рабочее время. Например, я обычно работаю 75 часов в две недели. Но я хочу изменить свое рабочее время с января 2017 года на март 2017 года, так как работаю всего 40 часов в две недели. В этом случае система будет редактировать часы только между этими датами, а остальные часы будут оставлены такими, как есть, до декабря 2020 года.

Теперь я хочу вытащить отчет о том, сколько часов я работаю в организации. Из-за динамической природы и бизнес-процесса о смене часов в любое время я хочу получить результат следующим образом;

Тестовый пользователь с 01/01/2010 по 31/12/2016 75 часов в две недели
Тестовый пользователь с 01.01.2017 по 31.03.2017 40 часов в две недели
Тестовый пользователь с 01.04.2017 по 31.12.2020 75 часов в две недели

Может ли кто-нибудь помочь мне с этим?

Спасибо.

Что я уже пробовал:

Я попробовал использовать курсоры для любого конкретного сотрудника, а также использовать прямое добавление к датам и сравнение предыдущих двухнедельных часов, чтобы увидеть, есть ли разница, чтобы разбить строки в соответствии с требованиями вывода. Но я в замешательстве, так как это не послужило цели.

Пожалуйста, помогите мне.

Спасибо.

Tomas Takac

Есть ли у вас возможность изменить способ хранения данных? Потому что прямо сейчас нет никакого смысла хранить данные в день, в то время как вы управляете ими и сообщаете о них через две недели. Также вы должны опубликовать свой код (через Improve question).

Nayan Ambaliya

Несмотря на то, что мы отчитываемся раз в две недели, мне приходится хранить их изо дня в день, потому что мы можем изменить рабочее время для любого конкретного дня.

2 Ответов

Рейтинг:
2

Nayan Ambaliya

Я думаю, что решил этот вопрос, но не уверен, что это эффективный способ сделать это. Пожалуйста, посмотрите на код ниже и дайте мне знать, если вы считаете, что это можно улучшить. 

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


Приведенный выше 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



Пожалуйста, дайте мне знать, если это можно улучшить.

Рейтинг:
0

Keviniano Gayo

Вы можете попробовать этот код:

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

Nayan Ambaliya

Спасибо Кевиниано, я думаю, что вы, вероятно, находитесь в правильном направлении, но я хотел бы получить конечный результат по сумме рабочих часов и его сравнение на двухнедельной основе. Таким образом, если сумма рабочих часов в течение текущей и следующей двух недель изменится, то в таблице конечных результатов должно быть две записи. Например,

Тестовый пользователь с 01/01/2010 по 31/12/2016 75 часов в две недели - то есть тестовый пользователь работает 75 часов в две недели в период с 01/01/2010 по 31/12/2016
Тестовый пользователь с 01.01.2017 по 31.03.2017 40 часов в две недели - то есть тестовый пользователь работает 40 часов в две недели в период с 01.01.2017 по 31.03.2017
Тестовый пользователь с 01/04/2017 по 31/12/2020 75 часов в две недели - то есть тестовый пользователь снова перешел на работу 75 часов в две недели с 01/04/2017 по 31/12/2020