Samples of T-SQL queries for applications (for example –  Web Application  (WebApp)).

(For queries stated below It is necessary to pay attention to that the result query   implementation located in the right column are fitted with CUEAV and SourceDB_For_EAV have Cyrillic_General_CI_AS collation)

The order of user’s operations consists of the following steps:

 WebApp executes query  and  gives   user management  tool to select necessary class.  Let’s suppose it will be class Type1_BloodTest where ID = 4.
 

SELECT ClassID, Code

FROM  Class		 662583 
WebApp executes query  to  determine classes for which Type1_BloodTest  is descendant because it is necessary to know identifiers of patient and his hospitalizations.
 

WITH Tree(ClassID, ParentClassID)

            AS

(SELECT ClassID, ParentClassID

  FROM ClassLinks WHERE ClassID = 4

UNION ALL

SELECT p.ClassID, p.ParentClassID

 FROM ClassLinks  P  INNER JOIN

 Tree A ON A.ParentClassID = P.ClassID

)

SELECT Code, Class.ClassID FROM Tree

INNER JOIN Class ON Tree.ClassID = Class.ClassID		  662584
 WebApp executes query.  User selects patient  Cambell where ObjectID = 8.
 

SELECT Attribute.Code AS AttributeCode, Value.Value, Object.ObjectID

FROM  Attribute INNER JOIN

               Class ON Attribute.ClassID = Class.ClassID INNER JOIN

               Object ON Class.ClassID = Object.ClassID INNER JOIN

               Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID

WHERE (Class.ClassID = 3) AND (Attribute.Code='Surname')		  662585
 WebApp executes query.   User selects hospitalization dated 15/05/2012 where OblectID = 2.
 

SELECT Attribute.Code AS AttributeCode, Value.Value, Object.ObjectID

FROM  Attribute INNER JOIN

               Class ON Attribute.ClassID = Class.ClassID INNER JOIN

               Object ON Class.ClassID = Object.ClassID INNER JOIN

               Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID

WHERE (Value.ObjectID IN

(

SELECT Object.ObjectID

FROM  Attribute INNER JOIN

               Class ON Attribute.ClassID = Class.ClassID INNER JOIN

               Object ON Class.ClassID = Object.ClassID INNER JOIN

               Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID

WHERE (Class.ClassID = 1) AND (Value.Value =

(SELECT Value.Value

FROM  Attribute INNER JOIN

               Class ON Attribute.ClassID = Class.ClassID INNER JOIN

               Object ON Class.ClassID = Object.ClassID INNER JOIN

               Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID

WHERE (Value.ObjectID = 8) AND (Attribute.Code = 'Type1_PatientID'))

)

) AND Attribute.Code = 'Date'

)

  662586
 WebApp executes query.  User selects analysis  dated 17/05/2012 where ObjectID = 13.
 

SELECT Attribute.Code AS AttributeCode, Value.Value, Object.ObjectID

FROM  Attribute INNER JOIN

               Class ON Attribute.ClassID = Class.ClassID INNER JOIN

               Object ON Class.ClassID = Object.ClassID INNER JOIN

               Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID

WHERE (Value.ObjectID IN

(

SELECT Object.ObjectID

FROM  Attribute INNER JOIN

               Class ON Attribute.ClassID = Class.ClassID INNER JOIN

               Object ON Class.ClassID = Object.ClassID INNER JOIN

               Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID

WHERE (Class.ClassID = 4) AND (Value.Value =

(SELECT Value.Value

FROM  Attribute INNER JOIN

               Class ON Attribute.ClassID = Class.ClassID INNER JOIN

               Object ON Class.ClassID = Object.ClassID INNER JOIN

               Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID

WHERE (Value.ObjectID = 2) AND (Attribute.Code = 'Type1_HospitalisationID'))

)

) AND Attribute.Code = 'Date'

)		  662587
WebApp executes query to prepare editing date form. User changes values  of Sodium from 140 to 142 and Potassium from 3.5 to 3.3
 

SELECT ValueID, Attribute.Code, Value

FROM  Attribute INNER JOIN

               Class ON Attribute.ClassID = Class.ClassID INNER JOIN

               Object ON Class.ClassID = Object.ClassID INNER JOIN

               Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID

WHERE (Value.ObjectID = 13) AND (Attribute.Code = 'Date' OR Attribute.Code = 'Sodium' OR Attribute.Code = 'Potassium' OR Attribute.Code = 'Urea')		  662588
WebApp executes query to change  CUEAV data.  

UPDATE Value

SET Value='142'

WHERE ValueID = 55

 

UPDATE Value

SET Value='3.4'

WHERE ValueID = 56		  

 

Principle scheme of building the query

2)  SELECT Value.Value

……………………………………………

……………………………………………

WHERE (Attribute.Code = ‘Type1_PatientID’)

AND Value.OblectID IN

 

1)  SELECT Value.ObjectID

     …………………………………………..

     ……………………………………………

   WHERE (Class.code = ’Type1_Patient’) AND (Attribute.Code = ‘Surname’)

    AND (Value.Value = ‘Cambel’)

Subqueries 1) and 2) are iterated for all classes of hierarchy with necessary filters.

 

SELECT Attribute.Code, Attribute.Name, Value.Value, 
Attribute.Unit, Attribute.DataType, Attribute.Min_Value, Attribute.Max_Value, 
Attribute.Min_Norma_Value, Attribute.Max_Norma_Value, 
CASE WHEN CAST(Value as float) < CAST(Min_Norma_Value as float) THEN CAST(Value as float) - CAST(Min_Norma_Value as float)
     WHEN CAST(Value as float) > CAST(Max_Norma_Value as float) THEN CAST(Value as float) - CAST(Max_Norma_Value as float)
     ELSE 0 END
AS Aberration
FROM  Attribute INNER JOIN
Class ON Attribute.ClassID = Class.ClassID INNER JOIN
Object ON Class.ClassID = Object.ClassID INNER JOIN
Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID
WHERE  Class.Code = 'Type1_BloodTest' AND 
(Attribute.Code = 'Potassium')  OR
(Attribute.Code = 'Sodium')  OR
(Attribute.Code = 'Urea')  AND
(Value.ObjectID IN
   (SELECT Value.ObjectID
   FROM   Value
   WHERE (Value.Value IN
    (SELECT Value.Value
    FROM   Attribute INNER JOIN
    Class ON Attribute.ClassID = Class.ClassID INNER JOIN
    Object ON Class.ClassID = Object.ClassID INNER JOIN
    Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID
    WHERE (Attribute.Code = 'Type1_HospitalisationID') AND (Value.ObjectID IN
      (SELECT Value.ObjectID
      FROM   Attribute INNER JOIN
      Class ON Attribute.ClassID = Class.ClassID INNER JOIN
      Object ON Class.ClassID = Object.ClassID INNER JOIN
      Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID
      WHERE (Attribute.Code = 'Date') AND (CASE ISDATE(Value.Value) WHEN 1 THEN CONVERT(datetime, Value.Value, 103) 
      ELSE NULL END >= '20110101') AND (Value.ObjectID IN
        (SELECT Value.ObjectID
        FROM   Attribute INNER JOIN
        Class ON Attribute.ClassID = Class.ClassID INNER JOIN
        Object ON Class.ClassID = Object.ClassID INNER JOIN
        Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID
        WHERE (Class.Code = 'Type1_Hospitalisation') AND (Attribute.Code = 'Type1_PatientID') AND (Value.Value IN
          (SELECT Value.Value
          FROM   Attribute INNER JOIN
          Class ON Attribute.ClassID = Class.ClassID INNER JOIN
          Object ON Class.ClassID = Object.ClassID INNER JOIN
          Value ON Attribute.AttributeID = Value.AttributeID AND Object.ObjectID = Value.ObjectID
          WHERE (Attribute.Code = 'Type1_PatientID') AND (Value.ObjectID IN
            (SELECT Value.ObjectID
            FROM   Attribute INNER JOIN
            Class ON Attribute.ClassID = Class.ClassID INNER JOIN
            Object ON Class.ClassID = Object.ClassID INNER JOIN
            Value ON Attribute.AttributeID = Value.AttributeID AND 
            Object.ObjectID = Value.ObjectID
            WHERE (Class.Code = 'Type1_Patient') AND (Attribute.Code = 'Surname') AND 
            (Value.Value = 'Cambell')))))))))))))