SQL Date calculation
-
Hi,
I have a requirement on my plate that driving me nuts and below dataset is in a similar fashion to what I have in our database.
I would really appreciate if you can point me in the right direction. My requirement is to calculate leaves (excluding weekends) while populating End Date column.
Oddly enough, the system in which sales department uses to record their leaves captures only Start Date and keep the End Date as NULL. Duration column is the only entity that we can see how many days people took for leaves. I need to populate "End Date" column, but simple dateadd function doesn't do the trick, basically because it adds weekends as well.
So, I need to exclude weekends while adding days based on given duration. Then, I need to extract days that fall into next month. Below dataset shows an example of this. Sales Person 1 took annual leave on Apr, 29 for 5 Days. So, Apr 29-30 fall into April while the rest of the days fall into May. So, I need to treat them separately. In this way, I will be able to calculate total leaves for each Sales Person in monthly.
CREATE TABLE TestData (
[Time Off Territory ID] int,
[Owner] varchar(255),
[Start Date] date,
[End Date] date,
[Reason] varchar(255),
[Duration] varchar(50)
);
INSERT INTO TestData (
[Time Off Territory ID],
[Owner],
[Start Date],
[End Date],
[Reason],
[Duration]
)
VALUES
(
'6232',
'Sales Person 1',
'2019-04-29',
NULL,
'Annual Leave',
'5 Days'
),
(
'5014',
'Sales Person 2',
'2019-03-28',
NULL,
'Sick Leave',
'5 Days'
);
Thanks
-
I have to ask, why is
Durationavarchar(50), and why is the value'5 days'? Does that mean you might have values like "2 weeks" or even1 month"? Something likeDATEADD(DAY,Duration, [Start Date])` is going to result in an error like "Conversion failed when converting the varchar value '5 Days' to data type int.". Before anything, you need to fix that data type, as you can't add the string "5 days" to a date.Readable sample data:
CREATE TABLE TestData ([Time Off Territory ID] int,
[Owner] varchar(255),
[Start Date] date,
[End Date] date,
[Reason] varchar(255),
[Duration] int);
GO
INSERT INTO TestData ([Time Off Territory ID],
[Owner],
[Start Date],
[End Date],
[Reason],
[Duration])
VALUES('6232','Sales Person 1','2019-04-29',NULL,'Annual Leave','5 Days'),
('5014','Sales Person 2','2019-03-28',NULL,'Sick Leave','5 Days');Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
You should add a column to contain the duration in days. Compute the value once when a row is inserted or deleted (using a trigger). You don't want to have to scan and edit the [Duration] value every time you use it.
For example:
CREATE TABLE dbo.TestData (
[Time Off Territory ID] int NOT NULL,
[Owner] varchar(255) NULL,
[Start Date] date NOT NULL,
[End Date] date NULL,
[Reason] varchar(255) NOT NULL,
[Duration] varchar(50) NOT NULL,
[Duration Days] decimal(5, 2) NULL
);
GO
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER TestData__TR_INSERT_UPDATE
ON dbo.TestData
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
IF TRIGGER_NESTLEVEL(@@PROCID, 'AFTER', 'DML') = 1
BEGIN
UPDATE TD
SET [Duration Days] = CASE
WHEN i.[Duration] LIKE '%Week%' THEN [duration_number] * 7.0
WHEN i.[Duration] LIKE '%Day%' THEN [duration_number]
WHEN i.[Duration] LIKE '%Hour%' THEN [duration_number] / 8.0
WHEN i.[Duration] LIKE '%Month%' THEN [duration_number] * 30
ELSE [duration_number] END
FROM dbo.TestData TD
INNER JOIN inserted i ON i.[Time Off Territory ID] = TD.[Time Off Territory ID]
CROSS APPLY (
SELECT PATINDEX('%[0-9]%', i.[Duration]) AS [duration_number_start]
) AS alias1
CROSS APPLY (
SELECT PATINDEX('%[^0-9.]%', SUBSTRING(i.[Duration] + '/',
[duration_number_start] + 1, 50)) AS [duration_number_length]
) AS alias2
CROSS APPLY (
SELECT CASE WHEN duration_number_start = 0 THEN 0
ELSE SUBSTRING(i.[Duration], [duration_number_start], [duration_number_length])
END AS duration_number
) AS alias3
WHERE UPDATE([Duration])
END /*IF*/
GO
TRUNCATE TABLE dbo.TestData;
INSERT INTO dbo.TestData ([Time Off Territory ID],[Owner],[Start Date],[End Date],[Reason],[Duration])
VALUES('6232','Sales Person 1','2019-04-29',NULL,'Annual Leave','5 Days'),
('5014','Sales Person 2','2019-03-28',NULL,'Sick Leave','5 Days'),
('9876','Sales Person 3','2019-05-04',NULL,'Sick Leave','Days 4'),
('8765','Sales Person 4','2019-05-04',NULL,'Sick Leave','1 Week'),
('7654','Sales Person 5','2019-05-04',NULL,'Sick Leave','6 Hours')
SELECT * FROM dbo.TestData;
UPDATE dbo.TestData SET [Duration] = '3 Days' WHERE [Time Off Territory ID] = '5014';
SELECT * FROM dbo.TestData WHERE [Time Off Territory ID] = '5014';SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Hmm, "30 days" for a month is a bad assumption in my view, Scott. Considering that 7 months have 31 days, and 1 has 28(/29), assuming 1 month is 30 days long has a higher chance of being wrong than right (only 4 out of 12 have 30 days).
Hopefully, the OP's data is always in the format "X days", and then that assumption doesn't come into play, but I'm not posting an answer until we hear otherwise.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
@thom-2 A, @scottpletcher, Many thanks for your replies. Data comes from one of the downstream services (CRM). The way how they keep dates is odd enough and this is something beyond my control. : (
There are number of selections (Duration) available while entering time off territory.
PM Off, AM Off, All Day, 2 Days, 3 Days, 4 Days, 5 Days, Long Absence.
End Date information gets stamped only when "Long Absence" is entered. Rest comes with "NULL".
Hope this information would provide you a solid foundation. @scottpletcher, I will also try to execute and understand the code you've shared above.
Thanks again
- seismicbeat wrote:
There are number of selections (Duration) available while entering time off territory. PM Off, AM Off, All Day, 2 Days, 3 Days, 4 Days, 5 Days, Long Absence.
Are those the only one you can get? So someone can't have 10 days off? What results would you expect for AM and PM? Can you provide more than a couple examples and expected results please, and ensure you cover all the scenarios. I don't want to have to make guesses about your data, which could be wrong.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Yes, those are the only one I can get. I know it is ridiculous but if someone gets 10 days off then he/she has two options.
1- Select "Long Absence" and put an End Date. Unfortunately, it contains weekends as well. I've added an example of that.
2- Enter two time off territory entries by using 5 Days. (5 Days + 5 Days)
AM and PM means that person used a half day leave. (0.5)
CREATE TABLE TestData ([Time Off Territory ID] int,
[Owner] varchar(255),
[Start Date] date,
[End Date] date,
[Reason] varchar(255),
[Duration] int);
GO
INSERT INTO TestData ([Time Off Territory ID],
[Owner],
[Start Date],
[End Date],
[Reason],
[Duration])
VALUES('6232','Sales Person 1','2019-04-29',NULL,'Annual Leave','5 Days'), --//First two days fall into April, but remaining days fall into May which makes thing this difficult to calculate.
('5014','Sales Person 2','2019-03-28',NULL,'Sick Leave','5 Days'),
('3467','Sales Person 3','2019-04-17',NULL,'Sick Leave','AM Off'),
('3514','Sales Person 4','2019-05-08',NULL,'Sick Leave','PM Off'),
('3782','Sales Person 5','2019-03-13',NULL,'Compensatory Leave','All Day'), --// This is used when sales person has an assignment during weekend. So, that person will have the right to take a day off during weekdays.
('3512','Sales Person 6','2019-04-05',NULL,'Annual Leave','2 Days'), --//Apr 5 falls into Friday in calendar and this person took a day off for next Monday as well (Apr 8).
('6523','Sales Person 7','2019-04-24',NULL,'Congress','3 Days'),
('8434','Sales Person 8','2019-04-03',NULL,'Annual Leave','4 Days'), --//Apr 3 falls into Wednesday in calendar but he/she used 4 days off which means that he/she used one day off from next week as well (next monday).
('9234','Sales Person 9','2019-03-04','2019-03-18','Annual Leave','Long Absence'); --//Long Absence differentiates itself with forcing the user to select an End Date, but it counts weekends as well. He/she actually took 10 days leave but when I subtract End Time - Start Time it returns 15 days which is wrong.Output:
I need to calculate total days in which sales persons are out of their territories for each reason (annual leave, sick leave etc...)
Below sample displays that Oct and Nov is not in the list, which means that user didn't take any leave in those months.
I can provide you more information if you require.
Thanks
-
Those expected results don't appear to be representative of the example data. Where is sales persons 2 - 9? Sales Person 1 has 5 days off in April in your sample data, but they have 1 day in the office in the expected results. It makes no sense.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thanks Thom. Got your point. : ) Let me change the expected result set along with sample data itself.
Does this make sense now?
CREATE TABLE TestData ([Time Off Territory ID] int,
[Owner] varchar(255),
[Start Date] date,
[End Date] date,
[Reason] varchar(255),
[Duration] int);
GO
INSERT INTO TestData ([Time Off Territory ID],
[Owner],
[Start Date],
[End Date],
[Reason],
[Duration])
VALUES('6232','Sales Person 1','2019-04-29',NULL,'Annual Leave','5 Days'), --//First two days fall into April, but remaining days fall into May which makes thing this difficult to calculate.
('1123','Sales Person 1','2019-03-11',NULL,'Sick Leave','3 Days'),
('5014','Sales Person 2','2019-03-28',NULL,'Sick Leave','5 Days'),
('3467','Sales Person 3','2019-04-17',NULL,'Sick Leave','AM Off'),
('3514','Sales Person 4','2019-05-08',NULL,'Sick Leave','PM Off'),
('3518','Sales Person 4','2019-05-02',NULL,'Annual Leave','2 Days'),
('3782','Sales Person 5','2019-03-13',NULL,'Compensatory Leave','All Day'), --// This is used when sales person has an assignment during weekend. So, that person will have the right to take a day off during weekdays.
('3512','Sales Person 6','2019-04-05',NULL,'Annual Leave','2 Days'), --//Apr 5 falls into Friday in calendar and this person took a day off for next Monday as well (Apr 8).
('6523','Sales Person 7','2019-04-24',NULL,'Congress','3 Days'),
('6525','Sales Person 7','2019-04-18',NULL,'Sick Leave','All Day'),
('8434','Sales Person 8','2019-04-03',NULL,'Annual Leave','4 Days'), --//Apr 3 falls into Wednesday in calendar but he/she used 4 days off which means that he/she used one day off from next week as well (next monday).
('9234','Sales Person 9','2019-03-04','2019-03-18','Annual Leave','Long Absence'); --//Long Absence differentiates itself with forcing the user to select an End Date, but it counts weekends as well. He/she actually took 10 days leave but when I subtract End Time - Start Time it returns 15 days which is wrong.Thanks
-
30 days was a placeholder as much as anything else. The logic can be as simple or as complex as needed to properly translate the Duration text. The main idea is to do it only once, at entry, rather than every time it's used.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Hi Scott,
Many thanks for sharing the above solution, I've tested it out and it works perfectly fine while handling durations but when I calculated ToTs for each sales person the data misleads me. The reason is that some entries have impact on the actual result set since they continue to next month.
For instance, first entry (6232) starts in April but ends in May (Apr 29 + 5 Days). How can I break these dates down into months? So, it will look like this;
Thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply