Thread: Question about getting values from range of dates
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
Hi Mike
Zitat von Mike Martin <redtux1@gmail.com>:
> 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.
Bit is formatted so little?
Sorry, I did not quite get the functional specification here, so I
just put forward a technical optimised proposal. It seems to me
equivalent to the following. Note I used Union ALL because the data
sets of the two cases seem to be disjoint to me. The ALL makes
PostgreSQL leave out the discarding process of identical rows (in the
complete set, i. e. if there are identical rows in one part of the set
they will be singled as well). Column aliasing is partly necessary
partly to be clearer.
with BASE as
(
select a.recordingdate as recordingdate_a,
b.recordingdate as recordingdate_b,
a.registration as registration_a,
b.filename as filename_b
from create_tdms a
join (
select registration,
recordingdate,
filename
from create_tdms
where filename not like 'DTC%') b
on b.registration = a.registration
where a.filename like 'DTC%'
)
select max (recordingdate_a) as max_recordingdate_a,
max (recordingdate_b) as max_recordingdate_b,
registration_a as registration,
max (filename_b) as filename
from BASE
where recordingdate_b < recordingdate_a
group by registration_a
union all
select max (recordingdate_a) as max_recordingdate_a,
max (recordingdate_b) as max_recordingdate_b,
registration_a as registration,
max (filename_b) as filename
from BASE
where recordingdate_b > recordingdate_a
group by registration_a
Kind regards Thiemo
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.