sql server - Counting records in a subquery -

i have table records holding patrols of guards in sql server 2008r2.

whenever duty starts new alert number created , within alert number there patrols starting time.

per 12 hours can bill flat rate when @ least 1 patrol has been performed. when under same alert number 12 hour range exceeded, further flat rate has billed.

the calculation of 12 hours starts time of first patrol.

i tried temp table not solve far.

declare @t1 table (     alertno int,     starttime smalldatetime,     endtime smalldatetime )  insert @t1 (alertno, starttime, endtime)  select alertno,        starttimepatrol,        dateadd(hour, 12, starttimepatrol) tblallpatrols patrolno = 1  select alertno,        (            select count(*)            [tblallpatrols]            inner join @t1 b on b.alertno = a.alertno            a.starttimepatrol between b.starttime , b.endtime        ) patrols [vwalledatensaetze] group alertno 

i know not end of story, cannot count numbers of patrols cannot find way solve problem.

it should somehow "group" patrols on 12-hour ranges per alert number , count how many groups exists under same alert number.

hope, of can lead me result need.

thanks michael

try this, assumes after first patrol billing period multiple of 8 hours time:

sql fiddle

ms sql server 2008 schema setup:

query 1:

declare @patrols table (     alertno int identity primary key,     starttime datetime )  insert @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 (     select min(starttime) billingstarttime,             dateadd(hour, 12, min(starttime)) billingendtime,             1 billingperiod      @patrols ), numbers (     select num     (values (0),(1), (2), (3), (4), (5), (6), (7), (8), (9)) n(num) ),  billingperiodscte (     select dateadd(hour, 8 * (billingperiod + numbers.num), billingstarttime) billingstarttime,             dateadd(hour, 8 * (billingperiod + numbers.num), billingendtime) billingendtime,             billingperiod + numbers.num billingperiod     firstbillingperiodcte     cross join numbers ) select count(distinct billingperiod) @patrols p inner join billingperiodscte b     on p.starttime >= b.billingstarttime , p.starttime < b.billingendtime 

results:

|   | |---| | 4 |