SQL problem... -- MIDRANGE-L

Hi all,

I've used too many hours now to try to join, union, sub-selects etc. andI'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 wherePersonDate 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, andalso shown a dotted line of which dates is before or on the specifieddate 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 theabove result (thus an extended version of the above SQL).


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


Best regards,
John

This mailing list archive is Copyright 1997-2026 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.