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 |

Comments

Post a Comment

Popular posts from this blog

ios - UITEXTFIELD InputView Uipicker not working in swift -

-
hello trying pop uipickerview view programmatically when user clicks on textfield. have tried doesn't doing anything. isn't working. nothing happening when click textfield class userprofiletableviewcontroller: uitableviewcontroller,uitextfielddelegate,uipickerviewdelegate,uipickerviewdatasource { var itempicker: uipickerview! = uipickerview() @iboutlet weak var gendertxtfield: uitextfield! var gender = ["male","female"] override func viewdidload() { super.viewdidload() gendertxtfield.delegate = self itempicker!.delegate = self itempicker!.datasource = self itempicker!.backgroundcolor = uicolor.blackcolor() self.gendertxtfield.inputview = itempicker } func numberofcomponentsinpickerview(pickerview: uipickerview) -> int{ return 1 } // returns # of rows in each component.. func pickerview(pickerview: uipickerview, numberofrowsincomponent component:...
Read more

Hatching array of circles in AutoCAD using c# -

-
i beginner in autocad plugins .i have created array of circle using following code. now, have hatch circles in array .how that??how hatch circles @ once? moreover, how can return hatch method , use hatch objects.sorry language.thanks in advance. [assembly: commandclass(typeof(beamsection.class1))] namespace beamsection { public class class1 { //class1 obj = new class1(); [commandmethod("beamatsupport")] public void addlightweightpolyline() { // current document , database document acdoc = application.documentmanager.mdiactivedocument; database accurdb = acdoc.database; promptpointresult ppr = acdoc.editor.getpoint("\nselect starting point "); var ucs = acdoc.editor.currentusercoordinatesystem; point3d startingpt = ppr.value.transformby(ucs); list<double> radius = new list<double>(); radius.add(0.5); ...
Read more