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

Vote count:

0

I have a huge flat table containing data from a handheld device. I currently use it to generate reports for a 'site', but this is done with a homegrown PAAS. The report works great (allows you to view an individual site), but most of the calculations are done on this .net layer. I however now need to expand to include key information on ALL sites, in a summarized type of way (line by line for each 'site'). I have an element (repeating panel) that can display all of this information by cycling through a table or view, and then lead the user into the individual report (that already exists on the paas), but I need to basically create a VIEW (I think this would be best but I could be wrong?) that will hold all this summarized information to be displayed, as well as an ID (site name) so that when an item in the repeating panel is selected I can drop the user on the individual site report he requires.

so here goes nothing...

The database is being used to create reports based on security guard tags checked. We extract information such as average patrol times, tags missed on routes, alarms pressed etc from a haldheld device.

I have the following coloumns of importance in my tables:

Data Import- This table is where 'transaction' data appears. basically every data line from my device.

-Site Name
-Route ID
-Patrol ID
-Type
-Tag Name
-Occurrence Date

Route Profiles- This is a profile of the route, basically containing a full set of 'tags' for a route ID that can be checked against to see which tags were not checked in each patrol, and how 'successful' this patrol by a guard was. We dont neccessarily need to use this table to get an aggregated understanding of how many tags were checked in a patrol, but the 'TotalTags' value is used to check against.

-RouteID
-Site Name
-TotalTags
-PerfectPatrol

I am trying to obtain the following basic stats. The challenge now is to generate a view that has all the aggregated calculations row by row. Each row is to cover the previous day's 6am to 6am window (based on the occurrence date coloumn).For each site I am trying to work out the following (which will effectively sit in 1 row of this VIEW):

1. Patrols required - this is calculated by counting the number of "PATROL STARTED" occurences in the 'type' coloumn for the site in question (each row will belong to a site, and effectively summarize that site)

2. Tags in route - pulled straight from the route profile table (match route ID)

3. Average patrol time - This is calculated by measuring the difference in 'occurrence date'(datetime) for each patrol (unique patrol ID's) in when a 'PATROL STARTED' and 'PATROL FINISHED' value appear in the type coloumn. This will be aggregated, eg a patrol of 10, another of 12, and another of 14, will result in an ave patrol time of 12 (minutes).

4. Successful patrols - this is an aggregated value for the number of patrols that occurred in this time window that (percentage wise) either match or are greater than the 'perfectpatrol' coloumn in the route profile (this figure in perfectpatrol is usually 100%) divided by the number of patrols required (see 1). Basically, counting up the number of tags obtained in that unique patrol, that are greater or equal to the number of tags in that route (totaltags), over the total number of patrols required (see 1)

5. Incomplete patrol - a patrol that contained some tags (less than perfectpatrol percentage), but not none. Worth noting here, a tag can be identified by the occurrence of 'TAG COMPLETED' in the 'type' coloumn.

6. Failed patrol - when no tags are checked in a patrol (so if in a unique patrolID there are no 'TAG COMPLETED' values in the 'type' coloumn).

7. Total alarms raised - the number of times 'CALLME' 'PANIC' or 'MANDOWN' occurs in the 'type' coloumn.

8. Call Me Alarms - the number of times 'CALLME' occurs in the 'type' coloumn.

9. Panic Alarms - the number of times 'PANIC' occurs in the 'type' coloumn.

10. Man Down Alarms - the number of times 'MANDOWN' occurs in the 'type' coloumn.

Things to note: 1. there can sometimes be multiple routes per site. It would be preferrential for each route to have its own row in this case, sharing the same site name. 2. Each report is over 24 hours, from 6am to 6am, and for the previous day. So an overriding filter on the Data Import table would ensure this.

I have no idea how to approach this due to the sheer size and complexity of all of these operations existing within one huge SQL query. As was mentioned, we use an in house paas that requires little coding, hence me reaching out for advice/help.

How do I go about tackling this problem?

asked 4 mins ago

Chen_Dogg

28

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