Re: SQL problem...

[Jerome Hughes <jromeh@xxxxxxx>]

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.