Counting entries in a subquery

Question

I have a table with records containing guards patrolling in SQL Server 2008R2.

Whenever a start starts, a new alert number is created, and inside this alert number there are patrols with a start time.

Within 12 hours, we can pay off a fixed rate when at least one patrol has been completed. When the 12-hour range is exceeded with the same alert number, you need to pay another fixed rate.

The calculation of 12 hours starts from the time of the first patrol.

I tried to use a temporary table, but could not solve it.

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

I know this is not the end of the story, but since I cannot even count the number of patrols, I cannot find a way to solve the problem.

- "" 12- , , .

, - , .

Answer 1

, , 8 :

SQL Fiddle

MS SQL 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 , .

"Tally", "Tally" . . ? .

@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: /questions/30360/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

, , , . . dateiff StartTimePatrol 12

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

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

altertno Billings

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

.

.