I am using foxpro but its a SQL question anyway.
Ive googled my a$$ off on the topic but cant seem to find an answer.
I have a table with a few date columns for payment dates. I need to get
the highest value out of this table and display it as a single column in
my query. On top of that, each date row determines who paid the bill.
An attorney a doctor etc. and mark the returned data as such ex
"10/24/05 ATT".
Anyone been down this road before.
Hi AJ,
give me a call to my office- i think i know how to pull this off- (giving that i
understand what the hell you're talking about..... :) )
the number is listed below....
Eyal Sela
Clinical Programmer Analyst
Bayer Pharmaceuticals Corporation
Phone: 203 812-3872
Fax: 203 812-2953
eyal.sela.b@bayer.com
aj
<aj@medicaldirect To: profox <profox@leafe.com>
or.com> cc:
Sent by: Subject: [NF] SQL question
profox-bounces@le
afe.com
05/24/2004 01:49
PM
Please respond to
ProFox Email List
I am using foxpro but its a SQL question anyway.
Ive googled my a$$ off on the topic but cant seem to find an answer.
I have a table with a few date columns for payment dates. I need to get
the highest value out of this table and display it as a single column in
my query. On top of that, each date row determines who paid the bill.
An attorney a doctor etc. and mark the returned data as such ex
"10/24/05 ATT".
Anyone been down this road before.
[excessive quoting removed by server]
AJ,
is it possible to Normalize this data. ( I am being to think that my first
answer to any SQL problem is to just say have you normalized the data, even
if there is no connection. <w> )
e.g. I see that there are repeating columns if you could pull that out and
create a subtable called something like Bill_Payment_Dates:
BPD_ID
Who_Paid ( Maybe a FK to a 'Who' table ? )
When_Paid
Now the SQL statement could use an order by date DESC to find the last date.
In your case a MAX( Field1, Field2.. ) may be helpful, but a second pass
would have to be done ( I think ) to find the column name.
Anthony ( Not a SQL expert <s> )
> -----Original Message-----
> From: aj [mailto:aj@medicaldirector.com]
> Sent: Monday, May 24, 2004 9:50 AM
> To: profoxtech@leafe.com
> Subject: [NF] SQL question
>
>
> I am using foxpro but its a SQL question anyway.
>
> Ive googled my a$$ off on the topic but cant seem to find an answer.
> I have a table with a few date columns for payment dates. I
> need to get
> the highest value out of this table and display it as a
> single column in
> my query. On top of that, each date row determines who paid
> the bill.
> An attorney a doctor etc. and mark the returned data as such ex
> "10/24/05 ATT".
> Anyone been down this road before.
>
>
>
>
[excessive quoting removed by server]
Opps forgot the all important FK back to the Bill Table:
BPD_PK
Bill_FK
Who_Paid ( Maybe a FK to a 'Who' table ? )
When_Paid
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
AJ:
I may have missed read your posting you said:
"On top of that, each date ROW determines who paid the bill.
An attorney a doctor etc. and mark the returned data as such ex
"10/24/05 ATT"."
I was thinking that it was date COLUMN. So my comments may not apply,
sorry.
Anthony
> -----Original Message-----
> From: aj [mailto:aj@medicaldirector.com]
> Sent: Monday, May 24, 2004 9:50 AM
> To: profoxtech@leafe.com
> Subject: [NF] SQL question
>
>
> I am using foxpro but its a SQL question anyway.
>
> Ive googled my a$$ off on the topic but cant seem to find an answer.
> I have a table with a few date columns for payment dates. I
> need to get
> the highest value out of this table and display it as a
> single column in
> my query. On top of that, each date row determines who paid
> the bill.
> An attorney a doctor etc. and mark the returned data as such ex
> "10/24/05 ATT".
> Anyone been down this road before.
>
>
>
>
[excessive quoting removed by server]
It is a date column. My query must return the data in that format.
Anthony Testi wrote:
>AJ:
>I may have missed read your posting you said:
>"On top of that, each date ROW determines who paid the bill.
>An attorney a doctor etc. and mark the returned data as such ex
>"10/24/05 ATT"."
>
>I was thinking that it was date COLUMN. So my comments may not apply,
>sorry.
>Anthony
>
>
>
>
>>-----Original Message-----
>>From: aj [mailto:aj@medicaldirector.com]
>>Sent: Monday, May 24, 2004 9:50 AM
>>To: profoxtech@leafe.com
>>Subject: [NF] SQL question
>>
>>
>>I am using foxpro but its a SQL question anyway.
>>
>>Ive googled my a$$ off on the topic but cant seem to find an answer.
>>I have a table with a few date columns for payment dates. I
>>need to get
>>the highest value out of this table and display it as a
>>single column in
>>my query. On top of that, each date row determines who paid
>>the bill.
>>An attorney a doctor etc. and mark the returned data as such ex
>>"10/24/05 ATT".
>>Anyone been down this road before.
>>
>>
>>
>>
>>
>>
[excessive quoting removed by server]
Were then my suggestions of any help?
> -----Original Message-----
> From: aj [mailto:aj@medicaldirector.com]
>
>
> It is a date column. My query must return the data in that format.
>
> Anthony Testi wrote:
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
At this point I cant change the structure of the table.
The max function in fox 8 will not let me put more that one column as a
parameter.
Unless im doing something very wrong that is.
Now I could just do it in fox code but there is no fun in that ?
;)
Thanks for the help.
Anthony Testi wrote:
>Were then my suggestions of any help?
>
>
>
>>-----Original Message-----
>>From: aj [mailto:aj@medicaldirector.com]
>>
>>
>
>
>
>>It is a date column. My query must return the data in that format.
>>
>>Anthony Testi wrote:
>>
>>
>
>
>--- StripMime Report -- processed MIME parts ---
>multipart/alternative
> text/plain (text body -- kept)
> text/html
>---
>
[excessive quoting removed by server]
Eyal I am unable to call at this time.
Just remember one thing though. I dont want to use foxcode to do this.
Just SQL.
There lies the trick my friend ;)
Eyal Sela wrote:
>
>
>Hi AJ,
>give me a call to my office- i think i know how to pull this off- (giving that i
>understand what the hell you're talking about..... :) )
>the number is listed below....
>
>Eyal Sela
>Clinical Programmer Analyst
>
>Bayer Pharmaceuticals Corporation
>Phone: 203 812-3872
>Fax: 203 812-2953
>eyal.sela.b@bayer.com
>
>
>
> aj
> <aj@medicaldirect To: profox <profox@leafe.com>
> or.com> cc:
> Sent by: Subject: [NF] SQL question
> profox-bounces@le
> afe.com
>
>
> 05/24/2004 01:49
> PM
> Please respond to
> ProFox Email List
>
>
>
>
>
>
>I am using foxpro but its a SQL question anyway.
>
>Ive googled my a$$ off on the topic but cant seem to find an answer.
>I have a table with a few date columns for payment dates. I need to get
>the highest value out of this table and display it as a single column in
>my query. On top of that, each date row determines who paid the bill.
>An attorney a doctor etc. and mark the returned data as such ex
>"10/24/05 ATT".
>Anyone been down this road before.
>
>
>
>
[excessive quoting removed by server]
Aj-
>I am using foxpro but its a SQL question anyway.
>
>Ive googled my a$$ off on the topic but cant seem to find an answer.
>I have a table with a few date columns for payment dates. I need to get
>the highest value out of this table and display it as a single column in
>my query. On top of that, each date row determines who paid the bill.
>An attorney a doctor etc. and mark the returned data as such ex
>"10/24/05 ATT".
>Anyone been down this road before.
Of course. But it's frustrating to try and guess what you need given
what you've said about the conditions so far. Anders Atberg, God of
SQL in the Fox world, always suggests people post an example of their
data and what they'd expect as a result set given a correct query.
Then we can help you fashion the query.
Show us in the style of the following what your structure is like
(leave off unrelated facts, please).
CREATE TABLE Payments (iID I, cPayee C(10), yPayment Y, dPaid D)
INSERT INTO Payees VALUES (1,'Terry',10.00, {^2004/05/24})
etc, etc.
Then, show us a result of what you want to see. Like
Payee Payment Date paid
----- ------- ---------
Terry 10.00 05/24/2004
And so on.
Regards-
Nancy Folsom
Pixel Dust Industries
Author _Debugging_Visual_FoxPro_Applications_