Hi
I am looking for suggestions about the best way to resolve this problem.
Scenario
I have entries for files in database which lack specific values (xml files not generated)
These values can be obtained by what are called DTC read files, where the values are the same in the last DTC read file before date of file and the next DTC read file (by date)
This code works but looks horrendous, so would appreciate any ideas.
thanks
Mike
select max(a.recordingdate) ,max(b.recordingdate) ,a.registration,max(b.filename)
from create_tdms a
join (select registration,recordingdate,filename from create_tdms where filename not like 'DTC%') b
on b.registration=a.registration
where b.recordingdate<a.recordingdate and a.filename like 'DTC%'
group by a.registration
union
select max(a.recordingdate) ,max(b.recordingdate) ,a.registration,max(b.filename)
from create_tdms a
join (select registration,recordingdate,filename from create_tdms where filename not like 'DTC%') b
on b.registration=a.registration
where b.recordingdate>a.recordingdate and a.filename like 'DTC%'
group by a.registration