Removing weekends from a SQL result

Question

I have the following query that produces a number of results for me. One of which is the number of days old each of the records is.

I need a more accurate figure now by removing weekends from the equation. Not sure how to proceed and struggling to understand answers I have found. My query so far is:

select
    i.incidentnumber,
    i.priority, 
    i.status, 
    i.subject,
    i.actualsystem, 
    t.ownerteam, 
    convert(varchar,i.createddatetime,103)[Created],
    convert(varchar,i.lastmoddatetime,103)[Modified], 
    datediff(day,i.createddatetime,{fn now()})[Days old],
    datediff(mi,i.createddatetime,{fn now()})[Minutes old],
    cast(i.createddatetime
    i.owner
from 
    incident i with (nolock) inner join task t with (nolock) on t.parentlink_recid = i.recid
where 
    i.status <> 'Closed' 
    and i.actualsystem <> 'System Administration' 
    --and i.service <> 'Service Request'
    and t.status in ('Active','Waiting','Accepted') 
    --and t.ownerteam <> 'UK Service Desk'

order by 
    --t.ownerteam asc
    --i.actualsystem asc
    datediff(day,i.createddatetime,{fn now()}) desc

I am using SQL server manager and querying a 2005 database. I comment out as necessary. The minutes old is a new column added today. Can anyone help?

Are weekends only Saturday and Sunday? What about holidays? Are holidays on Friday considered part of the weekend? Are holidays counted in general or not? There are numerous questions on this site already about calculating the number of days between two dates and the most reliable solution is usually a [calendar table](http://stackoverflow.com/questions/1201874/calendar-table-for-data-warehouse) where you can simply flag days as working days and write simple queries to get your answer. — Pondlife, Oct 15 '12 at 15:53

score 5 · Answer 1 · answered Oct 15 '12 at 11:51

5

DATEPART(dw, your_date) will tell you if it is a weekend. Usually 1 means saturday and 7 means sunday but it can change depending of the server configuration. Read about the datepart function to understand how it works

answered Oct 15 '12 at 11:51

Diego

34,802
21
91
134

Actually 1 usually means Sunday and 7 = Saturday, but the logic is the same. you can find out which day is set to = 1 on YOUR server by using select @@DATEFIRST. If it has a 7 then Sunday = 1st day of the week. – Data Masseur Oct 15 '12 at 13:58
thats kinda confusing because the @@DATEFIRST fuction should return the first day of the week, which is Sunday, but sunday has the value of 7, not 1 as you would expect. If you check this link: http://msdn.microsoft.com/pt-br/library/ms181598.aspx, you'll see that sunday means 7. – Diego Oct 15 '12 at 14:44
Who says Sunday is the first day of the week? Many people would tell you it's Monday. This is one reason why it's best to approach date-related tasks by using a table of dates instead of functions that may return different results based on server settings. – Pondlife Oct 15 '12 at 15:50
Using @@DATEFIRST gives you the Day that is set to the 1st day of the week using Mon=1, Tue=2, Wed=3, Thu=4, Fri=5, Sat = 6 & Sun = 7. The 1 thru 7 is just so we can use smallint instead of varchar. This value actually tells the system what value a day will equal in a DATEPART(dw, date) call. If you set Sunday to be 1st, then a Sunday will now return a one because that's how you've told the system to reply. – Data Masseur Oct 15 '12 at 20:10

Gidil · Answer 2 · 2012-10-16T13:08:41.490

0

If you want to calculate the number of work days (non-weekends) in the range, the simplest approach would be just to take into account 2 weekend days every week.
For example:

SELECT Datediff(D, '2012-01-01', '2012-01-31') / 7 * 5 + 
              Datediff(D, '2012-01-01', '2012-01-31') % 7 WorkDays, 
       Datediff(D, '2012-01-01', '2012-01-31')            AllDays

To calculate work hours (incl. partials), use the following query:

SELECT ( T.WORKDAYS - 1 ) * 10 + OPENINGDAYHOURS + CLOSINGDAYHOURS 
FROM   (SELECT Datediff(D, OPEN_DATE, CLOSE_DATE) / 7 * 5 + 
                              Datediff(D, OPEN_DATE, CLOSE_DATE) % 7 WorkDays, 
               18 - Datepart(HOUR, OPEN_DATE) 
               OpeningDayHours, 
               Datepart(HOUR, CLOSE_DATE) - 8 
               ClosingdayHours, 
               Datediff(D, OPEN_DATE, CLOSE_DATE)                    AllDays 
        FROM   TABLE1)T

This query assumes workdays from 8 AM to 6 PM.

Working example can be found here.

edited Oct 16 '12 at 13:08

answered Oct 15 '12 at 11:54

Gidil

4,137
2
34
50

I wonder whether I am better to work in days of the week or business hours in the day. The latter certainly does sound a little more difficult. – Allegin Oct 16 '12 at 12:45
Why would 'business hours' be more difficult? All days have the same number of business hours, so all that's left is to multiply the days by the number of daily hours. – Gidil Oct 16 '12 at 12:46
So if business hours were 10 per working day and a record was left open for 281 days, how would i then calculate the true business hours excluding the out of hours time. e.g. business hours are say 8am until 6pm. A record was opened at 5pm and remained open for 281 days. How can I retrieve the true number of business hours? – Allegin Oct 16 '12 at 12:52
Ah! You want to calculate the full days + partial days (possibly also at the end?). Did I understand correctly? If so, I'll prepare a solution soon. – Gidil Oct 16 '12 at 12:54
1

Yeah, the number of days old the record is taking into account the removal of weekends and out of business hours. Sorry for all the questions but this is definately helping. A massive learning curve for me, but helping nonetheless – Allegin Oct 16 '12 at 12:57
Give me a few minutes and I'll set up an example on SQL Fiddle. – Gidil Oct 16 '12 at 12:58
I had similar results with the number of hours it reported back. The problem I have is that I also need to take into account at what time the record was created. – Allegin Oct 16 '12 at 13:35

score 0 · Answer 3 · answered Oct 16 '12 at 18:19

0

To be safe that you get correct data from DATEPART(dw,GETDATE()) you need to use

SET DATEFIRST 1

It will make sure that 1 is Monday and 7 - is Sunday.

Reference: http://msdn.microsoft.com/en-en/library/ms181598.aspx

answered Oct 16 '12 at 18:19

Farfarak

1,497
1
8
8

Removing weekends from a SQL result

3 Answers3