时间范围之间每天T-SQL结果为1年

I need to return count on results per day between a time range that will overlap days.

我需要在一个重叠天数的时间范围内返回每天结果的计数。

So from 8 PM to 8 AM for every day that starts on M-F return a count for that time period. And do that for the entire year.

因此,从M-F开始的每一天的晚上8点到早上8点返回该时间段的计数。并且这整年都是这样做的。

This is what I have, and I could do a simple and between if I only wanted on day but I'm not sure how to iterate through days especially when the start is on one day and the end on the next but skip the ones that Start on a Saturday or Sunday.

这就是我所拥有的,我可以做一个简单的介绍,如果我只想在一天但我不知道如何迭代几天,特别是当开始是在一天而结束在下一天但跳过那些从周六或周日开始。

SELECT TOP (50)
    ClientVisit.visittype,
    ClientVisit.location_id,
    ClientVisit.visittype_id,
    Location.location_desc,
    Location.location_code,
    ClientVisit.timein,
    ClientVisit.timeout,
    ClientVisit.visit_dateday
FROM 
    ClientVisit
INNER JOIN 
    Location ON ClientVisit.location_id = Location.location_id
WHERE
    (ClientVisit.visittype Like '%Open Chart%'
     OR ClientVisit.visittype LIKE '%Diag%')
    AND (Location.location_code = 'Access-505'
         OR Location.location_code = 'Access-hosp')
    AND (ClientVisit.timein BETWEEN @param1 AND @param2)

1 个解决方案

#1

Filtering days of week and hours of the day is easy enough. Does this group by get at what you're trying to accomplish for the counts?

过滤一天中的几天和一天中的几个小时很容易。这个小组是通过了解你想要完成的计数吗?

SELECT CAST(ClientVisit.timein AS DATE) AS DT, COUNT(*)
FROM ClientVisit INNER JOIN Location
    ON ClientVisit.location_id = Location.location_id
WHERE
        (ClientVisit.visittype Like '%Open Chart%' OR ClientVisit.visittype LIKE '%Diag%')
    AND (Location.location_code = 'Access-505' OR Location.location_code = 'Access-hosp')
    -- Use date params rather than datetime
    AND CAST(ClientVisit.timein AS DATE) BETWEEN @param1 AND @param2
    -- M-F assuming @@DATEFIRST is Sunday (7)
    AND DATEPART(weekday, ClientVisit.timein) BETWEEN 2 AND 6
    -- time of day. won't include the instant of 8:00:00am
    AND (   DATEPART(hour, ClientVisit.timein) BETWEEN 8 AND 23
        OR  DATEPART(hour, ClientVisit.timein) BETWEEN 0 AND 7)
GROUP BY CAST(ClientVisit.timein AS DATE);

If you need to treat the hours from 8PM to 8AM as a single shift then you can adjust the times prior to so that times after midnight are treated as part of the preceeding day:

如果你需要将时间从晚上8点到早上8点作为一个班次处理,那么你可以调整之前的时间,以便午夜之后的时间被视为前一天的一部分:

WITH AdjustedVisit AS (
    SELECT *, DATEADD(hour, -8, timein) AS adjustedin FROM ClientVisit)
    -- Use date params rather than datetime
    WHERE CAST(timein AS DATE) BETWEEN @param1 AND @param2
)
SELECT CAST(v.adjustedin AS DATE) AS DT, COUNT(*)
FROM AdjustedVisit AS v INNER JOIN Location AS l
    ON v.location_id = l.location_id
WHERE
        (v.visittype Like '%Open Chart%' OR v.visittype LIKE '%Diag%')
    AND (l.location_code = 'Access-505' OR l.location_code = 'Access-hosp')
    -- M-F assuming @@DATEFIRST is Sunday (7)
    AND DATEPART(weekday, v.adjustedin) BETWEEN 2 AND 6
    -- time of day. won't include the instant of 8:00:00am
    AND DATEPART(hour, v.adjustedin) BETWEEN 12 AND 23
GROUP BY CAST(v.adjustedin AS DATE);

#1