How do I combine 3 date columns from 3 tables in Foxpro sql?

Question

I am writing a foxpro sql & need to combine three date columns from three different tables into a new date column. As an example if the three tables are A,B,& C and the date Columns are Adate, Bdate and Cdate, how do I combine them as distinct and separate dates into a column called TransDate and in the SQL? I would appreciate samples of the code if possible, as I am very new at this

Answer 1

Yeah, more date clarification is needed. You can't combine 3 into 1... or did you mean you want 1 result row that shows all 3 dates associated with a given transaction? Or, you want (for example) the EARLIEST of 3 dates to identify when the first transaction date started? In any event, please clarify in your question, but here is a possible options for you without knowing more of your table's structures... Also, if you are doing it directly from foxpro (VFP) tables, you'll have to obviously add the ";" as line continuation to the following statements

select 
      T1.ADate,
      T2.BDate,
      T3.CDate,
      iif( T1.ADate > T2.BDate and T1.ADate > T3.CDate, T1.ADate,
           iif( T2.BDate > T1.ADate and T2.BDate > T3.CDate, 
               T2.BDate, T3.CDate ) ) as LargestDate,
      iif( T1.ADate < T2.BDate and T1.ADate < T3.CDate, T1.ADate,
           iif( T2.BDate < T1.ADate and T2.BDate < T3.CDate, 
               T2.BDate, T3.CDate ) ) as EarliestDate
   from 
      YourTableA T1,
      YourTableB T2,
      YourTableC T3
   where 
          T1.IDKey = T2.IDKey
      AND T1.IDKey = T3.IDKey
   into 
      cursor C_SomeTestResultCursor

Again, I don't know what your "Key" is that would be common between your three tables, so you'll obviously have to adjust the query on whatever that element(s) is(are).

Answer 2

New answer option... from what your comment was about having a purchase, or stock adjustment or sale, and without any clear description or sample of the output you would LIKE to see, I am thinking you want something like...

Table A = Purchases Table B = Adjustments Table C = Sales

Not sure I would agree with this construct, but can only imply from brief descriptions. I think you want more of a "ledger" style summary

Purchase Jan 10 Purchase Jan 15 Sale Feb 2 Adjustment Feb 10 Purchase Feb 15 etc...

Then, I would do a UNION

select  
      "Purchase   " as TransType,
      T1.ADate as TransDate
   from
      YourTableA T1
   where 
      T1.IDKey = ?SomeValueSuchAsAccount
   order by
      TransDate
UNION ALL
select
      "Adjustments" as TransType,
      T2.BDate as TransDate
   from
       YourTableB T2
   where 
      T2.IDKey = ?SomeValueSuchAsAccount
UNION ALL
select
      "Sales      " as TransType,
      T3.CDate as TransDate
   from
       YourTableB T3
   where 
      T3.IDKey = ?SomeValueSuchAsAccount

If I'm incorrect on my assumptions, please provide myself and others a little more samples of such transactions, data layout, etc... Live data doesn't need be required, nor do full table structures or actual table names. However, we DO need more to work with, especially if you want assistance in the future, otherwise, people may just blow by your requests.