ElevateDB (latest release), Delphi 2007, Windows VISTA

Background information
---------------------------
I am creating an application that will track multiple values for each client for "case management" purposes. Typical setup will have about 12-15
users (case managers) and about 30,000 clients (with about 10% annual attrition / addition rate) and relatively low transaction volume (about
5000 transections per DAY - mostly new data entries in data tables described below). For sake of simplicity, current plan is to use a Non-CS setup
but you may want to comment if CS setup will be a better approach given the info provided above.

General Table Structures (truncated) are like:
----------------------------------------------------

Client Table: ClientID, ClientName, DateOfBirth, Gender, ...

Glucose Data Table:     ClientID, GlucoseDate, GlucoseValue,..
BP Data Table:             ClientID, BPDate, SystolicBP, DiastolicBP, ...
And MANY others "Data" Tables along the same lines...

As BP, Glucose and other values are collected over time, each client will have multiple records in each "Data" table. However, we will generally be
interested in the most recent value only (more below).


I need to be able to show clients' "Current Status Views" in tabular form in DBGrid.
(I am not using "view" in SQL sense though that may be the answer)
The fields in DBGrid can be read-only (no need to be editable).

=== Views using Data values from just ONE Data table ===

View 1 -- ClientName, DateOfBirth, GlucoseDate, GlucoseValue
View 2 -- ClientName, DateOfBirth, BPDate, SystolicBP, DistolicBP

== OR ===  Views using Data values from multiple Data tables ===

View 3 -- ClientName, DateOfBirth, SystolicBP, DistolicBP, GlucoseValue  
View 4 -- ClientName, DateOfBirth, SystolicBP, DistolicBP, GlucoseValue, X_Data_Value, Y_Data_Value, Z_Data_value

And more "Current Status Views" along these lines...


In principle, what I need to do is:
--------------------------------
For each client, find *** THE MOST RECENT record ** in the respective data table(s).
Use value(s) in that/those record(s) to create a "Current Status View" for each client that can be shown in DBGrid.

Searching the ElevateDB Newsgroup, I gather there is no "TOP" predicate. So I am lost how I will pickup value(s) in "THE MOST RECENT RECORD"
from multiple data tables.

I would think this is "bread and butter" issue in SQL. May be some nested SELECTs - I am just not seeing the light...


What I need is:
---------------
Some explanation of right approach and preferably a code example to get me started in the right direction. I can do this with "Brute Force"
method, but I know that will not be the right choice in the long run.

Finding "the most efficient approach" is IMPORTANT as users will be loading different views repeatedly throughout the day as they work with
different clients.

Responses shorter than 10 lines get zero points

Thank you


Jay M

Jay,

<< In principle, what I need to do is:
--------------------------------
For each client, find *** THE MOST RECENT record ** in the respective data
table(s).
Use value(s) in that/those record(s) to create a "Current Status View" for
each client that can be shown in DBGrid.

Searching the ElevateDB Newsgroup, I gather there is no "TOP" predicate. So
I am lost how I will pickup value(s) in "THE MOST RECENT RECORD" from
multiple data tables. >>

What you want is the RANGE clause in ElevateDB:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=11&topic=171

The single-table views are fairly easy to accomplish with a simple SELECT
with the RANGE.  The multi-table views are a little harder, but not if you
use derived tables.  For example, here is view 3:

CREATE TABLE Client
(
ClientID INTEGER,
ClientName VARCHAR(30),
DateOfBirth DATE,
Gender CHAR(1)
)

CREATE TABLE Glucose
(
ClientID INTEGER,
GlucoseDate DATE,
GlucoseValue INTEGER
)

CREATE TABLE BP
(
ClientID INTEGER,
BPDate DATE,
SystolicBP INTEGER,
DiastolicBP INTEGER
)

SELECT ClientID,
ClientName,
DateOfBirth,
(SELECT SystolicBP
FROM BP
WHERE BP.ClientID=Client.ClientID
ORDER BY BPDate DESC
RANGE 1 TO 1) AS SystolicBP,
(SELECT DiastolicBP
FROM BP
WHERE BP.ClientID=Client.ClientID
ORDER BY BPDate DESC
RANGE 1 TO 1) AS DiastolicBP,
(SELECT GlucoseValue
FROM Glucose
WHERE Glucose.ClientID=Client.ClientID
ORDER BY GlucoseDate DESC
RANGE 1 TO 1) AS GlucoseValue
FROM Client
ORDER BY ClientID

However, while executing a query I noticed that there's a bug in the latest
EDB (2.01 B5) when you use the RANGE clause in a correlated sub-query.  The
range doesn't get refreshed for multiple executions, so it will return
incorrect data.  I will make sure that this is corrected in the 2.02 release
due out next week, and you'll be able to use the above query just fine then.

--
Tim Young
Elevate Software
www.elevatesoft.com

<< The multi-table views are a little harder, but not if you use derived
tables. >>

Scratch that comment - it should have read "correlated sub-queries" instead
of "derived tables".

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Thank you so much for clear and detailed reply. I really appreciate you taking time not only to write code example but also test it.

What do you think of following approach to arrive at the same result: Create a "MasterData" table which will have EXACTLY ONE record for each
client and will look like:

ClientID, GlucoseDate, GlucoseValue, BPDate, SystolicBP, DiastolicBP, X_ItemDate, X_ItemValue, Y_ItemDate, Y_ItemValue....

For a given client, when a new data value is added (Glucose, BP, etc), we update the respective fields in the "MasterData" table. In effect,
the "MasterData" table IS THE MOST CURRENT VIEW and we can do a simple query to show the needed fields in DBGrid out of this table.

Considering pros and cons of getting the "Current Status Views" using nested Selects (your code example earlier) versus using MasterData table,
which one do you think is a better and more efficient approach? If you think using MasterData table is a viable approach - what are likely pitfalls
to watch for?

Thanks again,

Jay M

Jay


In the scenario you've described I'd opt for the additional MasterData table. No matter how fast Tim makes queries picking a single record out of a table (properly indexed) will always be faster than selecting from several tables. The only trap is keeping things in sync and with ElevateDB that should be a doddle - use Triggers on the base tables to ensure that this happens. The only downside is that inserting a new value will now take longer (not that the users should notice since you're also going to be checking in MasterData to see if the ClientID exists and if so updating it, if not creating and updating.

Roy Lambert [Team Elevate]

ps As a diabetic I would have thought you wanted trend data rather than spot?

Jay M <mavi@netacs.net> wrote:

ElevateDB (latest release), Delphi 2007, Windows VISTA

Background information
---------------------------
I am creating an application that will track multiple values for each client for "case
management" purposes. Typical setup will have about 12-15
users (case managers) and about 30,000 clients (with about 10% annual attrition / addition
rate) and relatively low transaction volume (about
5000 transections per DAY - mostly new data entries in data tables described below). For
sake of simplicity, current plan is to use a Non-CS setup
but you may want to comment if CS setup will be a better approach given the info provided
above.

------------------------------------
Jay,

I think you will find your system will be much simpler with a structure similar to the one
below.  SystolicBP, DistolicBP, Glucose are tests.  It is easy to add additional tests and
to find the query to find the latest results for each test for each client is something
like . . .

SELECT Client.ID, Max(EpisodeDate), TestResult.Testid, Test.Name, TestResult.Result
  FROM Client
     INNER JOIN ClientEpisode ON Client.ID = ClientEpisode.ClientID
     INNER JOIN TestResult ON ClientEpisode.ID = TestResult.ClientEpisodeID
     INNER JOIN Test ON TestResult.TestID = Test.ID
  GROUP BY Client.ID, TestID


CREATE TABLE "Client"(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
"LastName" VARCHAR(16) COLLATE "ANSI" NOT NULL,
"FirstName" VARCHAR(16) COLLATE "ANSI",
. . .
)

CREATE TABLE "ClientEpisode"(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
"ClientID" INTEGER NOT NULL,
"EpisodeDate" TIMESTAMP NOT NULL,
. . .
)

CREATE TABLE "Test"(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
"Name" VARCHAR(24) COLLATE "ANSI_CI" NOT NULL,
"MinValue" FLOAT,
"MaxValue" FLOAT,
"UnitOfMeasure" VARCHAR(10) COLLATE "ANSI_CI",
. . .
)

CREATE TABLE "TestResult"(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
"ClientEpisodeID" INTEGER NOT NULL,
"TestID" INTEGER NOT NULL,
"Result" FLOAT,
. . .
)


Also note that there is an ISO Standard for Sex ( 0 = not known,  1 = male,  2 = female,
9 = not applicable)

Richard Harding

Jay,

<< Considering pros and cons of getting the "Current Status Views" using
nested Selects (your code example earlier) versus using MasterData table,
which one do you think is a better and more efficient approach? If you think
using MasterData table is a viable approach - what are likely pitfalls to
watch for? >>

The nested SELECT version is actually pretty efficient, with each nested
SELECT simply being the equivalent of a Locate type of operation for each
ClientID row.  So, I would say that it would be best to stay with the nested
SELECTs unless you encounter a performance issue that would dictate
otherwise.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy:
<Spot vs Trend issue > Master table is simply to identify "at a glance" who is in good control and who is not. When case manager selects a row
(client), they will have the option to see form(s) with TRENDS of relevant values, e.g., the "Metabolic Syndrome Form" may show recent A1c,
Glucose, Lipids, BP, etc. A "Mental Health Form" may show recent scores for Depression, GAF, Suicidality, etc. Case managers will use such forms
when dealing with an individual patient.

Richard:
Thanks for your detailed reply. I have considered keeping all the tests in one table as you suggest. I agree it will certainly simplify things.
However, test result values can be integers, floats or even non-numeric. At times, we have to pick-up "values in a sets" and display them
according to medical conventions, e.g., Systolic BP and Diastolic BP, though separate values are typically showns as 130/80; Cholesterol is always
shown a 4 column: Total, HDL, LDL and Triglycerides, etc. Further, as I commented for Roy above, e.g., it will be easier to show "Most recent 10
records" from each table when showing relevant test trends on "Metabolic Syndrome Form."

"ISO Standard for Sex"  - I thought it is supposed to be free form... when moment is right... [Cialis anyone?]


Tim:
As application takes shape, I will perhaps try both approaches.

Thank you all so much for taking time to provide this valuable feedback.

Jay M