Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

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

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  
 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.02 seconds. Powered By: Snitz Forums 2000