× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



John--

So there can be only one record for a person with a date, correct?

Assuming that is so, try testing something like this...

#1
select a.PersonID, a.PersonDate, a.PersonAmount
from Persons a
where a.PersonID || a.PersonDate
  in (
       select b.PersonID || max(b.PersonDate)
       from Persons b
       where b.PersonDate <= "2006-04-01"
       group by b.PersonID
     )

#2
select sum(a.PersonAmount)
from Persons a
where a.PersonID || a.PersonDate
  in (
       select b.PersonID || max(b.PersonDate)
       from Persons b
       where b.PersonDate <= "2006-04-01"
       group by b.PersonID
     )

thx & hth,

--Jerome


On Aug 10, 2006, at 9:19 AM, JohnF wrote:

Hi all,

I've used too many hours now to try to join, union, sub-selects etc. and
I've given up for the day and hope that one of you can help me out.


MY DATA
=======
Table: "Persons"
+----------+------------+--------------+
| PersonID | PersonDate | PersonAmount |
+----------+------------+--------------+
|    10    | 2006-01-01 |         1.00 |
|    10    | 2006-02-10 |         2.00 |
|    10    | 2006-02-15 |         3.00 |
|    20    | 2006-02-17 |         4.00 |
|    10    | 2006-02-20 |         5.00 |
|    10    | 2006-03-03 |         6.00 |
|    30    | 2006-03-10 |         7.00 |
|    10    | 2006-03-11 |         8.00 |<==
|    30    | 2006-03-12 |         9.00 |
|    30    | 2006-03-13 |        10.00 |<==
|    20    | 2006-03-20 |        11.00 |
|    20    | 2006-03-25 |        12.00 |
|    20    | 2006-03-28 |        13.00 |<==
+ - - - - -+ - - - - - -+ - - - - - - -+
|    20    | 2006-04-04 |        14.00 |
|    20    | 2006-04-08 |        15.00 |
|    20    | 2006-04-10 |        16.00 |
|    10    | 2006-04-14 |        17.00 |
|    10    | 2006-04-18 |        18.00 |
|    10    | 2006-04-27 |        19.00 |
+----------+------------+--------------+


WISH #1
=======
I wan't to retrieve 1 record with all fields for each PersonID where
PersonDate is the newest date before or on '2006-04-01'.
The result should be sorted on PersonID.
Note: I have marked the records I wan't with "<==" in my data table, and
also shown a dotted line of which dates is before or on the specified
date and those that are after.

Result:
+----------+------------+--------------+
| PersonID | PersonDate | PersonAmount |
+----------+------------+--------------+
|    10    | 2006-03-11 |         8.00 |
|    20    | 2006-03-28 |        13.00 |
|    30    | 2006-03-13 |        10.00 |
+----------+------------+--------------+


WISH #2
=======
I wan't one SQL statement that returns the sum of PersonAmount from the
above result (thus an extended version of the above SQL).

Result:
+-------------------+
| SUM(PersonAmount) |
+-------------------+
|             31.00 |
+-------------------+


Best regards,
John

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.