How to reference data another lrow

[Thompyt]

Lodging: Switch(([Sleg]=1 And [Sleg]=[Eleg]),[TDYDay]*[PD_Lodging],[Sleg]>=1 And [Eleg]>=2,([TDYDay]-1)*([PD_Lodging])+(DLocal [PD_Lodging]),([Sleg]>2 And [Eleg]>2) And [Sleg]=[Eleg],([TDYDay]-1)*[PD_Lodging])

Sleg = Start Leg
ELeg = End of leg in trip id multiple destination, or final leg in 1 leg trip
PD_Lodging = Per Diem Loging costs per GTA
TDYDay = the length of the trip gotten from the (stop day of trip - the start day) +1
Olocal = Origin location
Dlocal = Destination location

Single leg trip is easy

On a multiple leg trip, the last day of the previous leg changes the PD_Lodging cost to where you put your head down that night or Dlocal

So if there is more than 1 leg then the last day of that leg will be the Dlocal PD_Lodging.



Leg 1 of 2 example
(TDYDay-1)*Olocal PD_Lodging + Dlocal PD_Lodging

TDYDay = 6
Olocal PD_Lodging for 5 days + 1 day of DLocal PD_Lodging

Quick query

SELECT TBL_Consolidate.CON_ID, TBL_Consolidate.OLocale, TBL_Consolidate.Dlocale, TBL_Consolidate.SLeg, TBL_Consolidate.ELeg, [TDYStop]-[TDYStart]+1 AS TDYDay, TBL_Location.PD_Lodging, Switch(([Sleg]=1 And [Sleg]=[Eleg]),[TDYDay]*[PD_Lodging],[Sleg]>=1 And [Eleg]>=2,([TDYDay]-1)*([PD_Lodging]),([Sleg]>2 And [Eleg]>2) And [Sleg]=[Eleg],([TDYDay]-1)*[PD_Lodging]) AS LodgingFROM TBL_Location INNER JOIN TBL_Consolidate ON TBL_Location.LOC_ID = TBL_Consolidate.LOC_ID;

[June7]

Review http://allenbrowne.com/subquery-01.html#AnotherRecord

If data is in other db platform like SQLServer, there are intrinsic functions to accommodate this requirement.

[CJ_London]

Provide some example data and the outcome required from that example data