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
Code:
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;