PJ
|
| Posted: 07/01/2003, 2:33 PM |
|
Can someone assist please.
What is the correct argument in MySQL that will calculate the earliest date
in a year from a range of dates for one record covering several years?
|
|
|
 |
Robert Sorrells
|
| Posted: 07/02/2003, 5:39 AM |
|
If you were looking for the earliest date in a group of records, In MS-SQL,
it would be something like
SELECT TOP 1 datefield FROM yourdatabase
ORDER BY datefield
WHERE your_filter_criteria
MySQL can do it too, and I've used it. I don't remember the command. Its
not the "TOP 1", but equivalent.
I don't understand the "... for one record covering several years." part of
the problem.
Bob
"PJ" <apis@globalnet.co.uk> wrote in message
news:bdsunk$rvo$1@news.codecharge.com...
Can someone assist please.
What is the correct argument in MySQL that will calculate the earliest date
in a year from a range of dates for one record covering several years?
|
|
|
|
PJ
|
| Posted: 07/02/2003, 6:06 AM |
|
I have a DB with table 1 of records. Table two holds multiple incidents of
T1 based on when records in T1 were seen i.e. the date. The records in T2
cover several years and I want to pull out the earliest sighting in the
calendar year. For example, 21st May would be the earliest until next year
it was seen on 20th April which then becomes the earliest sighting. Liken
it to bird-watchers looking out for the earliest sighting of a bird which
sets the record until superseded.
"Robert Sorrells" <rsorrellsU@KNOWmindspring.com> wrote in message
news:bdujq3$taq$1@news.codecharge.com...
> If you were looking for the earliest date in a group of records, In
MS-SQL,
> it would be something like
>
> SELECT TOP 1 datefield FROM yourdatabase
> ORDER BY datefield
> WHERE your_filter_criteria
>
> MySQL can do it too, and I've used it. I don't remember the command.
Its
> not the "TOP 1", but equivalent.
>
> I don't understand the "... for one record covering several years." part
of
> the problem.
>
> Bob
>
>
> "PJ" <apis@globalnet.co.uk> wrote in message
>news:bdsunk$rvo$1@news.codecharge.com...
> Can someone assist please.
>
> What is the correct argument in MySQL that will calculate the earliest
date
> in a year from a range of dates for one record covering several years?
>
>
>
|
|
|
|
DonB
|
| Posted: 07/02/2003, 6:41 AM |
|
This sounds like a job for "GROUP BY". Since I assume you store the
complete date in the database, this won't be terribly efficient, because in
indexed-lookup won't be possible on the "year" extracted from a date
column - but here goes:
Example:
SELECT Min(Year(DateCreated)) AS TheYear, Min(knowledgebase.DateCreated) AS
Expr1, knowledgebase.Published, Year(DateCreated) AS Expr2
FROM knowledgebase
GROUP BY Year(DateCreated), knowledgebase.Published, Year(DateCreated)
HAVING (((knowledgebase.Published)=True)
AND ((Year(DateCreated))=Year(Now())))
This query selects from the table "knowledgebase", grouped into each year,
then finds the earliest date in the group that matches the current year, and
further refines the selection using another criteria - where Published is
"true". Basically what you are doing, but "published" is your
"date-sighted".
I pulled this out of Access, but MySQL should provide the same SELECT
syntax. You will need to translate the date functions appropriately for
your situation. What I show is valid only for VBscript.
It sounds like you have a deficient database design, where you should be
storing the year and earliest-sighting-date, so the year can be indexed.
Possibly you should even have a table of "earliest sightings". This would
remove the constant need to scan for the earliest date - you can directly
access the information without any grouping/sorting/table-scanning overhead.
DonB
"PJ" <apis@globalnet.co.uk> wrote in message
news:bduldd$vgh$1@news.codecharge.com...
> I have a DB with table 1 of records. Table two holds multiple incidents
of
> T1 based on when records in T1 were seen i.e. the date. The records in T2
> cover several years and I want to pull out the earliest sighting in the
> calendar year. For example, 21st May would be the earliest until next
year
> it was seen on 20th April which then becomes the earliest sighting. Liken
> it to bird-watchers looking out for the earliest sighting of a bird which
> sets the record until superseded.
>
>
> "Robert Sorrells" <rsorrellsU@KNOWmindspring.com> wrote in message
>news:bdujq3$taq$1@news.codecharge.com...
> > If you were looking for the earliest date in a group of records, In
> MS-SQL,
> > it would be something like
> >
> > SELECT TOP 1 datefield FROM yourdatabase
> > ORDER BY datefield
> > WHERE your_filter_criteria
> >
> > MySQL can do it too, and I've used it. I don't remember the command.
> Its
> > not the "TOP 1", but equivalent.
> >
> > I don't understand the "... for one record covering several years." part
> of
> > the problem.
> >
> > Bob
> >
> >
> > "PJ" <apis@globalnet.co.uk> wrote in message
> >news:bdsunk$rvo$1@news.codecharge.com...
> > Can someone assist please.
> >
> > What is the correct argument in MySQL that will calculate the earliest
> date
> > in a year from a range of dates for one record covering several years?
> >
> >
> >
>
>
|
|
|
|
|
