RE: SQL problem...

John,

If you are wanting to do this in a single statement try something like
this:

 With                                                            
   temptable (tempid, tempdate) as                                
    (select personid, max(persondate) from persons        
        where persondate <= '2004-06-01'
        group by personid)                             
                                                                 
Select personid, tempdate, personamount
        from persons join temptable on personid = tempid

I haven't created files to test it with but it was taken from a working
statement.

HTH,

Rick

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of JohnF
Sent: Thursday, August 10, 2006 9:19 AM
To: midrange-l@xxxxxxxxxxxx
Subject: 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

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

Privileged and Confidential.  This e-mail, and any attachments there to, is 
intended only for use by the addressee(s) named herein and may contain 
privileged or confidential information.  If you have received this e-mail in 
error, please notify me immediately by a return e-mail and delete this e-mail.  
You are hereby notified that any dissemination, distribution or copying of this 
e-mail and/or any attachments thereto, is strictly prohibited.