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

Question

我需要在会重叠的时间范围之间每天返回每天的结果。

因此，从M-F开始，每天从晚上8点到8点，在该时间段返回计数。并在整整一年中这样做。

这就是我所拥有的，我可以做一个简单的事情，如果我只想在一天中想要，但我不确定如何迭代几天，尤其是在一天开始的时候，而下一天结束时，请跳过从星期六或周日开始的。

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)

Answer 1

每周和每小时的过滤几天很容易。这个 group by是否可以在您想完成的计数中获得什么？

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);

如果您需要从晚上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);