From: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
---|---|
To: | pdxpug(at)postgresql(dot)org |
Subject: | SQL Query Help Needed |
Date: | 2007-05-08 21:00:49 |
Message-ID: | Pine.LNX.4.64.0705081347370.10607@salmo.appl-ecosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pdxpug |
I've no experience with temporal queries, but I need to learn how to write
them right now. I've been working on one for more than a day and I just
don't see how to extract the information we need.
There are two tables involved: Permits and Permit_History. Pertinent
columns in Permits are:
permit_nbr VARCHAR(16) NOT NULL PRIMARY KEY,
title VARCHAR(64) DEFAULT ' ' NOT NULL,
...
monitoring_rpt_freq INTERVAL DEFAULT '3 MONTHS' NOT NULL
and in Permit_History:
permit_nbr VARCHAR(32) REFERENCES Permits(permit_nbr),
...
action_date DATE DEFAULT CURRENT_DATE NOT NULL,
action_type VARCHAR(32) DEFAULT '' NOT NULL,
CONSTRAINT valid_action_types
CHECK (action_type IN ('Compliance report submitted', 'Renewal appl. submitted',
'Renewal appl. acknowledged', 'Renewal issued',
'Notice received', 'Notice resolved', 'Terminated')),
What we need to extract are rows where a monitoring report has not yet
been submitted to the requlatory agency for the current quarter, but should
be done within the next two weeks.
The mess I currently have is this:
SELECT EXTRACT QUARTER FROM CURRENT_DATE AS Q1, EXTRACT QUARTER FROM
MAX(Permit_History.action_date) AS Q2, Permits.permit_nbr, Permits.title,
Permits.monitoring_rpt_freq, Permit_History.action_date,
Permit_History.action_type FROM Permits, Permit_History
WHERE Permits.monitoring_rpt_freq = '3 MONTHS' AND Q1 = Q2 AND
Permit_History.action_type <> 'Compliance report submitted'
but it's not correct. (We don't yet have data in the tables so I cannot
report just how postgres barfs on this query.)
One thing I know is wrong is checking whether the most recent
Permit_History.action_date is in the current calendar quarter when the
associated Permit_History.action_type is that a compliance report has been
submitted.
I'm using the Douglas & Douglas 'PostgreSQL, 2nd Ed.' and Joe Celko's
'SQL for Smarties, 3rd Ed.', and the online docs for Date/Time Functions and
Operators (Section 9.9). These time-dependent queries are quite difficult
for me as they are so new. I'd appreciate some help -- including pointers to
docs or examples -- to help me to quickly master them.
TIA,
Rich
--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas J Keller | 2007-05-12 00:19:47 | schema question |
Previous Message | Selena Deckelmann | 2007-05-08 17:26:50 | Re: PostgreSQL Maestro |