Transaction Data Consolidate

[thepastor]

Table with three fields
id transdate transamt

Multiple transaction records with same ID and contribution date
and countribution amt

Need to place Unique ID's in one record and supply the following

unique id's,
oldest date,
newest date,
oldest amt,
newest amt,
transaction count

Transaction count contains the count of transactions for each ID

I would like to use one sql select if possible.

 

[Mike Lewis]

Not quite sure what you are asking.

Do you want to end up with a single record, containing the oldest date, oldest amount, etc. and the total number of transactions? If so, what would you expect to see in the ID column?

Or, do you want one record per transaction? If so, how can you have the oldest date, etc., per transaction, given that you said that all records for a transaction have the same date and amount?

Perhaps you could clarify what you are trying to achieve.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

I would like to use one sql select if possible.

Why? Is there a requirement you're not telling?

You can get the lowest/highest date with something like:

Select ;
id, ;
count(id) as HowMany, ;
Min(trandate), ;
Max(trandate) ;
Group by id

The problem comes from the amount fields. There is no way "in a single sql select" to get the amounts right. You could get the tranamt values you want with corelated subqueries for those columns, though.

(This is where I wish Dr. Granor would pop in more often!)

Is this what you're after?

 

[Olaf Doschke]

Even with a subquery the outset of "Multiple transaction records with same ID and contribution date and countribution amt" make it harder to determine the real oldest or newest amount.

If data comes in chronological, you can make use of the recno(), but that's a nogo in a complex sql with subqueries. It would perhaps work within the subquery/-queries, as they can work on a single table. I'd rather put this seperate, though, eg

Select table
Set Order To ID && needs an index on the ID field
Set Key To x
Locate
oldestamt = table.transamt
Go Bottom
newestamt = table.transamt

It would help, if there was a secondary unique ID for each record. And indeed every table should have one such primary key anyway.

Bye, Olaf.

 

[thepastor]

Dan Freeman is close but not there. Here is a small example of data I'm talking about.

ID Tdate Tamt
12345 19990101 100.00
12345 20050101 50.00
12345 20070101 125.00
12345 20091010 10.00
11222 20010101 200.00
11222 20020101 125.00

Result Needed:
ID Oldest Tamt Newest Tamt
12345 100.00 10.00
12222 200.00 125.00

Does that help?

 

[Mike Lewis]

This is totally off the top of my head:

SELECT mt.ID, mt.tAmt, mx.tAmt ;
  FROM MyTable mt ;
  JOIN (SELECT ID, MIN(tDate) AS MinDate FROM MyTable GROUP BY ID) mint ;
  ON mt.ID = mint.ID AND mt.tDate = mint.MinDate ;
  JOIN (SELECT ID, MAX(tDate) AS MaxDate FROM MyTable GROUP BY ID) mx ;
  ON mt.ID = mx.ID AND mt.tDate = mx.MaxDate

It might not be completely correct, but it should give you the general idea.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[thepastor]

This is the direction I was thinking Mike, I assume that mt, mint and mx are reitterations of the same table?

 

[Mike Lewis]

To be exact, mt, mint and mx are local aliases. "mt" refers to MyTable, "mint" to the first subquery and "mx" to the second.

They are strictly optional. You can leave them out, and use the full table names instead.

I suggest you give the command a try and see what it gives you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[thepastor]

Nope, it can't find mt.tamt or mx.tamt and just for kicks I changed that to mt.mindate and mx.maxdate just to see what happens and it returned a very small subset of what it should and the dates min and max for each record were equal.

It needs to return one occurance of each ID and the oldest amt and the newest amt.

 

[Mike Lewis]

Well, I never said that the command was definitely going to solve the entire problem. I offered it only as a way of starting you in the right direction. Ultimately, it's up to you to get it working.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mikes SQL can only work in cases mindate = maxdate, as both joins are inner joins and only will result in records in the case all IDs match.

This works with your sample data:

Create Cursor curTransactions (id I, tdate C(8), tamt B)

Insert into curTransactions values (12345,'19990101',100.00)
Insert into curTransactions values (12345,'20050101', 50.00)
Insert into curTransactions values (12345,'20070101',125.00)
Insert into curTransactions values (12345,'20091010', 10.00)
Insert into curTransactions values (11222,'20010101',200.00)
Insert into curTransactions values (11222,'20020101',125.00)

SELECT mt1.ID, mt1.tAmt as oldestamt, t3.newestamt;
  FROM curTransactions  mt1 ;
  Inner JOIN (SELECT ID, MIN(tDate) AS MinDate FROM curTransactions GROUP BY ID) t1 ;
  ON mt1.ID = t1.ID AND mt1.tDate = t1.MinDate ;
Left Join ;
(SELECT mt2.id, mt2.tAmt as newestamt;
  FROM curTransactions  mt2 ;
  Inner JOIN (SELECT ID, MAX(tDate) AS MaxDate FROM curTransactions GROUP BY ID) t2 ;
  ON mt2.ID = t2.ID AND mt2.tDate = t2.MaxDate) t3;
  On mt1.id = t3.id

Bye, Olaf.

 

[thepastor]

Perfect Olaf. Thank you so much. I had to do tweak or two to take care of same date issues but this saved a tremendous about amount of time and frustration.

Thanks to all you folks who helped it's all useful.