SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 I Think This Join Statement is Making Me Crazy(er)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mkswanson
Starting Member

21 Posts

Posted - 02/20/2013 :  21:31:48  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000