Heirrachical recordsets - Compute statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Heirrachical recordsets - Compute statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-09-07 : 11:59:40

Hywel writes "
I want to generate a disease register report my my medical practice. The format of the report needs to be as follows:

<Practice1>
     <Disease1 total="number of patients with Disease1 in Practice1"> 
          <condition1 total="number of patients with Disease1 in Practice1 with specific condition">
               <patient surname="" forename="" dob="" />
               <patient surname="" forename="" dob="" />
          </condition1>
          <condition2 total="number of patients with Disease1 in Practice1with specific condition2">
               <patient surname="" forename="" dob="" />
               <patient surname="" forename="" dob="" />
          </condition1>
     </Disease1>
</Practice1>
<Practice2> etc ...

I would like to return a heirrachical recordset that uses compute and count

I thought the best way to achieve this would be with one shape command from the underlying database to return a heirrachical recordset. Then convert to XML.

I am struggling with the shape compute syntax.

The following shape command returns the data but fails to count the records for the total attributes above. can you help point me in the right direction.

The fist select statement returns the names of the Practice sites to which the patients are registered.

I need to total the ENTRY.READ_CODE column in the second statement. ie total for each (Disease1-3, above) C2A.. , C21.. and C22..

Then beneath each disease identify patients with condition attributes and total these too using the READ_CODE column ie 44P.. is a cholesterol value and 2469 is a blood pressure value.

I hope the shape command is self explanatory!!!

Thanks, my patients will be a hell of a lot better if i can sort this, cheers

Hywel

SHAPE 
{
SELECT DISTINCT SURGERY.SURGERY_NAME 
FROM SURGERY 
WHERE SURGERY.SURGERY_NAME <> ''
} AS Practice 
APPEND 
(
(
 SHAPE 
{
SELECT ENTRY.READ_CODE, ENTRY.PATIENT_ID, SURGERY.SURGERY_NAME, MAX(ENTRY.START_DATE) AS START_DATE 

FROM ENTRY, SURGERY 
JOIN PATIENT 
ON ENTRY.PATIENT_ID = PATIENT.PATIENT_ID 
AND PATIENT.SURGERY_ID = SURGERY.SURGERY_ID 
AND (
(ENTRY.READ_CODE = 'C2A..') OR (ENTRY.READ_CODE = 'C21..') 
OR 
(ENTRY.READ_CODE = 'C22..')
)
 AND ((PATIENT.MAIN_REG_TYPE = 1)) 
GROUP BY ENTRY.READ_CODE, ENTRY.PATIENT_ID, SURGERY.SURGERY_NAME
} AS Def_Diabetes 

APPEND 
(
{
SELECT ENTRY.PATIENT_ID, PATIENT.SURNAME, PATIENT.FORENAME_1, PATIENT.FORENAME_2, PATIENT.DATE_OF_BIRTH, MAX(ENTRY.START_DATE) AS START_DATE 

FROM ENTRY 
JOIN ENTRY_ATTRIBUTE 
ON ENTRY_ATTRIBUTE.ENTRY_ID = ENTRY.ENTRY_ID 
JOIN PATIENT 
ON ENTRY.PATIENT_ID = PATIENT.PATIENT_ID 
AND ENTRY.READ_CODE = '44P..' 
AND ENTRY_ATTRIBUTE.NUMERIC_VALUE > 5 
AND (ENTRY.PATIENT_ID = ?) 
GROUP BY ENTRY.PATIENT_ID, PATIENT.SURNAME, PATIENT.FORENAME_1, PATIENT.FORENAME_2, PATIENT.DATE_OF_BIRTH
} AS cmdCholgr5 

RELATE 'PATIENT_ID' TO PARAMETER 0
) AS cmdCholgr5,


(
{
SELECT ENTRY.PATIENT_ID, PATIENT.SURNAME, PATIENT.FORENAME_1, PATIENT.FORENAME_2, PATIENT.DATE_OF_BIRTH, MAX(ENTRY.START_DATE) AS START_DATE 

FROM ENTRY 
JOIN ENTRY_ATTRIBUTE 
ON ENTRY_ATTRIBUTE.ENTRY_ID = ENTRY.ENTRY_ID 
JOIN PATIENT 
ON ENTRY.PATIENT_ID = PATIENT.PATIENT_ID 
AND ENTRY.READ_CODE = '2469.' 
AND ENTRY_ATTRIBUTE.NUMERIC_VALUE > 160 
AND (ENTRY.PATIENT_ID = ?) 
GROUP BY ENTRY.PATIENT_ID, PATIENT.SURNAME, PATIENT.FORENAME_1, PATIENT.FORENAME_2, PATIENT.DATE_OF_BIRTH
} AS cmdSysgr160 
RELATE 'PATIENT_ID' TO PARAMETER 0
) AS cmdSysgr160,

(
{
SELECT ENTRY.PATIENT_ID, PATIENT.SURNAME, PATIENT.FORENAME_1, PATIENT.FORENAME_2, PATIENT.DATE_OF_BIRTH, MAX(ENTRY.START_DATE) AS START_DATE 

FROM ENTRY 
JOIN ENTRY_ATTRIBU

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources