Hospital is a database with one table, which is titled as Encounter. The table stores a series of hospital encounters by patients. Length of stay (LOS) is the difference between the discharge and admission dates of a patient (i.e., how long the patient stayed in the hospital). Note that LOS is not an attribute in the table but can be computed as said above. Write a single query that, for each record, shows a running average of LOS for all previous encounters in the database. The average must be in days (as the unit).

Output should look like this:

Since no encounters existed before the first encounter, avgLOS will be null. For the second encounter, the avgLOS is equal to the LOS of the first encounter. For the third encounter, avgLOS is equal to the average of LOS for the first and second encounters and so on. Note that in the output shown above, records are ordered by their admission date; that is, EncounterID does not imply order of encounters. NOTE: You must write the query assuming that there will be more records in the table in future; therefore, your query must work for 20, 30, 50, or 1000 records in the table.

Here is the code that I have so far. The problem that I am having is what is bolded above

create schema hospital; use hospital;

create table encounter (EncounterID int not null, PatientID int not null, DOB date, Race char(25), Gender char(1), AdmissionDate datetime, dischargeDate datetime, primary key (EncounterID));

insert into encounter values (1000000, 1, '1970-03-15', 'White', 'F', '2019-06-25 12:35:23', '2019-06-27 09:45:37'), (1000001, 2, '1955-05-04', 'Black', 'M', '2019-03-30 23:05:00', '2019-04-8 10:15:00'), (1000002, 3, '1940-11-24', 'Asian', 'F', '2018-10-12 15:45:45', '2018-10-20 13:30:35'), (1000003, 1, '1970-03-15', 'White', 'F', '2019-08-11 18:55:00', '2019-08-18 14:32:00'), (1000004, 4, '1939-07-20', 'White', 'M', '2015-02-12 20:25:10', '2015-02-28 11:40:00'), (1000005, 3, '1940-11-24', 'Asian', 'F', '2017-09-12 06:05:00', '2017-09-20 08:10:15'), (1000006, 5, '1945-12-25', 'Alaskan', 'M', '2018-11-18 06:45:05', '2018-12-01 08:40:30'), (1000007, 6, '1980-01-15', 'Black', 'M', '2019-09-25 09:33:08', '2019-09-28 08:19:35'), (1000008, 6, '1980-01-15', 'Black', 'M', '2020-02-10 16:23:24', '2020-02-14 19:58:23'), (1000009, 7, '2012-08-13', 'White', 'F', '2020-01-14 13:45:59', '2020-01-16 10:00:28');

Select *, DATEDIFF(AdmissionDate,dischargeDate)AS LOS, AVG(DATEDIFF(AdmissionDate,dischargeDate)) OVER(Order BY AdmissionDate Asc ROWS 9 Preceding ) AS avgLOS FROM encounter GROUP BY EncounterID,PatientID,DOB,Race,Gender,AdmissionDate,dischargeDate Order BY AdmissionDate;

Encounter ID Patient DOB Rece Gender Admission Date dischargeDate LOS VOLOS 1000004 1939-07-20 White M 2015-02-12 20:25:10 2015-02-28 11:40:00 15.6353 1000005 3 1940-11-24 Aslan F 2017-09-12 06:05:00 2017-09-20 08:10:15 8.0870 15.63530093 1000002 3 1940-11-24 Asian F 2013-10-12 15:45:45 2013-10-20 13:30:35 7.9051 11.86114005 1000005 5 1945-12-25 Alaskan M 2018-11-18 06:45:05 2018-12-01 08:40:30 13.0602 10:54280478 1000001 2 1955-05-04 Black M 2019-03-30 23:05:00 2019-04-08 10:15:00 8.4653 11.17714120 1000000 1 1970-03-15 White F 2019-06-25 12:35:23 2019-06-27 09:45:37 1.8821 10.63476852 1000003 1 1970-03-15 White 2019-08-11 18:55:00 2019-08-18 14:32:00 6.8174 9.17599151 1000007 6 1980-01-15 Black M 2019-09-25 09:33:08 2019-09-23 08:19:35 2949 8.8390431 1000009 7 2012-08-13 White F 2020-01-14 13:45:59 2020-01-16 10:00:23 1.8434 8. 10277922 1000008 6 1980-01-15 Black M 2020-02-10 16:23:24 2020-02-14 19:58:23 4.1493 7.40729167