Many inquiries from clients involve requests to find specific information. The .NET system allows users to easily query their database to get the information they are looking for. In this installment of our FAQ blog series, I’ll share a recent use case of the Ad Hoc Query tool.
Tool: Ad Hoc Query
A user had been performing a considerable amount of cession maintenance and was concerned they may have affected their treaty pools. The request was to perform a quick, high level check to assure that they weren’t missing any cessions in the pools.
I provided them with the following query, which they ran through the Ad Hoc Query tool in TAI .NET:
Select t1.cess_co, t1.cess_pol, t1.cess_cov, SUM(t2.trty_treaty_pct) as PercentCeded
From taicess t1, taitrty t2
Where t1.cess_treaty_no = t2.trty_treaty_no and t1.cess_treaty_no = t2.trty_treaty_no and t1.cess_status = 'pmp' and t1.cess_reins_date > 20160101
Group by t1.cess_co, t1.cess_pol, t1.cess_cov
Having SUM(t2.trty_treaty_pct) < .999999
Order by t1.cess_co, t1.cess_pol, t1.cess_cov
How the query works:
- The query is taking all premium paying (PMP) cessions from 1-1-2016 on
- It cross references the treaty table to find the treaty percent
- It then sums up the treaty percents by co, pol, cov
- Finally, it reports out any policies that total less than .99999
Note: I used .99999 instead of 1.0 because we received a lot of .99999s in testing, but clearly that rounds to 1.00. That would not indicate missing cessions.
The query allowed them to find exactly what they were looking for and do so in a quick, simple way. The user was thrilled with both the results and the method. Plus, by saving the query, they can run the same test to verify issues in the future.
How to use this query to verify issues in your cession pools?
If you want to try this query, it can just be copied and pasted into Ad Hoc Query (Utilities > Ad Hoc Query). I suggest outputting the data to Excel so you can have a working document.
PRO TIP #1: Define as much criteria as possible.
The more specific you can be, the fewer the records returned. This benefits both you and the system. While SQL can handle queries that generate large output, that output can overwhelm the screen view. When you start a new query, TAI .NET automatically puts in a “Top 50” limiter in the select clause. This is to protect you from accidentally running a query that generates extremely large output.
If you know you want more than 50 records and you know the output will be reasonable, you can check the “Don’t Add ‘TOP 50’ Result Limit” box. If you think the query will generate a lot of output, then select output to Excel or CSV. That makes it easier for you to work with the data offline anyway. If you output to Excel or CSV, you should be able to pick up your file through Admin > Transfer > Download File from Server.
PRO TIP#2: Try different parameters
Change the 'where' and 'having' parameters in the query to pull a different set of data. For example instead of looking at cessions after 2016, you can change the date to pull information on years prior.
What if the query returns no data?
That’s a good thing! This query is looking to identify potential issues, so the fewer the records the better! However, keep in mind the query relies on the treaty tables having Treaty Share defined for pool members. If that is not the case at your company, you would need a different query.
More tips for using queries:
- Only inquiry type queries can be run without a password of the day, so you will not be able to update or delete data
- If you have little querying experience, try using the Design Query tool first
- Use the Save Query button to save the query for future use
- Use the Load Query button the next time you need it
- Once you save a query, it can be set up to run regularly as part of a cycle too
Looking for more customized queries?
Use CONNECTOR to pull in data quickly without the help of a support team or IT pro. You can specify the information you want to extract using customizable templates. Plus, it allows for many different output formats, as well as scheduling and routing of repeated queries.