Thread: Question about getting values from range of dates

Question about getting values from range of dates

From
Mike Martin
Date:
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

Re: Question about getting values from range of dates

From
Thiemo Kellner
Date:
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.