Click here to Skip to main content
15,881,938 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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]
Posted
Updated 8-Apr-19 2:59am
v6
Comments
Maciej Los 22-Feb-19 5:53am    
Does the start time and end time is always full hour or can it contains minutes?
Member 14156756 22-Feb-19 7:21am    
You can use DATEADD() for converting into minute.

eg. DATEADD(MINUTE,60,@StartDate)
jaket-cp 22-Feb-19 8:30am    
I believe Maciej Los wants to know if the times (start and end) - can it be for example 9:35AM

Also can the start and end times span over days?
Maciej Los 22-Feb-19 14:21pm    
Oh yeah!

1 solution

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.
SQL
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
Results:
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
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
SQL
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 (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)
SQL
;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
 
Share this answer
 
v2
Comments
Maciej Los 22-Feb-19 14:22pm    
Great job!
CHill60 22-Feb-19 14:34pm    
Thanks Maciej!
Member 14156756 25-Feb-19 0:08am    
CAn we get this type of output.

TASK TIme_spend
1 140
2 50
3 170
CHill60 25-Feb-19 2:08am    
Yes. Put my results into a temporary table or use a cte, then group by the task id
CHill60 25-Feb-19 4:34am    
I've updated my solution

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