从SQL结果中删除周末

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?

我正在使用SQL server manager并查询一个2005年的数据库。我认为有必要。今天又增加了一篇新的专栏文章。谁能帮忙吗?

3 个解决方案

#1

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

如果是周末，DATEPART(dw, your_date)会告诉你。通常1表示星期六，7表示星期天，但是它可以根据服务器配置进行更改。读一下datepart函数，了解它是如何工作的。

#2

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.

此查询假设工作日为上午8点至下午6点。

Working example can be found here.

工作示例可在这里找到。

#3

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

为了安全起见，您需要从DATEPART(dw,GETDATE())中获取正确的数据

SET DATEFIRST 1

设置DATEFIRST 1

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

它将确保1是星期一，7是星期天。

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

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

#1

#2