CODEWITHSUNDEEP
sqlsql-serversql-server-2005datediff
Allegin
Allegin

Reputation: 31

Removing weekends from a SQL result

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?

Upvotes: 3

Views: 1996

Answers (3)

Farfarak
Farfarak

Reputation: 1517

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

Upvotes: 0

Gidil
Gidil

Reputation: 4137

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.

Upvotes: 0

Diego
Diego

Reputation: 36126

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

Upvotes: 5

Related Questions