在子查询中计数记录

Question

我有一张桌子，上面记录着SQLServer2008R2中守卫的巡逻记录。

每当值勤开始时，就会创建一个新的警报号，并且在此警报号内有一个带起始时间的巡逻。

每12小时，当至少进行一次巡逻时，我们可以收取统一的费用。当在相同的警报号码下超过12小时的范围时，必须进一步收取统一费率。

12小时的计算从第一次巡逻的时间开始。

我试过用临时表，但到目前为止还没能解决。

DECLARE @t1 TABLE (
    AlertNo INT,
    Starttime SMALLDATETIME,
    Endtime SMALLDATETIME
)

INSERT INTO @t1 (AlertNo, Starttime, Endtime)

SELECT AlertNo,
       Starttimepatrol,
       DATEADD(HOUR, 12, Starttimepatrol)
FROM tblAllPatrols
WHERE PatrolNo = 1

SELECT AlertNo,
       (
           SELECT COUNT(*)
           FROM [tblAllPatrols] a
           INNER JOIN @t1 b ON b.AlertNo = a.AlertNo
           WHERE a.Starttimepatrol BETWEEN b.Starttime AND b.Endtime
       ) AS patrols
FROM [vwAlleDatensaetze]
GROUP BY AlertNo

我知道这不是“故事”的结尾，但由于我连巡逻次数都数不清，所以我找不到解决问题的办法。

它应该以某种方式“分组”每一个警戒号在12小时范围内的巡逻，然后计算出在同一警戒号下有多少组。

希望你们中有人能带我找到我需要的结果。

谢谢你的帮助，迈克尔

Answer 1

尝试一下，它假设在第一次巡逻之后，计费期是从这个时间开始的8个小时的倍数：

SQL Fiddle

MS Server 2008架构设置

查询1

DECLARE @Patrols TABLE
(
    AlertNo INT IDENTITY PRIMARY KEY,
    StartTime DateTime
)

INSERT INTO @Patrols (StartTime)
VALUES ('20160126 09:57'), 
       ('20160126 10:21'),
       ('20160126 19:54'),
       ('20160126 23:21'),
       ('20160127 08:13'),
       ('20160127 16:43'),
       ('20160128 07:33')

;WITH FirstBillingPeriodCTE
AS
(
    SELECT MIN(StartTime) as BillingStartTime, 
           DateAdd(HOUR, 12, MIN(StartTime)) As BillingEndTime, 
           1 As BillingPeriod 
    FROM @Patrols
),
Numbers
As
(
    SELECT num
    FROM (Values (0),(1), (2), (3), (4), (5), (6), (7), (8), (9)) AS n(Num)
), 
BillingPeriodsCTE
AS
(
    SELECT DATEADD(Hour, 8 * (BillingPeriod + Numbers.Num), BillingStartTime) AS BillingStartTime, 
           DATEADD(Hour, 8 * (BillingPeriod + Numbers.Num), BillingEndTime) AS BillingEndTime, 
           BillingPeriod + Numbers.Num As BillingPeriod
    FROM FirstBillingPeriodCTE
    CROSS JOIN Numbers
)
SELECT COUNT(DISTINCT BillingPeriod)
FROM @Patrols P
INNER JOIN BillingPeriodsCTE B
    ON P.StartTime >= B.BillingStartTime AND P.StartTime < B.BillingEndTime

结果

|   |
|---|
| 4 |

Answer 2

这里有一个查询，它将给出每个记帐期，最多可达65,535个记帐期，精确到第二个。

我的解决方案使用经过计算的“理货”表，但从长远来看，在数据库中创建自己的物理“理货”表会更好。有关更多详细信息，请参阅创建和填充数字表的最佳方法是什么？。

您应该能够用您的巡逻表替换@tblPatrols。

DECLARE @tblPatrols TABLE (alertNo int, startTime datetime);
DECLARE @hoursPerBillingPeriod int, @toHoursConversion float;
SET @hoursPerBillingPeriod = 12;
SET @toHoursConversion = 60 * 60;

INSERT INTO @tblPatrols (alertNo, startTime)
VALUES 
  (1, '2016-01-28 05:57')
, (1, '2016-01-28 07:23')
, (1, '2016-01-28 08:10')
, (2, '2016-01-28 09:05')
, (2, '2016-01-28 12:22')
, (2, '2016-01-28 16:06')
, (2, '2016-01-28 23:45')
, (2, '2016-01-29 00:05')
, (3, '2016-01-28 12:00')
, (3, '2016-01-28 16:06')
, (3, '2016-01-29 00:00')
, (4, '2016-01-28 12:00')
, (4, '2016-01-28 16:06')
, (4, '2016-01-28 23:59:59.997')
;

;WITH
--......................
--This section used to simulate a "Tally" table... you would be better off to Create a physical Tally table
--  see: https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table
  Pass0 as (select 1 as C union all select 1) --2 rows
, Pass1 as (select 1 as C from Pass0 as A, Pass0 as B) --4 rows
, Pass2 as (select 1 as C from Pass1 as A, Pass1 as B) --16 rows
, Pass3 as (select 1 as C from Pass2 as A, Pass2 as B) --256 rows
, Pass4 as (select 1 as C from Pass3 as A, Pass3 as B)--65536 rows
, Tally as (select row_number() over(order by C) - 1 as N from Pass4) --65536 rows 
--........................
,cteNumBillings as (
    SELECT fp.alertNo
        , firstPatrolTime = min(fp.startTime) 
        , lastPatrolTime = max(fp.startTime)
        , hoursBetweenStartMinMax = datediff(second, min(fp.startTime), max(fp.startTime)) / @toHoursConversion
        , numberOfBillingPeriods = floor(((datediff(second, min(fp.startTime), max(fp.startTime)) / @toHoursConversion) / @hoursPerBillingPeriod) + 1)
    FROM @tblPatrols fp
    GROUP BY fp.alertNo
)
SELECT b.alertNo
    --This is the "x" value of the expression "Billing Period x of y"
    , BillingPeriodNumber = t.N + 1
    , BillingPeriodPatrolCount = 
            (select count(*) 
                from @tblPatrols p 
                where p.alertNo = b.alertNo 
                and p.startTime >= dateadd(hour, 12 * t.N, b.firstPatrolTime) 
                and p.startTime < dateadd(hour, 12 * (t.N+1), b.firstPatrolTime)
            )
    , BillingStart = dateadd(hour, 12 * t.N, b.firstPatrolTime)
    , BillingEnd = dateadd(second, -1, dateadd(hour, 12 * (t.N + 1), b.firstPatrolTime))
    --This is the "y" value of the expression "Billing Period x of y"
    , TotalBillingPeriodCount = b.numberOfBillingPeriods
FROM cteNumBillings b
INNER JOIN Tally t ON t.N >= 0 and t.N < b.numberOfBillingPeriods
ORDER BY 1,2
;

Answer 3

我自己找到了一个解决方案，这似乎更容易，我没有发现任何错误使用它。我把第一次巡逻的第一次启动时间作为变量。然后，我使用datediff将所有StartTimePatrol的死差除以第一次巡逻的启动时间，再除以12小时。

set @BillingPeriod=(select (datediff(hour,@StartTime,@StartTimePatrol)/12)+1)

然后，我将每条记录的结果放入临时表中。

insert into @t2 ( Alertno, Starttime, Billings )
values ( @Alertno, @StartTimePatrol, @BillingPeriod )

然后我把互斥者和比林斯人分在一起，数一数

select alertno, count(Billings ) from (select alertno, Billings from @t2 
group by alertno, Billings ) temp group by alertno

结果对我来说是正确的。

谢谢你的回复。迈克尔