Question about getting values from range of dates - Mailing list pgsql-general

From Mike Martin
Subject Question about getting values from range of dates
Date
Msg-id CAOwYNKY+Siukf6G81BZg4hfrBsReTR43DSMi3EfwU3T11b5qmA@mail.gmail.com
Whole thread Raw
Responses Re: Question about getting values from range of dates
List pgsql-general
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

pgsql-general by date:

Previous
From: Moreno Andreo
Date:
Subject: Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data
Next
From: Thiemo Kellner
Date:
Subject: Re: Question about getting values from range of dates