Re: Query Involving Sums and Two Most Recent Dates

22 Jun 2018


      Frank,
You're responding here to my response to Ted's code, not yours.
Your code runs fine, and I responded to that separately.
Ken
...
Content-Transfer-Encoding: base64Did you run it 
as part of the first set of code that I gave you?
It runs here with no errors in VFP9 with the latest hot fix and SPs. ;)
What error is it giving you? Maybe a line split 
somewhere due to the email formatting?
This is the full code:
CREATE CURSOR Consumers;
(ConPK int)
INSERT INTO consumers (ConPK ) VALUES (1)
INSERT INTO consumers (ConPK ) VALUES (2)
INSERT INTO consumers (ConPK ) VALUES  (3)
*!*    1
*!*    2
*!*    3
*!*    Status
CREATE CURSOR Status;
(StatPK int,;
ConFK Int,;
Date date,;
Type c(10))
INSERT INTO Status (StatPK , ConFK, Date, Type) 
VALUES (1,1,{^2018/1/15},"Pending")
INSERT INTO Status (StatPK , ConFK, Date, Type) 
VALUES (2,1,{^2018/4/15},"Approved")
INSERT INTO Status (StatPK , ConFK, Date, Type) 
VALUES (3,2,{^2018/6/1},"Closed")
INSERT INTO Status (StatPK , ConFK, Date, Type) 
VALUES (4,2,{^2018/4/15},"Approved")
INSERT INTO Status (StatPK , ConFK, Date, Type) 
VALUES (5,3,{^2017/11/22},"Pending")
INSERT INTO Status (StatPK , ConFK, Date, Type) 
VALUES (6,3,{^2018/2/3},"Approved")
INSERT INTO Status (StatPK , ConFK, Date, Type) 
VALUES (7,3,{^2018/3/12},"Suspended")
INSERT INTO Status (StatPK , ConFK, Date, Type) 
VALUES (8,3,{^2018/5/11},"Approved")
CREATE CURSOR Hours;
(HourPK int,;
conFK int,;
Date d,;
Apphours int)
*!*    PK,ConFK,,Date,,AppHours
INSERT INTO Hours (HourPK , ConFK, Date, AppHours) VALUES (1,2,{^2017/7/3},10)
INSERT INTO Hours (HourPK , ConFK, Date, 
AppHours) VALUES (2,2,{^2018/3/12},14)
INSERT INTO Hours (HourPK , ConFK, Date, AppHours) VALUES (3,3,{^2018/4/1},20)
INSERT INTO Hours (HourPK , ConFK, Date, 
AppHours) VALUES (4,3,{^2018/5/1},12 )
SELECT Status.ConFK, LastHours.AppHours;
    FROM Status ;
    INNER JOIN (SELECT ConFK, MAX(Date) AS MaxDate;
        FROM Status;
        GROUP BY ConFK) M ON Status.ConFK = 
M.ConFK AND Status.Date = M.MaxDate ;
    AND Type = "Approved";
LEFT JOIN (SELECT Hours.ConFK, Hours.AppHours;
            FROM Hours;
            INNER JOIN (SELECT ConFK, MAX(date) AS MaxHoursDate;
                            FROM Hours;
                            GROUP BY ConFK) MH ;
                        ON Hours.ConFK = 
MH.ConFK AND Hours.Date = MH.MaxHoursDate ) LastHours ;
Â            ON STatus.ConFK = LastHours.ConFK
Frank.
Frank Cazabon
On 22/06/2018 12:21 PM, Ken Dibble wrote:
ÑSPÕÛÛÝ[Y\Ý]\ËÝ\Ë\Ý\ FROM Consumer
...
...
JOIN Status on Consumer.ConPK=Status.ConFK
QÕUTÒSÝ\ÈÛÛÛÝ[Y\ÛÛÏRÝ\ËÛÛK
ÒTHÝ]\Ë]HB4TÄT5BÔ7FC"äFFRe$ôÒStatus Stat2 WHERE
...
Ý]ÛÛÈHÛÛÝ[Y\ÛÛÈÔÕTBY Stat2.ConFK)
SÝ]\Ë\HH\ÝYSÀurs.Date = (SELECT 
MAX(Hours2.Date) FROM Hours Hours2 WHERE
...
Hours2.ConFK = Consumer.ConPK GROUP BY Hours.PK)
I actually can't get this to run without 
errors. Interpolating, I **think** you meant:
4TÄT5B6öç7VÖW"â , Status.*, Hours.AppHours
...
...
FROM Consumer
ÒSÝ]@s on Consumer.ConPK=Status.ConFK
...
...
LEFT OUTER JOIN Hours on Consumer.ConPK=Hours.ConFK
ÒTHÝ]\Ë]HB(SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE
...
Stat2.ConFK = Consumer.ConPK AND Status.Type = "Approved" GROUP BY
Ý]ÛÛÊBSÝ\Ë]HH
ÑSPÕPV
Ý\s2.Date) FROM Hours Hours2 WHERE
...
...
Hours2.ConFK = Consumer.ConPK GROUP BY Hours.PK)
However, I get "Function missing )" on the last subquery on this this,
[Z]her intellisense nor I can find any unmatched parens.
Also, the first subquery is grouping on the Status table FK, while the
...
second subquery groups on the Hours table PK. I 
think they should both group on the same key, 
but I don't understand which one that should be.
@nks.
Ù[Ù^Ù\ÜÚ]H][Ý[È[[ÝYH[excessive quoting removed by server]

2026

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

Re: Query Involving Sums and Two Most Recent Dates