self join table in query

[illuzioner]

Hi,

what is the SQL syntax to do a self-join of a table to itself where the left table (tblLeft) pairs with the table on the right (tblRight) when

tblRight.Date = Max(tblRight.Date)
where tblRight.date < tblLeft.Date

tblLeft and tblRight are the same table. I'm guessing this is a Left Join instead of an Inner Join because I'm not matching fields from both tables.

Any help is greatly appreciated!

Lou

 

[swampBoogie]

I don't understand what you are trying to do.

Could you instead, describe the table, provide some sample data and the expected result.

 

[illuzioner]

Okay, here is what the table (tblCurrent) looks like:

PID IDate CurVal
1 3/15/2002 5
1 3/20/2002 11
1 3/20/2002 7
2 3/25/2002 4
1 3/25/2002 8
2 3/30/2002 9
1 4/15/2002 12
2 4/15/2002 6

grouping by PID, I am trying to calculate the difference in CurVal between a record and the previous record to look like this:

PID IDate CurVal CurValDiff
1 3/15/2002 5 0
1 3/20/2002 11 6
1 3/20/2002 7 -4
1 3/25/2002 8 1
1 4/15/2002 12 4

and likewise for PID=2. so, the row IDate=3/20/2002 is paired up with IDate=3/15/2002, and each row is paired with its previous row, hence I tried the max function where the max is < the IDate of the left table.

There are more fields that I want to do this with and so I would like more than just the CurVal value, but several values from that row.

Let me know if you need more info.

Thanks!

Lou

 

[lameid]

Select t1.PID, t1.Idate as MasterDate, t1.CurVal as MasterVal, Max(t2.Idate) as SlaveDate
From tblCurrent as T1 Inner Join tblCurrent as t2 On
T1.PID = T2.PID
Where t1.IDATE > t2.IDATE
Group By T1.PID, MasterDate, MasterVal

Name this query &quot;QryCurrentDiffVal&quot;

Second query:

Select QryCurrentDiffVal.PID, QryCurrentDiffVal.IDate, QryCurrentDiffVal.MasterVal as Curval, QryCurrentDiffVal.MasterVal - tblCurrent.Curval
From QryCurrentDiffVal Inner Join tblCurrent On
QryCurrentDiffVal.PID = tblCurrent.PID And
QryCurrentDiffVal.Slavedate = tblCurrent.Idate


That's the jist of what you need to do... When I skimmed your question the first time, I missed the fact that you had some Idate's on the same day so this is not an exact solution. You will need to improve it by adding an autonumber field to tblcurrent and thinking a bit.

Sorry I didn't see the whole question to start with.

 

[illuzioner]

Thank you for this answer. I will play around with it to see if I can make it work for my exact table. This helps a lot.

Best,

Lou