Below is a query I created that works ok. I need the MINIMUM admdate by provider by linkno (I.E. the first admit for each patient in a hospital or health system)
select min(admdate) as MinAdmitDate, providerhs, linkno from PatientDataArr20132
group by providerhs, linkno
union
select min(admdate) as MinAdmitDate, providerhs, linkno from PatientDataArr20131
group by providerhs, linkno
order by providerhs, linkno, MinAdmitDate
What I need to do now is add all the columns to the query so that I can build a new table of the same structure but with one extra column. I ‘m sure I need an embedded select statement but I just can’t place it correctly. Can someone please help? .
PatientDataArr20131 is the input table
EventDateArr20131 is the output table, same as above but with one additional column
I have attached a screenshot with the structures of the two tables.
*, MIN(admdate) OVER(PARTITION BY providerhs, linkno) AS MinAdmitDate
--INTO <new_table_name>
FROM PatientDataArr20132