× 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.


  1.  Home
  2. MIDRANGE-L
  3. August 2006

SQL problem...

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

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
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.