Ken,
Sorry, I hadn't realised you were responding to Ted's code.
Frank.
Frank Cazabon
On 22/06/2018 01:11 PM, Frank Cazabon wrote:
Did 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:
SELECT Consumer.*, Status.*, Hours.AppHours
FROM Consumer
JOIN Status on Consumer.ConPK=Status.ConFK
LEFT OUTER JOIN Hours on Consumer.ConPK=Hours.ConFK
WHERE Status.Date =
(SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE
Stat2.ConFK = Consumer.ConPK GROUP BY Stat2.ConFK)
AND Status.Type = "Approved"
AND Hours.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:
SELECT Consumer.*, Status.*, Hours.AppHours
FROM Consumer
JOIN Status on Consumer.ConPK=Status.ConFK
LEFT OUTER JOIN Hours on Consumer.ConPK=Hours.ConFK
WHERE Status.Date =
(SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE
Stat2.ConFK = Consumer.ConPK AND Status.Type = "Approved" GROUP BY
Stat2.ConFK)
AND Hours.Date = (SELECT MAX(Hours2.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, and neither 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.
Thanks.
Ken
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
