SQL Server：SELECT上周五下午12点到本周五下午12点

I am trying to get all records submitted to SQL Server from last Friday 12PM to this Friday 12PM for the purposes of payroll. I have the past 7 days working as shown below but I need it to be specific to 12PM last Friday to 12PM this Friday.

我试图从上周五中午12点到本周五中午12点将所有记录提交给SQL Server,以用于工资单。我过去7天的工作时间如下所示,但我需要特别针对上周五的12点到本周五的12点。

SELECT DISTINCT 
    notifications.requestid,
    Max(timestatuschange),
    assignedtouser,
    Count(requestid) AS Total
FROM   
    notifications
WHERE  
    timestatuschange >= Dateadd(day, -7, Getdate())
    AND notifications.status = 'Yellow'
    OR timestatuschange >= Dateadd(day, -7, Getdate())
    AND notifications.status = 'Red'
    OR timestatuschange >= Dateadd(day, -7, Getdate())
    AND notifications.status = 'Blue'
GROUP BY 
    requestid,
    assignedtouser

1 个解决方案

#1

Since you are using GETDATE(), I am assuming today is Friday, you can try something like this

由于您使用的是GETDATE(),我假设今天是星期五,您可以尝试这样的事情

SELECT DATEADD(hour,12,CONVERT(VARCHAR(10),GETDATE(),112)),
DATEADD(day,-7,DATEADD(hour,12,CONVERT(VARCHAR(10),GETDATE(),112)))

Your query would be

你的查询是

DECLARE @StartDate DATETIME = DATEADD(day,-7,DATEADD(hour,12,CONVERT(VARCHAR(10),GETDATE(),112)))

DECLARE @EndDate DATETIME = DATEADD(hour,12,CONVERT(VARCHAR(10),GETDATE(),112))

SELECT DISTINCT notifications.requestid,
                Max(timestatuschange),
                assignedtouser,
                Count(requestid) AS Total
FROM   notifications
WHERE  timestatuschange >=  @StartDate
       AND  timestatuschange <= @EndDate
       AND notifications.status IN ('Yellow','Red','Blue')
GROUP  BY requestid,
          assignedtouser

#1