Counting records in a subquery

Question

I have a table with records Holding patrols of guards in SQL Server 2008R2.

Whenever a duty starts a new alert number is created and within this alert number there a patrols with a starting time.

Per 12 hours we can bill a flat rate when at least one patrol has been performed. When under the same alert number the 12 hour range is exceeded, a further flat rate has to be billed.

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

I tried with a temp table but could not solve it so far.

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 that this is not the end of the Story, but as I cannot even count the numbers of patrols I cannot find a way to solve the Problem.

It should somehow "group" the patrols over 12-hour ranges per alert number and then count how many groups exists under the same alert number.

Hope, someone of you can lead me to the result I Need.

Thanks your help Michael

Looking at your requirements it seems that to decide whether a patrol starts a new billing duty or falls within the previous duty you need to look at all previous patrols and duties (this may be fixed if the patrols always start at 0:00 / 12:00). Thus I think you should not try to do this within SQL Server, I suggest you do this async in a seperate application. — Stephan B, Jan 28 '16 at 11:48
Well, I could solve it in VBA, with Loops but it has to be done on the Server at the end it should create an insert into another table with the result record. Patrols start any time and an alert number is a Kind of an order from the customer which could continue a few days or even more. — mak, Jan 28 '16 at 11:53
Is there an end time on tblAllPatrols? If not, then how do you know when a patrol ends? — BateTech, Jan 28 '16 at 12:08
There is no end time, as all patrols have a Duration of 10 min. independant what the real Duration is. Only the starttime is important, due to the calculation of the 12-hour range. — mak, Jan 28 '16 at 12:12

score 0 · Accepted Answer · answered Jan 28 '16 at 13:00

Try this, it assumes that after the first patrol the billing period is a multiple of 8 hours from this time:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Query 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

Results:

|   |
|---|
| 4 |

Steve, I tried your solution and it works fine. Thanks a lot! — mak, Feb 15 '16 at 07:53

score 0 · Answer 2 · edited May 23 '17 at 12:31

Here is a query that will give each billing period, up to 65,535 billing periods, accurate to the second.

My solution uses a calculated "Tally" table, but you would be better off in the long run to create your own physical "Tally" table in your database. See What is the best way to create and populate a numbers table? for more details.

You should be able to replace @tblPatrols with your patrol table.

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
;

Thanks a lot, I will try it when back from Holidays. Michael — mak, Feb 04 '16 at 17:00

score 0 · Answer 3 · answered Feb 08 '16 at 10:24

I found a solution by myself, which seems to be easier and I could not find any mistake using it. I take the first Startime of the first patrol in a variable. Then I use datediff for die difference of the all StartTimePatrol to the startime of the first patrol and divide it by 12 hours

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

then I put the result of each record in a temp table

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

then I group the altertno and Billings and count them

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

The result looks correct for me.

Thanks for all replies. Michael

This seems to take a lot of steps to achieve, what can be achieved in a single statement! See my answer — Steve Ford, Feb 12 '16 at 14:55

Counting records in a subquery

3 Answers3