asked
JOIN or Case When possibilities
Hi Everyone! I'm stumped here! I have a query that I would like to use a CASE WHEN with but I cannot find a way to do so. I was also thinking of a potential Stored Procedure but then the logic on that didn't make sense on me. Here is what I am looking at: SELECT TICKETNUMBER, I.RECNAME, I.COMMISSIONPCT, L.QTYSHIPPED, E.RECNAME AS 'SALESREP', C.RECNAME AS 'CUSTOMER' FROM ORDERS AS O JOIN ORDERLINES AS L ON O.TICKETNUMBER = L.ORDERTICKETNUMBER JOIN ITEMS AS I ON L.ITEMNID = I.ITEMNID JOIN DBO.CUSPAYMENTAPPLIES AS CP ON O.TICKETNUMBER = TOORDERTICKETNUMBER LEFT JOIN CUSTOMERS AS C ON C.CUSNID = O.TOCUSNID LEFT JOIN EMPLOYEES AS E ON O.SlsEmpNid = E.EMPNID WHERE TOTALRECEIVABLEREMAINING = 0 AND BATCHENTRYDATE BETWEEN '2011-01-24' AND '2011-01-29' AND I.PRODUCTCLASSNID IN (1, 2) --AND E.EMPNID = 123 AND C.ACTIVEFLAG = 1 AND (O.RECEIVABLESNOTE NOT LIKE 'NC%' OR O.RECEIVABLESNOTE IS NULL) AND O.ISVOIDED = 0 What I want say with the LEFT JOIN EMPLOYEES is IF o.slsempnid c.acctmanagernid AND employees.firstname NOT LIKE '(SR%' then c.acctmanagerid = e.empnid ELSE o.slsempnid = e.empnid. We have some data entry personnel that should not be tied to the commission but instead the acctmanager for that order should be accredited the commision even though they were not the one to enter the order in the system. How can I write this to accomodate a "switch" JOIN based on o.slsempnid c.acctmanagernid? EDIT (added table creation scripts and test call) --- /* CREATE ORDERS */ CREATE TABLE [dbo].[Orders]( [TicketNumber] [int] NOT NULL, [SlsEmpNid] [int] NULL, [ToCusNid] [int] NOT NULL, [TotalReceivableRemaining] [money] NULL, [ReceivablesNote] [nvarchar](40) NULL, [isvoided] [int] NULL) GO INSERT INTO [dbo].[Orders] ([TicketNumber] ,[SlsEmpNid] ,[ToCusNid] ,[TotalReceivableRemaining] ,[ReceivablesNote] ,[isvoided]) VALUES ( 154917, 35, 2315, 0.00, NULL, 0 ), ( 157725, 114, 10807, 0.00, NULL, 0 ), ( 158673, 114, 10807, 0.00, NULL, 0 ), ( 173903, 123, 1368, 0.00, NULL, 0 ) GO CREATE TABLE [dbo].[OrderLines] ([ItemNid] [int] NOT NULL, [OrderTicketNumber] [int] NOT NULL, [QtyShipped] [int] NOT NULL) GO INSERT INTO [dbo].[OrderLines] ([Itemnid], [OrderTicketNumber], [QtyShipped]) VALUES (61, 154917, 5), (64, 154917, 5), (64, 157725, 1), (1238, 157725, 1), (63, 157725, 1), (64, 158673, 4), (1344, 158673, 1), (360, 158673, 1), (100, 173903, 0), (193, 173903, 1), (2711, 173903, 1), (1252, 173903, 1), (2964, 173903, 1), (208, 173903, 1), (51, 173903, 3), (63, 173903, 2), (61, 173903, 3), (62, 173903, 2), (56, 173903, 0), (64, 173903, 2), (57, 173903, 1), (1238, 173903, 3), (2276, 173903, 2), (58, 173903, 1), (246, 173903, 1), (248, 173903, 1), (245, 173903, 1), (2341, 173903, 1), (1042, 173903, 1) GO /* Create Items */ CREATE TABLE [dbo].[Items] ([ItemNid] [int] NOT NULL, [RecName] [nvarchar](60) NOT NULL, [CommissionPct] [decimal](5, 2) NOT NULL, [ProductClassNid] [int] NULL) GO INSERT INTO [dbo].[Items] ([ItemNid], [RecName], [CommissionPct], [ProductClassNid]) VALUES (51, 'Product A', 2, 2), (56, 'Product B', 2, 2), (57, 'Product C', 2, 2), (58, 'Product D', 2, 2), (61, 'Product E', 0.5, 2), (62, 'Product F', 0.5, 2), (63, 'Product G', 1.5, 2), (64, 'Product H', 0.5, 2), (100, 'Product I', 2, 2), (193, 'Product J', 2, 2), (208, 'Product K', 0.5, 2), (245, 'Product L', 2, 2), (246, 'Product M', 0.5, 2), (248, 'Product N', 0.5, 2), (360, 'Product O', 0, 4), (1042, 'Product P', 11, 1), (1238, 'Product Q', 1.5, 2), (1252, 'Product R', 2.67, 1), (1344, 'Product S', 3.67, 1), (2276, 'Product T', 2, 2), (2341, 'Product U', 2.67, 1), (2711, 'Product V', 2, 2), (2964, 'Product W', 0.5, 2) GO /*Create CusPaymentApplies */ CREATE TABLE [dbo].[CusPaymentApplies] ([ToOrderTicketNumber] [int] NULL, [BatchEntryDate] [datetime] NOT NULL) GO INSERT INTO [dbo].[CusPaymentApplies] ([ToOrderTicketNumber], [BatchEntryDate]) VALUES (154917, '2011-01-28 00:00:00.000'), (157725, '2011-01-27 00:00:00.000'), (158673, '2011-01-27 00:00:00.000'), (173903, '2011-01-26 00:00:00.000') GO /* Create Customers */ CREATE TABLE [dbo].[Customers] ([ActiveFlag] [bit] NOT NULL, [AcctManagerNid] [int] NULL, [CusNid] [int] NOT NULL, [RecName] [nvarchar](40) NOT NULL) GO INSERT INTO [dbo].[Customers] ([ActiveFlag], [AcctManagerNid], [CusNid], [RecName]) VALUES (1, 196, 1368, 'Customer 1'), (1, 109, 2315, 'Customer 2'), (1, 190, 10807, 'Customer 3') GO /* CREATE EMPLOYEES */ CREATE TABLE [dbo].[Employees] ([EmpNid] [int] NOT NULL, [RecName] [nvarchar](60) NOT NULL, [FirstName] [nvarchar](15) NOT NULL) GO INSERT INTO [dbo].[Employees] ([EmpNid], [RecName], [FirstName]) VALUES (35, 'John John', 'John'), (109, '(SR 09) Lisa Simpson', '(SR 09) Lisa'), (114, 'Jacob Johnson', 'Jacob'), (123, '(SR 10) Nate Bob', '(SR 10) Nate'), (190, '(SR 20) Stephen Stephenson', '(SR 20) Stephen'), (196, '(SR 22) Matthew Cuddy', '(SR 22) Matt') GO DECLARE @FromDate AS date, @ThruDate AS date, @Nid AS int SET @FromDate = '2011-01-24' SET @ThruDate = '2011-01-29' SELECT DISTINCT o.TICKETNUMBER, I.RECNAME, I.COMMISSIONPCT, L.QTYSHIPPED, e.recname AS 'SALESREP', c.AcctManagerNid, o.SlsEmpNid, C.RECNAME AS 'CUSTOMER' FROM ORDERS AS O JOIN ORDERLINES AS L ON O.TICKETNUMBER = L.ORDERTICKETNUMBER JOIN ITEMS AS I ON L.ITEMNID = I.ITEMNID JOIN CUSPAYMENTAPPLIES AS CP ON O.TICKETNUMBER = cp.TOORDERTICKETNUMBER LEFT JOIN CUSTOMERS AS C ON C.CUSNID = O.TOCUSNID LEFT OUTER JOIN EMPLOYEES AS E ON e.empnid = CASE WHEN c.AcctManagerNid o.SlsEmpNid AND e.FirstName NOT LIKE '%SR%' OR e.FirstName LIKE '(SR 00%' OR e.FirstName LIKE '(SR 99%' THEN c.AcctManagerNid ELSE o.SlsEmpNid END WHERE o.TOTALRECEIVABLEREMAINING = 0 AND cp.BATCHENTRYDATE BETWEEN @FromDate AND @ThruDate AND I.PRODUCTCLASSNID IN (1, 2) AND C.ACTIVEFLAG = 1 AND (O.RECEIVABLESNOTE NOT LIKE 'NC%' OR O.RECEIVABLESNOTE IS NULL) AND O.ISVOIDED = 0 ORDER BY SALESREP, TicketNumber, RecName
I have this now partially working. If I enter a specified empnid then I get the correct return set but when I use the CASE WHEN in the LEFT JOIN it seems that I'm dropping approximately 1,000 rows. DECLARE @FromDate AS date, @ThruDate AS date, @Nid AS int SET @FromDate = '2011-01-24' SET @ThruDate = '2011-01-29' SELECT TICKETNUMBER, I.RECNAME, I.COMMISSIONPCT, L.QTYSHIPPED, E.RECNAME AS 'SALESREP', C.RECNAME AS 'CUSTOMER' FROM ORDERS AS O JOIN ORDERLINES AS L ON O.TICKETNUMBER = L.ORDERTICKETNUMBER JOIN ITEMS AS I ON L.ITEMNID = I.ITEMNID JOIN DBO.CUSPAYMENTAPPLIES AS CP ON O.TICKETNUMBER = TOORDERTICKETNUMBER LEFT JOIN CUSTOMERS AS C ON C.CUSNID = O.TOCUSNID JOIN EMPLOYEES AS E ON e.EmpNid = CASE WHEN o.SlsEmpNid c.AcctManagerNid AND e.FirstName NOT LIKE '(SR%' THEN c.AcctManagerNid ELSE o.SlsEmpNid END WHERE TOTALRECEIVABLEREMAINING = 0 AND BATCHENTRYDATE BETWEEN @FromDate AND @ThruDate AND I.PRODUCTCLASSNID IN (1, 2) AND C.ACTIVEFLAG = 1 AND (O.RECEIVABLESNOTE NOT LIKE 'NC%' OR O.RECEIVABLESNOTE IS NULL) AND O.ISVOIDED = 0 ORDER BY SALESREP Does anyone know what I may be over looking? hmmmm.....
You have changed your join type here from explicity stating a `LEFT JOIN` to using just `JOIN`, depending upon foreign keys and column nullability you may be seeing an inner join in the second query. Have you tried explicity stating the join as a `LEFT JOIN` after adding the `CASE` statement?
@WilliamD - I had to modify the CASE WHEN. I had to remove the LEFT JOIN to a JOIN because of my CASE WHEN statement was pulling Nulls instead of the o.slsempnid when it should have. I had some LIKE statements incorrect which was causing me some issues. I had to modify the e.firstname statements to give me what I was needing. I think I have this resolved. I will post my recent code which seems to be giving me the correct return set.
@WilliamD - After looking into this more the LEFT is actually needed but I am getting NULLS returned. NULL 109 35 Center NULL 109 35 Center NULL 190 114 HSCT NULL 190 114 HSCT NULL 190 114 HSCT NULL 190 114 HSCT NULL 190 114 HSCT In the case above the name of the person assigned as 109 and 190 should have their names showing. Can you further assist please?
Have you tried a scalar-valued function to determine the correct employee ID, and used that in the query rather than the CASE function? Here's what I came up with: CREATE FUNCTION dbo.GetAppropriateEmployeeId ( @SalesEmpId INT, @AccountManagerEmpId INT ) RETURNS INT AS BEGIN DECLARE @EmployeeID INT DECLARE @EmpName NVARCHAR(15) SELECT @EmpName = FirstName FROM dbo.Employees WHERE EmpNid = @SalesEmpId IF( ( (NOT (@EmpName LIKE '(SR%')) OR @EmpName LIKE '(SR 00%' OR @EmpName LIKE '(SR 99%') AND @SalesEmpId <> @AccountManagerEmpId ) SET @EmployeeID = @AccountManagerEmpId ELSE SET @EmployeeID = @SalesEmpId RETURN @EmployeeID END To save space, I'm only including the relevant portion of the query from above: JOIN DBO.CUSPAYMENTAPPLIES AS CP ON O.TICKETNUMBER = TOORDERTICKETNUMBER LEFT JOIN CUSTOMERS AS C ON C.CUSNID = O.TOCUSNID JOIN EMPLOYEES E ON e.empnid = dbo.getAppropriateEmployeeId(o.SlsEmpNid, c.AcctManagerNid)
Using a scalar function like that will be a performance pig. The function will be called for each record that is joined to it. One way to call change that would be to change the function into a table value function and use OUTER or CROSS APPLY. However, a query will be writable without the need for a function at all.
@WilliamD and djpbusinessinc - I am somewhat following you both but I think that the function and potential performance issues is probably not the best choice for me. I am however going to use the function just so I can get a "feel" for what it is doing. (since I'm still learning and all.) @WilliamD - I am interested in seeing the query in which you use for this. I still have tried many other ways to write this but yet still cannot get the return result sets I need.
OK, so I took some time off this weekend - otherwise you would have had an answer earlier. As far as I understand it the problem has two solutions that spring to mind, the first being to change the function supplied by @djpbusinessinc into a table valued function: CREATE FUNCTION dbo.GetAppropriateEmployeeId (@SalesEmpId int, @AccountManagerEmpId int) RETURNS TABLE AS RETURN SELECT CASE WHEN (FirstName NOT LIKE '(SR%)' OR FirstName LIKE '(SR 00%)' OR FirstName LIKE '(SR 99%)') AND @SalesEmpId @AccountManagerEmpId THEN @SalesEmpId ELSE @AccountManagerEmpId END AS EmpNid FROM dbo.Employees E WHERE EmpNid = @SalesEmpId GO DECLARE @FromDate AS date, @ThruDate AS date, @Nid AS int SET @FromDate = '2011-01-24' SET @ThruDate = '2011-01-29' SELECT DISTINCT O.TicketNumber, I.RecName, I.CommissionPct, L.QtyShipped, E.RecName AS 'SALESREP', C.AcctManagerNid, O.SlsEmpNid, C.RecName AS 'CUSTOMER' FROM dbo.Orders AS O JOIN dbo.OrderLines AS L ON O.TicketNumber = L.OrderTicketNumber JOIN dbo.Items AS I ON L.ItemNid = I.ItemNid JOIN dbo.CusPaymentApplies AS CP ON O.TicketNumber = CP.ToOrderTicketNumber JOIN dbo.Customers AS C ON C.CusNid = O.ToCusNid CROSS APPLY dbo.GetAppropriateEmployeeId(o.SlsEmpNid, c.AcctManagerNid) GAEI INNER JOIN dbo.Employees E ON GAEI.EmpNid = E.EmpNid WHERE O.TotalReceivableRemaining = 0 AND CP.BatchEntryDate BETWEEN @FromDate AND @ThruDate AND I.ProductClassNid IN (1, 2) AND C.ActiveFlag = 1 AND (O.ReceivablesNote NOT LIKE 'NC%' OR O.ReceivablesNote IS NULL) AND O.isvoided = 0 ORDER BY SALESREP, TicketNumber, RecName I then rewrote the code to use two left joins on `Employee`, joining on either Sales Person or Account Manager, then doing the CASE comparison in the output. DECLARE @FromDate AS date, @ThruDate AS date, @Nid AS int SET @FromDate = '2011-01-24' SET @ThruDate = '2011-01-29' SELECT DISTINCT O.TicketNumber, I.RecName, I.CommissionPct, L.QtyShipped, CASE WHEN AMGR.EmpNid SLS.EmpNid AND (AMGR.FirstName NOT LIKE '%SR%' OR AMGR.FirstName LIKE '(SR 00%' OR AMGR.FirstName LIKE '(SR 99%') THEN AMGR.RecName ELSE SLS.RecName END AS 'SALESREP', C.AcctManagerNid, O.SlsEmpNid, C.RecName AS 'CUSTOMER' FROM dbo.Orders AS O JOIN dbo.OrderLines AS L ON O.TicketNumber = L.OrderTicketNumber JOIN dbo.Items AS I ON L.ItemNid = I.ItemNid JOIN dbo.CusPaymentApplies AS CP ON O.TicketNumber = CP.ToOrderTicketNumber JOIN dbo.Customers AS C ON C.CusNid = O.ToCusNid LEFT JOIN dbo.Employees AS AMGR ON AMGR.EmpNid = C.AcctManagerNid LEFT JOIN dbo.Employees AS SLS ON SLS.EmpNid = O.SlsEmpNid WHERE O.TotalReceivableRemaining = 0 AND CP.BatchEntryDate BETWEEN @FromDate AND @ThruDate AND I.ProductClassNid IN (1, 2) AND C.ActiveFlag = 1 AND (O.ReceivablesNote NOT LIKE 'NC%' OR O.ReceivablesNote IS NULL) AND O.isvoided = 0 ORDER BY SALESREP, TicketNumber, RecName ; Both solutions seem to return the correct results (you have to check that out), but the second solution (without a function) was faster on my test box. Your mileage may vary there, so test both out with a representative data set. Let us know if that solves the problem.
@WilliamD - This is looking really good so far! I'm running the reports and verify the data! I will do a complete follow up by end of day. GENIUS!!! Thanks again. Question: So you can actually join one table twice as long as you do an alias that differs from itself? Is that how I am reading this query?
You can reference a table as many times as you want as long as you use an alias, yes. If you want to filter on a specific employee it depends where you want the filter to work. If it is over the entire data-set, I would suggest possibly packing the entire select (the second one in my answer - remove the order by) into a CTE, then selecting from the CTE specifying the necessary WHERE clause. E.G. WITH OrderOverview AS ( SELECT ..... ) SELECT * FROM OrderOverview WHERE ....
@Noonies - I'm not too sure what you mean here. The quick example would be to filter on the entire dataset, you could state it so that your variable is evaluated against AcctManagerNid or SLSEmpNid or against both, just try them out with an AND or and OR condition, or am I missing your point?
