我们可以使用CROSS APPLY获得结果吗？ [英] Can we get result using CROSS APPLY?

问题描述

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

我的尝试：

What I have tried:

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]

推荐答案

它您不太可能需要使用任何类型的循环。我的文章处理SQL Server中的循环 [ ^ ]提供了几个有效的循环替代例子。 />


在这种情况下，您需要识别生产表上存在的各个时隙，例如

It is highly unlikely that you need to use a loop of any kind. My article Processing Loops in SQL Server[^] offers several worked examples with alternatives to loops.

In this case you need to identify the individual timeslots that exist on the production table e.g.

create table #timeslots (id int identity(1,1), [startDateTime] [datetime] NOT NULL,
[endDateTime] [datetime] NULL)
insert into #timeslots SELECT DISTINCT startDateTime, endDateTime FROM production

这为每个时间段提供了一个唯一的标识符，例如

This gives each timeslot a unique identifier e.g.

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

然后您需要做的就是计算开始和结束时间之间的差异，并将其除以通过工作人员参与该时间段的任务数量：例如

All you have to do then is work out the difference in minutes between the start and end times and divide it by the number of tasks the worker was involved in in that timeslot: e.g.

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 为空，则不起作用。我建议你对所有工人的时间段进行标准化，否则很难解释结果（代表同一时期的时间太多）



如果有人想要的话为了改进这一点，这里是我根据OP的描述使用的样本数据

This will work if timeslots cross over days but won't work if the endDateTime is null. I suggest that you standardise your timeslots over all workers otherwise it is going to be difficult to interpret the results (too many timeslots representing the same period)

if anyone else wants to have a go at improving this, here is the sample data I used based on the OP's description

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')

编辑（参见下面的OP评论）：

获取您可以使用的每项任务所花费的总时间一个CTE（或一个临时表）和TaskId上的组（注意我必须为计算添加一个列名花费）

EDIT (see OP comment below):
To get the total amount of time spent on each task you could use a CTE (or a temporary table) and just group on TaskId (note I've had to add a column name for the calculation 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

这篇关于我们可以使用CROSS APPLY获得结果吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！