SELECT MIN DATE FROM SCRIPT LOAD

[#1]

Hi ,

How can i amend my script load to select only min [Appointment Date] for each [Patient ID] ?

Below is my current load script.

---------------

LOAD
[Patient ID],
Date([Appointment Date],'DD-MM-YY') As [Appointment Date],
Date(MonthStart([Appointment Date]), 'MMM-YY') as Month,
[Patient Surname],
[Patient Forename 1],
[Patient DOB],
[Sex Code],
[Address]
FROM
[\Activity from MDS.txt]
(txt, utf8, embedded labels, delimiter is '\t', msq);

-------------

Any ideas?

[hector · Accepted solution]

Hi, try this

t1:
LOAD
 [Patient ID],
 Date([Appointment Date],'DD-MM-YY') As [Appointment Date],
 Date(MonthStart([Appointment Date]), 'MMM-YY') as Month,
 [Patient Surname],
 [Patient Forename 1],
 [Patient DOB],
 [Sex Code],
 [Address]
FROM
[\Activity from MDS.txt]
(txt, utf8, embedded labels, delimiter is '\t', msq);
inner join(t1) //so, selecting the min date and joining, will be exclude the other dates
Load
[Patient ID],
MIN( [Appointment Date]) as [Appointment Date]
resident t1
group by
 [Patient ID]
;

Rgds