How to enter your own SQL Statements

We try to provide every possible report needed to run your business, and provide the ability to answer any question you can imagine, using the filters. But more reports just for the sake of adding a number to our marketing literature is not the best solution. Sometimes, more is more confusing that helpful. But if you have a question that we can’t answer with reports or filters, there is often another solution.

The program stores data in a database based on SQL (Structured Query Language). What that means in English is that it uses a standard way to put data in and get information out. So in most cases, and with the help of a good SQL tutorial, you should be able to answer any question you can think of.

For example; “How many customers do I have?”. Actually that’s an easy one, because we have a report (Customer List) which answers that one. How about “How many customers who are on Oxygen therapy; E1390?”. Again, too easy, because you can use a filter for the HCPCS code on a Revenue report. Let’s try again, “How many active customers do I have?”. Ok, that ones more difficult, but we have to define what we mean by “active”. Let’s define it as any customer who has purchased/rented something in the current year.

First we define which table we want to use (you may have to consult a data dictionary; via IBExpert or other 3rd party program for that information). This one is easy, we want to use the Customer table (conveniently labeled CUSTOMER), and print the Customers name (the fields are LAST, FIRST, MIDDLE, GENERATION).

select distinct LAST, FIRST, MIDDLE, GENERATION from CUSTOMER

Then we want to connect it to the Claims table (conveniently labeled CLAIM) with the following:

inner join CLAIM on (CLAIM.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID)

Next, we want to limit what defines a Current Customer as someone who has made a purchase in the current year with a Where clause such as:

where CLAIM.POSTED > "01/01/2007"

Finally, enter that completed SQL command into the SQL Editor and press F9 to execute it and see the results.

Note: Don’t worry if this seems a bit foreign to you. Pickup a beginner’s book on SQL and before you know it, you’ll be creating your own SQL statements.