Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
How to create "Views" using ONE latest value from multiple data tables. |
Fri, Sep 12 2008 10:49 PM | Permanent Link |
Jay M | 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 |
Sat, Sep 13 2008 7:04 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sat, Sep 13 2008 7:18 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Sat, Sep 13 2008 9:44 AM | Permanent Link |
Jay M | 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 |
Mon, Sep 15 2008 2:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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? |
Mon, Sep 15 2008 8:36 AM | Permanent Link |
Richard Harding | 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 |
Mon, Sep 15 2008 10:10 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Sep 16 2008 10:06 PM | Permanent Link |
Jay M | 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |