Re: Date operations

On 03/01/2011 08:14 AM, Chetan Suttraway wrote:

What I would like, if possible, is something on the line of "SELECT

DISTINCT ON file ..... WHERE date NOT GREATER THAN somedate [ORDER BY

date desc]" but cannot seem to find a way to do it.

I would greatly appreciate any ideas on this subject, kind regards to all,

Barbara F.

Would it be possible to post the setup in terms of sql queries?

Hello, thanks for your answer.

The view in question has this definition:

--

CREATE VIEW olx_gst_exp AS

SELECT e.exp_name, e.exp_number, g.gest_fecha, g.gest_tipo FROM olx_expedient e, olx_gestiones g WHERE (e.exp_id = g.olx_exp_id) ORDER BY e.exp_name;

whereas the corresponding tables have this setup:

openlex=# \d olx_expedient

Table "public.olx_expedient"

Column | Type | Modifiers

----------------+--------------------------+--------------------------------------------------------------------------

exp_number | text | not null

exp_ext_id | integer |

crt_id | integer |

exp_sec_id | integer |

exp_start | timestamp with time zone | default now()

exp_end | timestamp with time zone |

exp_user_stamp | character varying(64) | default "current_user"()

exp_time_stamp | timestamp with time zone | default now()

exp_id | integer | not null default nextval(('"olx_expedient_exp_id_seq"'::text)::regclass)

exp_name | character varying(64) |

exp_nick | text |

activo | boolean | default true

....

and

openlex=# \d olx_gestiones

Table "public.olx_gestiones"

Column | Type | Modifiers

----------------+--------------------------+--------------------------------------------------------------

gest_id | integer | default nextval(('"olx_gestiones_gest_id"'::text)::regclass)

gest_tipo | text |

gest_fecha | date | default ('now'::text)::date

olx_exp_id | integer | not null

plazo | interval |

fechas_cl | date |

vencim | integer |

timestamp | timestamp with time zone | default now()

fojas | integer |

exp_user_stamp | text | default "current_user"()

I hope this is clear enough, thanks again for your response.

Barbara F.

Chetan Sutrave

http://www.enterprisedb.com

--
Dra. Bárbara M. Figueirido
25 de Mayo 1331
8400 S.C. de Bariloche - RIO NEGRO
Argentina
Tel.-Fax: +54 2944 456252
15682745