SELECT Seller_FullNameStreetCityState = CASE WHEN NULLIF(L.DbaName, '') IS NOT NULL THEN L.DbaName + ', ' + L.LotAddress + ', ' + L.LotCity + ', ' + L.LotState + ', ' + L.LotZip ELSE D.CompanyName + ', ' + D.CompanyAddress + ', ' + D.CompanyCity + ', ' + D.CompanyState + ', ' + D.CompanyZip END,
	Seller_FullNameStreetCityState1 = CASE WHEN NULLIF(L.DbaName, '') IS NOT NULL THEN L.DbaName ELSE D.CompanyName END,
	Seller_FullNameStreetCityState2 = CASE WHEN NULLIF(L.DbaName, '') IS NOT NULL THEN L.LotAddress + ', ' ELSE D.CompanyAddress + ', ' END,
	Seller_FullNameStreetCityState3 = CASE WHEN NULLIF(L.DbaName, '') IS NOT NULL THEN L.LotCity + ', ' + L.LotState + ', ' + L.LotZip ELSE D.CompanyCity + ', ' + D.CompanyState + ', ' + D.CompanyZip END,
	Form_Date = CONVERT(CHAR(10), GetDate(), 101),
	Vehicle_Year = C.year,
	Vehicle_Make = C.make,
	Vehicle_Model = C.model,
	Vehicle_Other_Text_MULTIPART_1 = C.style,
	Vehicle_IdentificationNumber = C.vin,
	Insurance_Agent_FullNameAddressyDayPhone = NULLIF(A.InsuranceAgent, '') + ', ' + A.InsuranceAddress + ', ' + A.InsurancePhone,
	A.InsurancePolicy AS Insurance_PolicyNumber,
	A.InsuranceColl AS Insurance_Vehicle_Collision_Deductible_Amount,
	A.InsuranceComp AS Insurance_Vehicle_Comprehensive_Deductible_Amount,
	A.InsuranceDateFrom AS Form_EffectiveFrom_Date,
	A.InsuranceDate AS Form_EffectiveTo_Date,
	AllBorrowers_FullNameStreetCityZip = RTRIM(B.Firstname) + ' ' + RTRIM(B.Lastname) + ', ' + B.Address + ', ' + B.City + ', ' + B.Zip,
	AllBorrowers_FullNameStreetCityZip1 = RTRIM(B.Firstname) + ' ' + RTRIM(B.Lastname),
	AllBorrowers_FullNameStreetCityZip2 = B.Address + ', ',
	AllBorrowers_FullNameStreetCityZip3 = B.City + ', ' + B.STATE + ', ' + B.Zip,
	Agreement_FirstSigner_FullName = RTRIM(B.Firstname) + ' ' + RTRIM(B.Lastname),
	Agreement_SecondSigner_FullName = RTRIM(B2.Firstname) + ' ' + RTRIM(B2.Lastname),
	Payee_FullNameStreetCityStateZip = A.financecompany + ', ' + V.VendorAddress + ', ' + V.VendorCity + ', ' + V.VendorState + ', ' + V.VendorZip,
	LienholderInfo_Name = V.VendorName,
	LienholderInfo_Address = V.VendorAddress,
	LienholderInfo_City = V.VendorCity,
	LienholderInfo_State = V.VendorState,
	LienholderInfo_Zip = V.VendorZip
FROM DealsTable A
INNER JOIN customertable B ON B.customercode = A.buyercode
INNER JOIN inventorytable C ON C.StockNumber = A.StockNumber
INNER JOIN LotsTable L ON A.LotName = L.LotName
LEFT JOIN CustomerTable B2 ON B2.CustomerCode = A.CoBuyerCode
LEFT JOIN VendorListTable V ON V.VendorName = A.FinanceCompany
LEFT JOIN DMSData..CompanyTable D ON D.CompanyCode = '<CompanyCode>'
WHERE A.AccountNumber = '<AccountNumber>'