CODEWITHSUNDEEP
sqlsql-server
user7463647
user7463647

Reputation: 53

How to get the difference between two datetime columns in SQL

I have two columns (Created and ResolutionDate) in a table with the datetime values enter image description here

I need to get the difference between the columns created and resolutiondate to get the number of days it took to be resolved from created date.

And also I need to get the result only with the working days or network days i.e., Monday to Friday (not the weekends and holidays).

For example, if I take created:2015-09-22 and resolutiondate: 2015-09-30, then the result should be 6 days, because two days are saturday and sunday between the created and resolutiondate I choose.

Please let me know how can I work it out with SQL.

Upvotes: 1

Views: 690

Answers (3)

paparazzo
paparazzo

Reputation: 45106

If you need company dates then have a table with all company work days 

select ticket.ID, count(companyWorkDay.dt)
from ticket 
left join companyWorkDay
       on companyWorkDay.dt between ticket.created and ticket.resolution 
group by ticket.ID

For the hassle of populating the table once you get a lot easier queries
You could write a query to enter the weekdays and then just remove the company holidays

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 82020

Perhaps something like this... The Cross Apply portion could be a UDF

Declare @YourTable table (ID int,Created datetime, ResolutionDate datetime)
Insert Into @YourTable values
(1,'2015-09-22 13:35:38','2015-09-30 17:37:09'),
(2,'2016-02-28 12:55:22','2016-02-29 12:55:44'),
(3,'2015-09-22 13:30:31','2015-09-30 17:37:09')

Select A.*
      ,B.WorkingDays
 From  @YourTable A
 Cross Apply (
                Select WorkingDays=count(*)
                 From (Select Top (DateDiff(DD,A.Created,A.ResolutionDate)+1) D=DateAdd(DD,Row_Number() over (Order By (Select NULL))-1,cast(cast(A.Created as date) as datetime)) From master..spt_values N1) D
                 Where  D >= A.Created and D<= A.ResolutionDate
                   and  DatePart(DW,D) not in (7,1)
                   and  Cast(D as Date) Not In (Select Date From (Values 
                                       ('2016-01-01','New Year''s Day'),
                                       ('2016-01-18','Martin Luther King, Jr,'),
                                       ('2016-02-15','Washington''s Birthday'),
                                       ('2016-03-25','Good Friday'),
                                       ('2016-05-30','Memorial Day'),
                                       ('2016-07-04','Independence Day'),
                                       ('2016-09-05','Labor Day'),
                                       ('2016-11-24','Thanksgiving'),
                                       ('2016-11-25','Black Friday'),
                                       ('2016-12-26','Christmas Day') 
                                ) as H (Date,Name))   
       ) B

Returns

ID  Created                   ResolutionDate            WorkingDays
1   2015-09-22 13:35:38.000   2015-09-30 17:37:09.000   6
2   2016-02-28 12:55:22.000   2016-02-29 12:55:44.000   1
3   2015-09-22 13:30:31.000   2015-09-30 17:37:09.000   6

Upvotes: 0

NicoRiff
NicoRiff

Reputation: 4883

For calculating the difference between two dates in working days, you can use the following function. Be aware that this will only calculate without weekends, and if you have holidays in the middle, it will calculate them as ordinary days.

    public double GetBusinessDays(DateTime startD, DateTime endD)
    {
        double calcBusinessDays =  1 + ((endD - startD).TotalDays * 5 - (startD.DayOfWeek - endD.DayOfWeek) * 2) / 7;
        if ((int)endD.DayOfWeek == 6) calcBusinessDays--;
        if ((int)startD.DayOfWeek == 0) calcBusinessDays--;

        return calcBusinessDays;
    }

Upvotes: 1

Related Questions