mkswanson (2/21/2013)
I tried this, but I'm having the same problem I seem to have had with the other approaches I've taken. In this case, I'm getting a number of NULL values for the information that is coming from the employee table (I believe for everything that doesn't have a matching entry in the tipTransactionPivot table).asiaindian (2/21/2013)
Hello,Please try below query...may be you have to tweak it to suit your requirement....hope this helps
SELECTdbo.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.tipTransactionPivot
FULL OUTER JOIN dbo.salesExport
ON dbo.tipTransactionPivot.employeeID = dbo.salesExport.microsEmployeeNumber
AND dbo.tipTransactionPivot.businessDate = dbo.salesExport.businessDate
AND dbo.tipTransactionPivot.rvcID = dbo.salesExport.rvcID
LEFT JOIN dbo.employee
ON dbo.employee.microsEmployeeNumber = dbo.tipTransactionPivot.employeeID
You need to join to Employee twice - once for each main tablein the query. Since either table may have null values in the row, you must 'LEFT OUTER JOIN' to Employee. this will result in null values in Employee - so you must make sure you refer to the values of the correct instance of Employee.
For instance, if dbo.salesExport is non-null & it is joined to EMP1 - EMP1 values will be non-null. If in the same row, dbo.tipTransactionPivot has null values (ie no matching records), then EMP2 will have null values.