April 18, 2019 at 6:28 pm
Hi,
I'm trying to calculate number of days that sales persons are out of their territories per-quarter based on start and end dates. Originally the table contains time off territory information in sales person level and data comes from one of our downstream services.
Let's say Sales Person A took annual leave for 15 days and reported it in the satellite system. System ensures to keep this information in one row rather than creating multiple rows.
There are number of selections available while entering time off territory and end date information gets stamped only when "Long Absence" is entered. So, system ensures that an end date is going to be selected to submit that particular report. For example, absence due to maternity leave.
But, filling the End Date information is not required for other selections. Instead, users are selecting some options like PM/AM Off, All Day, 2-3-4 and 5 Days.
What I need to do is to calculate business days based on start/end dates, but unlike long absence end date column doesn't get stamped with a proper date. I tried to populate end dates based on the value on time column (adding days with dateadd function), so I've updated the values in time column like 5 Days to 5, All Day to 1 and then I used the following query, but it didn't work out as I planned since I realized that I completely omitted weekends.
There are instances where sales persons took annual leave in the midst of week. For example, a sales person took an annual leave for 5 days starting from April 17, 2019 Wednesday and selected 5 days from the drop-down . Naturally, when I added 5 days to start date and populated the end date column based on that it starts from April 17 and ends on April 21 (Sunday). So, I'm missing Monday and Tuesday.
CREATE TABLE TOTTestData (
[ToT ID] int,
[Owner] varchar(255),
[Start Date] date,
[End Date] date,
[Sub Reason] varchar(255),
[Time] varchar(255),
[Status] varchar(255)
);
INSERT INTO TOTTestData (
[ToT ID],
[Owner],
[Start Date],
[End Date],
[Sub Reason],
[Time],
[Status]
)
VALUES
(
'420',
'Sales Person 1',
'2019-04-01',
'2019-04-15',
'Annual Leave',
'Long Absence',
'Submitted'
),
(
'421',
'Sales Person 2',
'2019-03-20',
NULL,
'Sick Leave',
'3 Days',
'Submitted'
),
(
'422',
'Sales Person 3',
'2019-03-13',
NULL,
'Annual Leave',
'5 Days',
'Submitted'
),
(
'423',
'Sales Person 4',
'2019-03-11',
NULL,
'Annual Leave',
'4 Days',
'Submitted'
),
(
'424',
'Sales Person 5',
'2019-04-10',
NULL,
'Meeting',
'PM Off',
'Submitted'
);
Below is the query I tried after I populated end date column.
P.S. The reason why I'm trying to calculate in quarter level is that specifically long absences like maternity leave can last 4 or 5 months forcing me to calculate in quarter level.
;WITH CTE
AS
(
SELECT [ToT ID], [Owner], [Sub Reason], [Time], [Status], [Start Date], [End Date]
FROM TOTTestData
UNION ALL
SELECT [ToT ID], [Owner], [Sub Reason], [Time], [Status], [Time], [Status], DATEADD(DAY,1,[Start Date]), [End Date] FROM CTE
WHERE [Start Date] < [End Date]
)
SELECT [ToT ID], [Owner], [Sub Reason], [Time], [Status], [Time], [Status], DATEPART(q, [Start Date]) AS [Quarter], COUNT(DATEPART(q, [Start Date])) AS [ToT Days]
FROM CTE
WHERE DATEPART(dw,[Start Date]) NOT IN (7,1)
GROUP BY [ToT ID], [Owner], [Sub Reason], [Time], [Status], [Time], [Status], DATEPART(q, [Start Date])
OPTION (MAXRECURSION 0);
Your help is deeply appreciated.
Regards
April 18, 2019 at 7:28 pm
According to what I understand, your query actually gets the correct results but it needs to be optimized for performance? You don't need to get the number of days from the Time column?
April 18, 2019 at 7:57 pm
Hi Luis,
Actually, there are certain ToT selections that return NULL in the end date column and to get around this I'm using dateadd to update end date based on value located in time column.
Let's say, Start Date is 17/04/2019, End Date is NULL and Time is 5 Days/Annual Leave. Since I know the sales person got 5 days paid annual leave, in this particular case, I would add 5 days to Start Date and can use the result as the End Date. Start Date will remain 17/04/2019 while End Date becomes 21/04/2019. But, this approach has shortcomings and in fact causing me to miss Monday and Tuesday.
17/04/2019 - Wednesday, 18/04/2019 - Thursday, 19/04/2019 - Friday, 20/04/2019 - Saturday and 21/04/2019 - Sunday
In reality, end date should be 23/04/2019 (covering next Monday and Tuesday). Since sales person got 5 days paid annual leave and if it falls into weekend than it should continue to next week. That's why the above query doesn't return exactly what I need.
Thanks
April 18, 2019 at 10:11 pm
I'm getting error:
Msg 208, Level 16, State 1, Line 67
Invalid object name 'CS_REPTOT'.
--Vadim R.
April 19, 2019 at 4:15 am
rVadim, you're right. I've created a sample data but forgot to change the table name. I've just modified the script. Sorry for the inconvenience caused.
Thanks
April 19, 2019 at 6:30 am
The above query would possibly work if I manage to add business days to the Start Date but excluding weekends. Let's say ten days from start date but excluding weekends.
Start Date: 15/04/2019 End Date: 26/04/2019 instead of 24/04/2019. I would really appreciate if someone can help me with that.
April 19, 2019 at 9:58 am
Figured this out. : ) Since I already have"Time" column, I don't have to calculate End Dates for each row. I have combined the following queries. Basically, the first one returns everything except Long Absence and the latter one is purely handling Long Absence and eliminate days that fall into weekend.
TRUNCATE TABLE SomeTable
INSERT INTO SomeTable
SELECT
R.[TOT ID],
R.[Owner],
R.[Alias],
R.[Reason],
R.[Sub-Reason],
R.[Time],
DATEPART(q, [Date]) AS [Quarter],
[ToT Workday]
FROM
SomeTable R
WHERE
R.[Time] <> 'Long Absence'
--//(Long Absence)
;WITH CTE
AS
(
SELECT [TOT ID], [Owner], [Alias], [Reason], [Sub-Reason], [Time], [Date], [End Date]
FROM SomeTable
UNION ALL
SELECT [TOT ID], [Owner], [Alias], [Reason], [Sub-Reason], [Time], DATEADD(DAY,1,[Date]), [End Date] FROM CTE
WHERE [Date] < [End Date]
)
INSERT INTO SomeTable
SELECT [TOT ID], [Owner], [Alias], [Reason], [Sub-Reason], [Time], DATEPART(q, [Date]) AS [Quarter], COUNT(DATEPART(q, [Date])) AS [ToT Workday]
FROM CTE
WHERE DATEPART(dw,[Date]) NOT IN (7,1) AND [Time] = 'Long Absence'
GROUP BY [TOT ID], [Owner], [Alias], [Reason], [Sub-Reason], [Time], DATEPART(q, [Date])
OPTION (MAXRECURSION 0);
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply