CODEWITHSUNDEEP
sql-serverdatediff
Janek
Janek

Reputation: 85

datediff inside the time schedule

I need to calculate datedif(ss, start_time, end_time) between certain types of activities, which I pretty know how to do it. My code is:

WITH WorkDates AS (
SELECT 
    ROW_NUMBER() OVER (PARTITION BY A.NUMBER ORDER BY datestamp DESC) AS RowNumber,
    p2.affected_item as Service,
    p1.close_time as close_time,
    datestamp,
    description,
    a.type,
    a.number as number
from ACTIVITYM1 a
left outer join probsummarym1 p1 on a.number = p1.number
inner join probsummarym2 p2 on p1.number = p2.number
where ((a.type like 'status%'
and (description like '%to Work In Progress%'
or description like '%from Work In Progress%'))  or a.type like 'Closed')
)
SELECT
O1.Service,
O1.number,
O1.close_time,
    sum(DATEDIFF(ss, O2.datestamp, O1.datestamp)) AS sum_of_time
FROM WorkDates O1
LEFT JOIN WorkDates O2
    ON O2.RowNumber = O1.RowNumber + 1
    and O2.number = O1.number
where O2.description like '%to Work In Progress%' 
and (O1.description like '%from Work In Progress%' or O1.type like 'Closed')
group by O1.Service, O1.number, O1.close_time
order by O1.Service, O1.number

I'm doing it that way, because I need to calculate it between certain types of activities, and a number of such 'time windows' where it should be counted is not constant. And it works quite ok, but it calculates full time, including holidays, weekends

But there is one one more thing I need to have done here.

I would like to calculate datediff(ss, start_date, end_date) but only from Monday to Friday. It means, for example if start_date was on Friday at 16:00 and end_date on Monday at 7:00, datediff should be (24:00 - 16:00) + (07:00 - 00:00) = 15 hours. Is it possible?

Upvotes: 0

Views: 61

Answers (1)

Andrey Davydenko
Andrey Davydenko

Reputation: 350

You should calculate week difference, then convert it to seconds, then deduct it from datediff. Like this:

sum(DATEDIFF(ss, O2.datestamp, O1.datestamp) - abs(DATEPART(ww,O2.datestamp) - DATEPART(ww,O1.datestamp)) * 2 * 24 * 60 * 60 ) AS sum_of_time

Upvotes: 2

Related Questions