Rate workload by start and end date of tasks

Question

I want to display the monthly workload of each user according to: the task and the project. I don't know if this can be done in one request and I don't know how to proceed.

Here is my initial request:

SELECT 
    T1.Contract, T1.Project,
    T2.WP, T2.Tasks,
    T4.User, T2.PlannedStartDate, T2.PlannedEndDate,
    CAST(T3.PlannedWorkload AS decimal(10,2)) AS PlannedWorkload
FROM 
    GEN_Projects T1
INNER JOIN 
    GEN_Task T2 ON T1.UNID = T2.UNIDProject
INNER JOIN 
    GEN_Assignments T3 ON T2.UNID = T3.UNIDTasks
INNER JOIN 
    GEN_Resources T4 ON T3.UNIDAssignment = T4.ExternalID
WHERE 
    T1.Contract = 'IRIS'

The table below is the result of this SQL query:

Contract	Project	WP	Tasks	User	PlannedStartDate	PlannedEndDate	PlannedWorkload
IRIS	IRIS-IOC	IRIS-WP3_2-P2	Tasks1	User1	2021-03-17	2021-07-15	26.70
IRIS	IRIS-IOC	IRIS-WP1331-P1	Tasks2	User2	2018-09-26	2019-04-30	0.00
IRIS	IRIS-IOC	IRIS-WP1332-P2	Tasks3	User2	2019-11-20	2020-03-31	40.00

The second table is the expected result.

The rule is:

countNbMonth = Calculate the number of months between PlannedStartDate and PlannedEndDate grouped by (User,Tasks,WP,Project and Contract) (maybe add new column)
YearMonth = for the first row, the value retrieved is the MM and year of the PlannedStartDate. Then for the following lines, the month increments by one month until the month of the PlannedEndDate
Rate = PlannedWorkload / countNbMonth

Contract	Project	WP	Tasks	User	PlannedStartDate	PlannedEndDate	PlannedWorkload	YearMonth	Rate
IRIS	IRIS-IOC	IRIS-WP3_2-P2	Tasks1	User1	2021-03-17	2021-07-15	26.70	2021-03	5.34
IRIS	IRIS-IOC	IRIS-WP3_2-P2	Tasks1	User1	2021-03-17	2021-07-15	26.70	2021-04	5.34
IRIS	IRIS-IOC	IRIS-WP3_2-P2	Tasks1	User1	2021-03-17	2021-07-15	26.70	2021-05	5.34
IRIS	IRIS-IOC	IRIS-WP3_2-P2	Tasks1	User1	2021-03-17	2021-07-15	26.70	2021-06	5.34
IRIS	IRIS-IOC	IRIS-WP3_2-P2	Tasks1	User1	2021-03-17	2021-07-15	26.70	2021-07	5.34
IRIS	IRIS-IOC	IRIS-WP1331-P1	Tasks2	User2	2018-09-26	2019-04-30	0.00	2018-09	0
IRIS	IRIS-IOC	IRIS-WP1331-P1	Tasks2	User2	2018-09-26	2019-04-30	0.00	2018-10	0
IRIS	IRIS-IOC	IRIS-WP1331-P1	Tasks2	User2	2018-09-26	2019-04-30	0.00	2018-11	0
IRIS	IRIS-IOC	IRIS-WP1331-P1	Tasks2	User2	2018-09-26	2019-04-30	0.00	2018-12	0
IRIS	IRIS-IOC	IRIS-WP1331-P1	Tasks2	User2	2018-09-26	2019-04-30	0.00	2019-01	0
IRIS	IRIS-IOC	IRIS-WP1331-P1	Tasks2	User2	2018-09-26	2019-04-30	0.00	2019-02	0
IRIS	IRIS-IOC	IRIS-WP1331-P1	Tasks2	User2	2018-09-26	2019-04-30	0.00	2019-03	0
IRIS	IRIS-IOC	IRIS-WP1331-P1	Tasks2	User2	2018-09-26	2019-04-30	0.00	2019-04	0
IRIS	IRIS-IOC	IRIS-WP1332-P2	Tasks3	User2	2019-11-20	2021-03-31	40.00	2019-11	8
IRIS	IRIS-IOC	IRIS-WP1332-P2	Tasks3	User2	2019-11-20	2021-03-31	40.00	2019-12	8
IRIS	IRIS-IOC	IRIS-WP1332-P2	Tasks3	User2	2019-11-20	2021-03-31	40.00	2020-01	8
IRIS	IRIS-IOC	IRIS-WP1332-P2	Tasks3	User2	2019-11-20	2021-03-31	40.00	2020-02	8
IRIS	IRIS-IOC	IRIS-WP1332-P2	Tasks3	User2	2019-11-20	2021-03-31	40.00	2020-03	8

Do not hesitate to tell me if my question is not clear enough.

Thank you in advance.

Its not clear what the logic is, and whether the first table is sample data, and the second table desired results? Or something elst? — Dale K, Apr 28 '21 at 11:20

score 1 · Answer 1 · answered Apr 28 '21 at 12:01

I believe this is what you are looking for. You have to break out the date range by months using some type of number table or recursive CTE. To give credit where credit is due, I used the query I found here: Months between two dates to generate the list of months.

You can adapt this into your posted query, but used the initial table you posted to come up with this solution:

CREATE TABLE #T(Contract varchar(8), Project varchar(10),   WP varchar(20), Tasks varchar(10),  [User] varchar(10),PlannedStartDate date,PlannedEndDate date,   PlannedWorkload decimal(10,2))
INSERT INTO #T VALUES
('IRIS','IRIS-IOC','IRIS-WP3_2-P2','Tasks1','User1','2021-03-17','2021-07-15',26.70),
('IRIS','IRIS-IOC','IRIS-WP1331-P1','Tasks2','User2','2018-09-26','2019-04-30',0.00),
('IRIS','IRIS-IOC','IRIS-WP1332-P2','Tasks3','User2','2019-11-20','2021-03-31',40.00)


SELECT * 
, CAST(PlannedWorkLoad/ (DATEDIFF(month,plannedstartdate,plannedenddate) + 1) as decimal(10,2)) Rate
FROM #T
CROSS APPLY
(
    SELECT DATENAME(YEAR, DATEADD(MONTH, x.number, plannedstartdate)) + '-' +  
        RIGHT('0' + CAST( DATEPART( MONTH,DATEADD(MONTH, x.number, plannedstartdate) ) as varchar(4)), 2) AS YearMonth
    FROM    master.dbo.spt_values x
    WHERE   x.type = 'P'        
    AND     x.number <= DATEDIFF(MONTH, plannedstartdate, plannedenddate)
) D

This effectively answers what I want to do ! By adapting this query it also allowed me to highlight an error in the example of the tasks3. Thank you so much. — Gaël Duff, Apr 28 '21 at 12:22

Rate workload by start and end date of tasks

1 Answers1