Member 14156756 Ответов: 1

Можем ли мы получить результат с помощью CROSS APPLY?

select Particulars,Date,BillAmount,0'PaidAmount' from tblBill
union
select Particulars,Date,0'BillAmount',PaidAmount from tblPayment
order by Date


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

With Tb1 as
(select Date,Particulars,BillAmount,0'PaidAmount' from tblBill
union
select Date,Particulars,0'BillAmount',PaidAmount from tblPayment
)

SELECT T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount],(Sum(T2.BillAmount) - Sum(T2.PaidAmount)) as Balance FROM Tb1 as T1
            INNER JOIN
                Tb1 as T2
                ON T1.[date] >= T2.[date]
                Group By T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount]
                Order by [Date]

Maciej Los

Всегда ли время начала и окончания составляет полный час или оно может содержать минуты?

Member 14156756

Вы можете использовать DATEADD() для преобразования в минуту.

например, DATEADD(минута,60,@StartDate)

jaket-cp

Я думаю, что Мацей Лос хочет знать, есть ли время (начало и конец) - может ли это быть, например, 9:35 утра

Также могут ли начальное и конечное время растянуться на несколько дней?

Maciej Los

О да!

1 Ответов

Рейтинг:
7

CHill60

Крайне маловероятно, что вам нужно использовать петлю любого рода. Моя статья Циклы обработки в SQL Server[^] предлагает несколько проработанных примеров с альтернативами циклам.

В этом случае вам необходимо определить отдельные временные интервалы, существующие в производственной таблице, например 

create table #timeslots (id int identity(1,1), [startDateTime] [datetime] NOT NULL,
[endDateTime] [datetime] NULL)
insert into #timeslots SELECT DISTINCT startDateTime, endDateTime FROM production
Это дает каждому временному интервалу уникальный идентификатор например
1	2019-02-22 09:00:00.000	2019-02-22 10:00:00.000
2	2019-02-22 10:00:00.000	2019-02-22 11:00:00.000
3	2019-02-22 11:00:00.000	2019-02-22 12:00:00.000
4	2019-02-22 12:00:00.000	2019-02-22 13:00:00.000
Все, что вам нужно сделать, это вычислить разницу в минутах между временем начала и окончания и разделить ее на количество задач, в которых был задействован работник в этом временном интервале: например 
select TaskId, WorkerId, T.id AS TIMESLOT
, DATEDIFF(MINUTE, T.startDateTime, T.endDateTime) / COUNT(*) OVER (PARTITION BY WorkerId, T.id)
from #production P
inner join #timeslots T ON P.startDateTime = T.startDateTime AND P.endDateTime = T.endDateTime
Результаты:
1	A	1	60
1	A	2	30
2	A	2	30
1	A	3	20
2	A	3	20
3	A	3	20
1	A	4	30
3	A	4	30
Это будет работать, если временные интервалы пересекаются в течение нескольких дней, но не будет работать, если endDateTime является нулевым. Я предлагаю вам стандартизировать свои временные интервалы для всех работников, иначе будет трудно интерпретировать результаты (слишком много временных интервалов, представляющих один и тот же период)

если кто-то еще хочет попробовать улучшить это, вот примеры данных, которые я использовал на основе описания операции
CREATE TABLE #production(
[id] [varchar](10) NOT NULL,
[taskId] [varchar](10) NOT NULL,
[startDateTime] [datetime] NOT NULL,
[endDateTime] [datetime] NULL,
[workerId] [varchar](5) NOT NULL
)
INSERT INTO #production (id, taskid, startDateTime, endDateTime, workerId) values
-- From 9AM to 10AM worker "A" spent 60 minutes on task 1
('1','1','22-Feb-2019 09:00:00', '22-Feb-2019 10:00:00', 'A'),
--From 10AM to 11AM "worker "A" spent 30 minutes on task 1 because worker A worked on task 1 and task 2.
('2','1','22-Feb-2019 10:00:00', '22-Feb-2019 11:00:00', 'A'),
('3','2','22-Feb-2019 10:00:00', '22-Feb-2019 11:00:00', 'A'),
--From 11AM to 12AM worker "A" spent 20 minutes on task1 because he also worked on task 2 and task 3 
('4','1','22-Feb-2019 11:00:00', '22-Feb-2019 12:00:00', 'A'),
('5','2','22-Feb-2019 11:00:00', '22-Feb-2019 12:00:00', 'A'),
('6','3','22-Feb-2019 11:00:00', '22-Feb-2019 12:00:00', 'A'),
-- From 12AM to 1PM worker "A" spent 30 minutes on task 1 because he also worked on task 3 
('7','1','22-Feb-2019 12:00:00', '22-Feb-2019 13:00:00', 'A'),
('8','3','22-Feb-2019 12:00:00', '22-Feb-2019 13:00:00', 'A')


EDIT (см. комментарий OP ниже):
Чтобы получить общее количество времени, затраченного на каждую задачу, вы можете использовать CTE (или временную таблицу) и просто сгруппироваться по TaskId (обратите внимание, что мне пришлось добавить имя столбца для расчета spent)
;with cte as 
(
select TaskId, WorkerId, T.id AS TIMESLOT
, DATEDIFF(MINUTE, T.startDateTime, T.endDateTime) / COUNT(*) OVER (PARTITION BY WorkerId, T.id) as spent
from #production P
inner join #timeslots T ON P.startDateTime = T.startDateTime AND P.endDateTime = T.endDateTime
) select TaskId, SUM(spent) 
FROM cte GROUP BY TaskID

Maciej Los

Отличная работа!

CHill60

Спасибо Мацей!

Member 14156756

Можем ли мы получить такой тип вывода.

Задача TIme_spend
1 140
2 50
3 170

CHill60

Да. Поместите мои результаты во временную таблицу или используйте cte, а затем сгруппируйте по идентификатору задачи

CHill60

Я обновил свое решение

Member 14156756

Пример 2 - два работника, которые работают над одной и той же задачей.
Рабочий а работает над заданием 1 с 9 утра до 11 утра
Рабочий а работает над заданием 2 с 9 утра до 10 утра
Рабочий б работает над заданием 1 с 11 утра до 1 часа дня

Продолжительность времени, затраченного на выполнение задания 1:
С 9 утра до 10 утра рабочий а тратил 30 минут потому что работал над заданием 2
С 10 утра до 11 утра работник а потратил 60 минут на выполнение задания 1
С 11 утра до 1 часа дня работник Б потратил 120 минут на выполнение задания 1

CHill60

- Да? Я обновил свое решение на основе этого. В чем проблема?

Member 14156756

У нас есть еще один пример.. что отличается от примера 1.

Member 14156756

я получил такой результат.
Временной интервал TaskId WorkerId
------ -------------------- ----------- -----------
1 1 1 240
2 1 2 120
3 1 3 240



Когда нам нужен именно такой результат.

ЗАДАЧА TIME_SPEND
----------- -----------
1 140

ЗАДАЧА TIME_SPEND
----------- -----------
2 50

ЗАДАЧА TIME_SPEND
----------- -----------
3 170

CHill60

Вы совсем не проясняете свое "я". Если у вас есть другая информация о времени, то поместите эту информацию в таблицу. Решение в любом случае одно и то же. Для примера, который вы описываете, данные будут следующими 

INSERT INTO #production (id, taskid, startDateTime, endDateTime, workerId) values
-- From 9AM to 10AM worker "A" worked on Task 1 and Task 2
('1','1','22-Feb-2019 09:00:00', '22-Feb-2019 10:00:00', 'A'),
('2','2','22-Feb-2019 09:00:00', '22-Feb-2019 10:00:00', 'A'),
--From 10AM to 11AM "worker "A" spent 60 minutes on task 1 
('3','1','22-Feb-2019 10:00:00', '22-Feb-2019 11:00:00', 'A'),
--From 11AM to 13:00hrs worker "B" spent 120 minutes on task 1
('4','1','22-Feb-2019 11:00:00', '22-Feb-2019 13:00:00', 'B')
Если я проверю это с помощью запросов, которые я вам дал, то получу идентификатор задачи 1 210 часов (=30 + 90 + 120) а для задания id 2-30 минут от работника а между 09:00 и 10:00. В вашем примере нет задачи 3, поэтому вы не могли бы получить эти результаты.
Если вы хотите, чтобы каждый из них был указан отдельно, то вам нужно использовать предложение WHERE и получать их по одному - честно говоря, я не вижу смысла.
Если у вас возникли проблемы, то вместо того, чтобы описывать пример словами, поделитесь данными, которые вы на самом деле используете.

CHill60

:вздох: теперь я жалею об этом.

jaket-cp

хорошая моя 5 :)

CHill60

Спасибо!

Nirav Prabtani

5+, Хорошая работа !!