I feel like I've been staring at this statement for hours and just keep mangling it more. Any help would be appreciated.
I want to select all records from tipTransactionPivot and salesExport. If there are the same values for rvcID, employeeID, and businessDate, then I want to join them. Otherwise, I'd like to return them with null values for the other table.
I also want to get certain values from the employee table joined on either the microsEmployeeNumber (in the case of salesExport) or employeeID (in the case of tipTransactionPivot).
Like I said, I probably have this pretty mangled at this point, so I'm sure there is a better way to do it.
P.S. I know it would be better to do some of this in an application rather than in SQL, but it isn't really an option, so I need to return SQL results from a single query if at all possible.
SELECT dbo.salesExport.businessDate, dbo.salesExport.rvcID, dbo.employee.employeeID, dbo.employee.employeeFName, dbo.employee.employeeLName,
dbo.salesExport.chargedReceipts, dbo.salesExport.grossReceipts - dbo.salesExport.chargedReceipts AS otherReceipts,
dbo.salesExport.grossReceipts, dbo.salesExport.discountTotal, dbo.salesExport.bqtSvc19, dbo.salesExport.bqtSvc20, CONVERT(numeric(18, 2),
dbo.salesExport.bqtSvc19 / 0.19 * - 1) AS bqt19Sales, CONVERT(numeric(18, 2), dbo.salesExport.bqtSvc20 / 0.20 * - 1) AS bqt20Sales,
CONVERT(numeric(18, 2), ((dbo.salesExport.grossReceipts + dbo.salesExport.discountTotal) + dbo.salesExport.bqtSvc19 / 0.19 * - 1)
+ dbo.salesExport.bqtSvc20 / 0.20 * - 1) AS netSales, dbo.salesExport.chargedTips, dbo.employee.microsEmployeeNumber,
ISNULL(dbo.tipTransactionPivot.cashTips, 0) AS cashTips, ISNULL(dbo.tipTransactionPivot.cashTipsOut, 0) AS cashTipsOut,
ISNULL(dbo.tipTransactionPivot.cashTipsIn, 0) AS cashTipsIn, ISNULL(dbo.tipTransactionPivot.chargeTipsOut, 0) AS chargeTipsOut,
ISNULL(dbo.tipTransactionPivot.chargeTipsIn, 0) AS chargeTipsIn
FROM dbo.employee AS employee_1 INNER JOIN
dbo.tipTransactionPivot ON employee_1.employeeID = dbo.tipTransactionPivot.employeeID FULL OUTER JOIN
dbo.employee RIGHT OUTER JOIN
dbo.salesExport ON dbo.employee.microsEmployeeNumber = dbo.salesExport.microsEmployeeNumber ON
employee_1.microsEmployeeNumber = dbo.salesExport.microsEmployeeNumber AND
dbo.tipTransactionPivot.businessDate = dbo.salesExport.businessDate AND dbo.tipTransactionPivot.rvcID = dbo.salesExport.rvcID
I Think This Join Statement is Making Me Crazy(er)
Posted - 02/20/2013 : 21:31:48
