sql server - Turning a flat SQL table into a report, aggregating numbers and nesting calculations/comparisons in a view query -



sql server - Turning a flat SQL table into a report, aggregating numbers and nesting calculations/comparisons in a view query -

i have huge flat table containing info handheld device. utilize generate reports 'site', done homegrown paas. study works great (allows view individual site), of calculations done on .net layer. need expand include key info on sites, in summarized type of way (line line each 'site'). have element (repeating panel) can display of info cycling through table or view, , lead user individual study (that exists on paas), need create view (i think best wrong?) hold summarized info displayed, id (site name) when item in repeating panel selected can drop user on individual site study requires.

so here goes nothing...

the database beingness used create reports based on security guard tags checked. extract info such average patrol times, tags missed on routes, alarms pressed etc haldheld device.

i have next coloumns of importance in tables:

data import- table 'transaction' info appears. every info line device.

-site name -route id -patrol id -type -tag name -occurrence date

route profiles- profile of route, containing total set of 'tags' route id can checked against see tags not checked in each patrol, , how 'successful' patrol guard was. dont neccessarily need utilize table aggregated understanding of how many tags checked in patrol, 'totaltags' value used check against.

-routeid -site name -totaltags -perfectpatrol

i trying obtain next basic stats. challenge generate view has aggregated calculations row row. each row cover previous day's 6am 6am window (based on occurrence date coloumn).for each site trying work out next (which sit down in 1 row of view):

1. patrols required - calculated counting number of "patrol started" occurences in 'type' coloumn site in question (each row belong site, , summarize site)

2. tags in route - pulled straight route profile table (match route id)

3. average patrol time - calculated measuring difference in 'occurrence date'(datetime) each patrol (unique patrol id's) in when 'patrol started' , 'patrol finished' value appear in type coloumn. aggregated, eg patrol of 10, of 12, , of 14, result in ave patrol time of 12 (minutes).

4. successful patrols - aggregated value number of patrols occurred in time window (percentage wise) either match or greater 'perfectpatrol' coloumn in route profile (this figure in perfectpatrol 100%) divided number of patrols required (see 1). basically, counting number of tags obtained in unique patrol, greater or equal number of tags in route (totaltags), on total number of patrols required (see 1)

5. incomplete patrol - patrol contained tags (less perfectpatrol percentage), not none. worth noting here, tag can identified occurrence of 'tag completed' in 'type' coloumn.

6. failed patrol - when no tags checked in patrol (so if in unique patrolid there no 'tag completed' values in 'type' coloumn).

7. total alarms raised - number of times 'callme' 'panic' or 'mandown' occurs in 'type' coloumn.

8. phone call me alarms - number of times 'callme' occurs in 'type' coloumn.

9. panic alarms - number of times 'panic' occurs in 'type' coloumn.

10. man downwards alarms - number of times 'mandown' occurs in 'type' coloumn.

things note: 1. there can multiple routes per site. preferrential each route have own row in case, sharing same site name. 2. each study on 24 hours, 6am 6am, , previous day. overriding filter on info import table ensure this.

i have no thought how approach due sheer size , complexity of of these operations existing within 1 huge sql query. mentioned, utilize in house paas requires little coding, hence me reaching out advice/help.

how go tackling problem?

sql sql-server

-

Comments

Post a Comment

Popular posts from this blog

formatting - SAS SQL Datepart function returning odd values -

formatting - SAS SQL Datepart function returning odd values - i'm having massive problem project can't seem right. i'm trying modify info variable in datetime format. first info 30mar12:00:00:00. i've been advised utilize code below returns value 30mar60:5:30:00. want date component , have no thought how info got exported in format wasn't in microsoft access. i've tried several mods code 1 time again comes blank or sends error message. proc sql; update dataset set date = datepart(date); quit; any advice appreciated. i'm guessing variable date numeric , formatted datetime. 1 time you've carried out conversion, alter format date9. sas datetime value number of seconds since 01 jan 1960, whereas dates number of days since 01 jan 1960. both stored numbers, using right format display value key. illustration below. data _null_; a='30mar12:00:00:00'dt; b=datepart(a); c=b; format b datetime. c date9.; set b c; run; ...
Read more

c++ - Apple Mach-O Linker Error(Duplicate Symbols For Architecture armv7) -

c++ - Apple Mach-O Linker Error(Duplicate Symbols For Architecture armv7) - here error: duplicate symbol _objc_metaclass_$_bfapplinkreturntorefererview in: /users/user/documents/facebooksdk/facebooksdk.framework/facebooksdk(bfapplinkreturntorefererview.o) /users/user/desktop/project/project/project/assets/facebook/editor/ios/facebooksdk/facebooksdk.a(bfapplinkreturntorefererview.o) ld: 889 duplicate symbols architecture armv7 clang: error: linker command failed exit code 1 (use -v see invocation) i have searched find solution of error couldn't. there tons of similar error this, 1 hasn't been asked yet. problem shows when add together "-objc" "other linker flags". need add together utilize admob service. problem? how can solve this? i faced same issue while integrating facebook-ios-sdk. because using 3rd party library had bolts.framework linked against it. solution found incorporate facebook-ios-sdk source code hosted on github. ...
Read more

php - Yii 2: Unable to find a class into the extension 'yii2-admin' -

php - Yii 2: Unable to find a class into the extension 'yii2-admin' - extension's github project: https://github.com/mdmsoft/yii2-admin i'm using advanced template of yii2, i've backend , frontend , common folders advanced folder. the advanced folder on same level of vendor folder. i've others extensions , working then installed yii2-admin composer php composer.phar require mdmsoft/yii2-admin "*" installation worked well. into vendor folder i've mdmsoft folder yii2-admin subfolder it. this actual content of v endor/yiisoft/extensions.php (modified composer installation, haven't manually touched it) <?php $vendordir = dirname(__dir__); homecoming array ( 'yiisoft/yii2-jui' => array ( 'name' => 'yiisoft/yii2-jui', 'version' => '2.0.0.0', 'alias' => array ( '@yii/jui' => $vendordir . '/yiisoft/yii2-jui', ...
Read more