Joining Same DB Table but want to locate back one record only

[RebelDevil]

Right folks.

First time poster here.

I have a table called Trans that looks like



Batch, Seq, Client, Date, Transaction
---------------------------------------------------------
B100, 1, C1, 10/09/2007, T1
B100, 2, C2, 10/09/2007, T2
...
B78, 5, C1, 05/05/2007, T5
..
B69, 3, C2, 04/04/2007, T8
..
B53, 3, C1, 03/03/2007, T10


The script I need will have to go through all recs in B100 and find the last transaction (by date) for each client within the same table. I basically want to end up with

Client, Date
----------------------
C1, 05/05/2007
C2, 04/04/2007

Hopefully somebody can give me a script to do it.

Thanks

RebelDevil

[amitpatel66]

Right folks.

First time poster here.

I have a table called Trans that looks like



Batch, Seq, Client, Date, Transaction
---------------------------------------------------------
B100, 1, C1, 10/09/2007, T1
B100, 2, C2, 10/09/2007, T2
...
B78, 5, C1, 05/05/2007, T5
..
B69, 3, C2, 04/04/2007, T8
..
B53, 3, C1, 03/03/2007, T10


The script I need will have to go through all recs in B100 and find the last transaction (by date) for each client within the same table. I basically want to end up with

Client, Date
----------------------
C1, 05/05/2007
C2, 04/04/2007

Hopefully somebody can give me a script to do it.

Thanks

RebelDevil

Your sample data is not that clear and is not with in sync of your requirement:

You want the latest transaction for each client under each batch.

Sample data

Batch client date trans
B100 C1 10/09/2007 T1
B100 C1 05/05/2007 T2
B100 C2 10/09/2007 T3
B100 C2 05/04/2007 T4

Then your sample output should look like:

Client Date
C1 10/09/2007
C2 10/09/2007
...........

I assume my understanding is correct,
try for below query:

 
SELECT client,date FROM table_name 
WHERE date IN(select max(date) from table_name GROUP BY batch,client)
 
 

[RebelDevil]

Sorry,

Let me try to explain it again. I might have got it wrong above.

The records in the Trans Table are

Batch, Seq, Client, Date, Transaction
---------------------------------------------
B100, 1, C1, 10/09/2007, T1
B100, 2, C2, 10/09/2007, T2
B78, 5, C1, 05/05/2007, T5
B69, 3, C2, 04/04/2007, T8
B69, 4, C1, 04/04/2007, T12
B53, 3, C1, 03/03/2007, T10


The unique index for this table is Batch+Seq

I have a screen then that display all transactions for Batch B100 so it displays

B100, 1, C1, 10/09/2007, T1
B100, 2, C2, 10/09/2007, T2

But I also need to display the previous transaction for each client.

When I say previous I mean by the last transaction by Sample date. So the previous one in the file for C1 is

B78, 5, C1, 05/05/2007, T5

and the previous one in the file for C2 is

B69, 3, C2, 04/04/2007, T8


My final SQL Statement should return
Client, Prev Transaction.
C1, T5
C2, T8

Once I have a SQL statement to display the above then I can work out the rest.

Hope this explains it better.

TIA
RebelDevil

[amitpatel66]

Sorry,

Let me try to explain it again. I might have got it wrong above.

The records in the Trans Table are

Batch, Seq, Client, Date, Transaction
---------------------------------------------
B100, 1, C1, 10/09/2007, T1
B100, 2, C2, 10/09/2007, T2
B78, 5, C1, 05/05/2007, T5
B69, 3, C2, 04/04/2007, T8
B69, 4, C1, 04/04/2007, T12
B53, 3, C1, 03/03/2007, T10


The unique index for this table is Batch+Seq

I have a screen then that display all transactions for Batch B100 so it displays

B100, 1, C1, 10/09/2007, T1
B100, 2, C2, 10/09/2007, T2

But I also need to display the previous transaction for each client.

When I say previous I mean by the last transaction by Sample date. So the previous one in the file for C1 is

B78, 5, C1, 05/05/2007, T5

and the previous one in the file for C2 is

B69, 3, C2, 04/04/2007, T8


My final SQL Statement should return
Client, Prev Transaction.
C1, T5
C2, T8

Once I have a SQL statement to display the above then I can work out the rest.

Hope this explains it better.

TIA
RebelDevil

Try below query:

SELECT e.client,e.transaction FROM
(SELECT client,date,transaction,dense_rank() OVER(PARTITION BY client ORDER BY date DESC) rn FROM table_name) e
WHERE e.rn = 2
 

[RebelDevil]

Try below query:

SELECT e.client,e.transaction FROM
(SELECT client,date,transaction,dense_rank() OVER(PARTITION BY client ORDER BY date DESC) rn FROM table_name) e
WHERE e.rn = 2
 

That wont work because if I open Batch 69 I should be able to get the previous transactions for those client.

B69, 3, C2, 04/04/2007, T8
B69, 4, C1, 04/04/2007, T12

so it would locate T10 for C1 from the below record and nothering for C2.

B53, 3, C1, 03/03/2007, T10

[amitpatel66]

That wont work because if I open Batch 69 I should be able to get the previous transactions for those client.

B69, 3, C2, 04/04/2007, T8
B69, 4, C1, 04/04/2007, T12

so it would locate T10 for C1 from the below record and nothering for C2.

B53, 3, C1, 03/03/2007, T10

I have a small clarification:

Consider following data:

B100 C1 10/09/07 T1
B100 C1 04/04/07 T2
B100 C2 10/09/07 T3
B100 C2 04/04/07 T4

So you want previous transactions for all the clients for batch B100 ie

C1 T2
C2 T4

This you can achieve by the query that I gave you in the previous post.

But now consider following data:

B100 C1 10/09/07 T1
B100 C1 06/04/07 T2
B100 C2 10/09/07 T3
B100 C2 06/04/07 T4
B69, C2, 04/04/2007, T8
B69, C1, 04/04/2007, T12
B53, C1, 03/03/07, T10

So you want the previous transaction of all the clients for a particular batch right?

If you pass B100 then you want as:

C1 T2
C2 T4

and when you pass B69 then you want as:

C1 T10

Is my explanation correct?

[RebelDevil]

I have a small clarification:

Consider following data:

B100 C1 10/09/07 T1
B100 C1 04/04/07 T2
B100 C2 10/09/07 T3
B100 C2 04/04/07 T4

So you want previous transactions for all the clients for batch B100 ie

C1 T2
C2 T4

This you can achieve by the query that I gave you in the previous post.

But now consider following data:

B100 C1 10/09/07 T1
B100 C1 06/04/07 T2
B100 C2 10/09/07 T3
B100 C2 06/04/07 T4
B69, C2, 04/04/2007, T8
B69, C1, 04/04/2007, T12
B53, C1, 03/03/07, T10

So you want the previous transaction of all the clients for a particular batch right?

If you pass B100 then you want as:

C1 T2
C2 T4

and when you pass B69 then you want as:

C1 T10

Is my explanation correct?

Yes, your explanation with your data is correct.

[amitpatel66]

Yes, your explanation with your data is correct.

Check out below Query. It takes one input parameter ie BATCH. Pass any batch value and check if it is returning you the previous transaction for each client of that batch

SELECT g.client,g.hdate FROM
(SELECT DISTINCT tran.batch,e.client,tran.hdate,ROW_NUMBER() OVER(PARTITION BY e.client ORDER BY tran.hdate DESC) rn FROM
(SELECT client,hdate from tran where batch = &1) e, tran
WHERE e.client=tran.client
AND  tran.hdate < e.hdate) g
WHERE g.rn = 1
 

[RebelDevil]

sorry about this but this is exactly what I have. Above I was trying to give an example so I could work with the replies I got.

Current table is

TType BType Ins BNo ISO Id Res SDate
2 1 F 057 010 S1 14 10/10/2007
2 1 F 057 020 S2 1 10/10/2007
2 1 F 057 030 S3 11 10/10/2007
2 1 F 054 010 S1 12 09/10/2007
2 1 F 053 040 S3 4 05/10/2007
2 1 F 050 020 S1 20 01/10/2007
2 1 F 050 070 S2 30 01/10/2007

The unique index in this table is the first 5 columns

The Script I want will go through a batch and return the previous Res for all ids within a Batch (BNo)..

The script I have at the moment is

SELECT cur.ISO, MAX(prv.SDate) 
FROM BatchDets cur
INNER JOIN BatchDets prv 
    ON cur.TType = prv.TType AND cur.BType = prv.BType 
    AND cur.Id = prv.Id AND cur.SDate > prv.SDate 
WHERE cur.TType= '2' and  cur.BType = '1' and  cur.Ins = 'F'
    and  cur.BNo = '057' 
GROUP BY cur.ISO

The above statement returns

cur.ISO MAX(prv.SDate)
010 09/10/2007
020 01/10/2007
030 05/10/2007

what I would like to be returned is

cur.ISO prv.Res
010 12
020 30
030 4

If I try to add the prv.Res to the select statement I get errors.

I hope this explains in full my requirements.

Thanks again
RebelDevil

[amitpatel66]

sorry about this but this is exactly what I have. Above I was trying to give an example so I could work with the replies I got.

Current table is

TType BType Ins BNo ISO Id Res SDate
2 1 F 057 010 S1 14 10/10/2007
2 1 F 057 020 S2 1 10/10/2007
2 1 F 057 030 S3 11 10/10/2007
2 1 F 054 010 S1 12 09/10/2007
2 1 F 053 040 S3 4 05/10/2007
2 1 F 050 020 S1 20 01/10/2007
2 1 F 050 070 S2 30 01/10/2007

The unique index in this table is the first 5 columns

The Script I want will go through a batch and return the previous Res for all ids within a Batch (BNo)..

The script I have at the moment is

SELECT cur.ISO, MAX(prv.SDate) 
FROM BatchDets cur
INNER JOIN BatchDets prv 
    ON cur.TType = prv.TType AND cur.BType = prv.BType 
    AND cur.Id = prv.Id AND cur.SDate > prv.SDate 
WHERE cur.TType= '2' and  cur.BType = '1' and  cur.Ins = 'F'
    and  cur.BNo = '057' 
GROUP BY cur.ISO

The above statement returns

cur.ISO MAX(prv.SDate)
010 09/10/2007
020 01/10/2007
030 05/10/2007

what I would like to be returned is

cur.ISO prv.Res
010 12
020 30
030 4

If I try to add the prv.Res to the select statement I get errors.

I hope this explains in full my requirements.

Thanks again
RebelDevil

Could you please try the query that i provided you:

SELECT g.client,g.hdate FROM
(SELECT DISTINCT tran.batch,e.client,tran.hdate,ROW_NUMBER() OVER(PARTITION BY e.client ORDER BY tran.hdate DESC) rn FROM
(SELECT client,hdate FROM tran WHERE batch = &1) e, tran
WHERE e.client=tran.client
AND  tran.hdate < e.hdate) g
WHERE g.rn = 1
 

I think it would be easier for you to re write the above query in the way you want and check if it is working!!

[RebelDevil]

Could you please try the query that i provided you:

SELECT g.client,g.hdate FROM
(SELECT DISTINCT tran.batch,e.client,tran.hdate,ROW_NUMBER() OVER(PARTITION BY e.client ORDER BY tran.hdate DESC) rn FROM
(SELECT client,hdate FROM tran WHERE batch = &1) e, tran
WHERE e.client=tran.client
AND  tran.hdate < e.hdate) g
WHERE g.rn = 1
 

I think it would be easier for you to re write the above query in the way you want and check if it is working!!

I've tried to change the query you gave me to extract the information I want but couldn't as I need to be able to select from 2 different views of the same table at the end.

In you example it would mean starting with

SELECT e.client,g.hdate FROM

[amitpatel66]

I've tried to change the query you gave me to extract the information I want but couldn't as I need to be able to select from 2 different views of the same table at the end.

In you example it would mean starting with

SELECT e.client,g.hdate FROM

Let me explain my query:

First the batch number is passed to the query and it selects all the clients,tran date for that batch. Then i compare tran date with another object of same table, and i get records for tran date < date of another table alias....

i partition the data and assign the row number and get the record whose row = 1