July 27, 2020 at 4:28 pm
Hi Friends,
I struggling to create a datetime column with certain conditions. My data looks like below
CreatedDateTime, Priority, TargetDate
I already have a calendar table which will say if its a business day or not as 1 or 0. I want to calculate the TargetDate with conditions as follows.
If Priority is 1 Then add 2 hours to CreatedDate (No exclusions)
If priority is 2 then add 4 hours to CreatedDate (No exclusions)
Priority is 3 then add 8 hours CreatedDate (but exclude holiday and weekends). Example if the ticket is created on 24th Jul 11:00 PM then target date should be 27th Jul and if 27th is a bank holiday then target date should be 28th. 25/26 is a weekend.
Similarly for Priorty 4 it is 7 days. Is this possible?
July 27, 2020 at 4:47 pm
An alternative to creating a TargetDate column would be to create a Priorities table which contains columns related to the priority. Then calculate the TargetDate when you need it. Priorities shift all the time why get boxed in?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 27, 2020 at 5:14 pm
Priority 1 and 2 should be trivial if I understand this right - for Priority 1, TargetDate = DATEADD(hour, 2, CreatedDate). And for Priority 2, you change the 2 to a 4.
Priority 3 becomes a bit more complicated, but you already have your calendar table, it is not THAT complicated. One way to do it would be (may not be the most efficient, but I think it should work):
UPDATE MyTable A
SET A.TARGETDATE= CASE WHEN DATEPART(hours, A.CreatedDateTime) < 16) THEN DATEADD(hour,8,A.CreatedDate)
ELSE DATEADD(hour,DATEPART(hours,DATEADD(hours, 8,A.CreatedDateTime),MIN(B.WeekDay))
FROM CalendarTable B
WHERE B.WorkDay = 1
AND B.weekday > A.CreatedDateTime;
And similar for priority 4, that is similar to priority 1 and 2 as you just need to add 7 days, but you will want to check the calendar table too as you need to make sure it is not a bank holiday in which case you will need to add 1 to 3 days depending on how the holiday falls.
NOTE - I did not test the above code as I had no sample data to play with. It is entirely off the top of my head and I didn't check for syntax errors. I may have missed a bracket, but I think I caught them all. Also, as I don't know your data, I was guessing on that (like is WorkDay what you called your business day check column? and weekday what you called your calendar date column in the calendar table?).
And there are multiple ways you could do this. You COULD do it all in 1 query with case statements, or you could do it with 4 different queries. My approach would be to design it using 4 queries (one for each priority) and once I am confident they all work, then merge it down to 1. That being said, performance may be 'good enough" with 4 queries and maybe having 4 updates is good enough in your situation. I don't know your environment or your data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 28, 2020 at 4:22 am
Be careful, there could be a lot of matching rows for that query.
I think you need to specify that you want the first work date only. I also have not tested my code:
UPDATE tn
SET CreatedDateTime = workday.date
FROM dbo.table_name tn
CROSS APPLY (
SELECT TOP (1) date
FROM dbo.calendar_table /*or work_days table*/
WHERE date >=
CASE WHEN Priority IN (1, 2) THEN CAST(tn.CreatedDateTime AS date)
WHEN Priority = 4 THEN CAST(DATEADD(HOUR, 16, tn.CreatedDateTime) AS date)
WHEN Priority = 7 THEN CAST(DATEADD(DAY, 7, tn.CreatedDateTime) AS date)
ELSE CAST(tn.CreatedDateTime AS date)
END
ORDER BY date
) AS workday
WHERE CAST(tn.CreatedDateTime AS date) <> workday.date
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply