General requirements: SQLape users must prepare a SQLape_input.txt file, which is the unique input file required. In this file, each row corresponds to a new hospital stay and must include all the variables listed in the table below. Fields name should not be included in the file (first line of the file = first record). Each field must be separated by a semicolon (“;”) and each record must be ended by a carriage return compatible with Windows. If it is not the case, open the file with the windows’ application “Bloc-note” and save it.

SQLape® is updated every year with the latest valid international nomenclatures: ICD-10 (WHO version) for diagnosis and ICD-9-CM (US version). Former codes are transcoded in updated codes to allow temporal comparisons (from 1998 to now). (Swiss nomenclatures)

FieldsSwiss Federal Office of Statistics designation (Swiss nomenclatures)Format 
Field 1Hospital stay identifier-text
Field 2Hospital identifier0.1.V02text
Field 3Location0.1.V03text
Field 4Patient identifier0.2.V01text
Field 5Stay definition0.2.V02text
Field 6Gender1.1.V01numberinteger
Field 7Age at admission1.1.V03numberinteger
Field 8Residence1.1.V04text
Field 9Admission date1.2.V01YYYYMMDDhh(text)
Field 10Stay before admission1.2.V02Numberinteger
Field 11Admission mode1.2.V03Numberinteger
Field 12Type of stay1.3.V01Numberinteger
Field 13Vacations1.3.V04Numberinteger
Field 14Department1.4.V01text
Field 15Discharge date*1.5.V01YYYYMMDDhh(text)
Field 16Discharge decision1.5.V02numberinteger
Field 17Stay after discharge1.5.V03numberinteger
Field 18Length of stay-numberinteger
Field 19Hospital cost-numberreal
Field 20Weight at birth2.2.V04numberinteger
Field 21Main diagnosis4.2.V010text
Field 22Complement to main diagnosis4.2.V020text
Fields 23-71Diagnoses 2 to 504.2.V030-4.2.V510text
Field 72Procedure 14.3.V010text
Field 73Procedure 24.3.V020text
Fields 74-171Procedures 3 to 1004.3.V030-4.3.V1000text
Field 172Procedure 1 date4.3.V015YYYYMMDD(text)
Field 173Procedure 2 date4.3.V025YYYYMMDD(text)
Fields 174-271Procedure 3 to 100 dates4.3.V035-4.3.V1005YYYYMMDD(text)
Field 2721 st  interruption, discharge date4.7.V01YYYYMMDD(text)
Field 2731 st  interruption, readmission date4.7.V02YYYYMMDD(text)
Field 274Reason of 1 st  interruption4.7.V03numberinteger
Field 2752 nd  interruption, discharge date4.7.V11YYYYMMDD(text)
Field 2762 nd  interruption, readmission4.7.V12YYYYMMDD(text)
Field 277Reason of 2 nd  interruption4.7.V13numberinteger
Field 2783 rd  interruption, discharge date4.7.V21YYYYMMDD(text)
Field 2793 rd  interruption, readmission4.7.V22YYYYMMDD(text)
Field 280Reason of 3 rd  interruption4.7.V23numberinteger
Field 2814 th  interruption, discharge date4.7.V31YYYYMMDD(text)
Field 2824 th  interruption, readmission4.7.V32YYYYMMDD(text)
Field 283Reason of 4 th  interruption4.7.V33numberinteger
Field 284Stay after the first interruption4.8.V17numberinteger
Field 285Stay after the second interruption4.8.V18numberinteger
Field 286Stay after the third interruption4.8.V19numberinteger
Field 287Stay after the fourth interruption4.8.V20numberinteger

Important technical specifications: The stay identifier must be unique in the whole SQLape_input file, not only for each hospital or site. Do not use the underscore “_” character in the identifier.

The patient identifier must be unique for the whole calculation period, independently of the year (important to calculate readmissions’ rate).

Missing dates must be empty (no character).

ICD-10 diagnostic and ICD-9-CM procedure codes can be used with or without point (47.01 or 4701 for instance are both acceptable).

ICD-9-CM codes must be in text format (0109 is different from 109 for instance)

Fields 18 and 19 are only required to compute length of stay and hospital cost. They can be kept empty for other indicators. Hospital cost is not included in hospital medical records; this information corresponds to the total cost computed for SwissDRGs. The length of stay is defined as follows:

Discharge date – Admission date + 1 – Vacation duration (hours/24).

Temporal delimitation: The usual setting includes all discharges from January 1st to December 31th, but shorter periods might also be used. If several years are included in the SQLape_input.txt file, the analysis will take into account the discharges of the more recent year if they represent more than 10% of cases; otherwise, the analysis will be applied to the year including the most cases.

If you are interested in readmissions indicators, please also consider following recommendations:

  1. To compute unbiased observed rates, the input file must include other available hospitals to identify external readmissions.
  2. To compute unbiased expected rates, the input file must be extended to June 1stof the previous year. This extension is necessary to adjust for possible six months previous hospital stays.

Data quality assessment: It is recommended to assess the quality of data before to interpret the results (data quality)

Tips: If you want to have results with your own hospital and patient identifiers or medical services you may need to update the fields 1, 4 or 14.

If you are not interested in results per location, you can use the field 3 to use more detailed units (maximum of 50).