problem with query using calculated field

Question

Could someone please help:

 

I have created a query that uses a support table called ‘tbluser’.   tbluser contains two fields, SalesRepID and rptdetail.    if the field rptdetail is equal to 1 I will use the salesrepid from the order detail record in my query.  if rptdetail is equal to 0,  I need to use the salesrepid from a joined customer table.

 

Here is the query that is yielding the incorrect sales numbers

 

   

DECLARE @tmp1 TABLE

(

   OutsideSlsrep varchar(20) ,

   sales MONEY

)

 

INSERT  @tmp1

        SELECT

        'OutsideSlsRep' = ( CASE WHEN ( SELECT  rptdetail

                                        FROM    tbluser

                                        WHERE   SalesRepId =     

                                                s.OutsideSlsrep

                                             ) = 1 THEN s.OutsideSlsrep

                                        ELSE c.OutsideSlsrep

                                    END ) ,

                s.Sales AS SALES

FROM    ord_detail AS s

        INNER JOIN Customer AS c ON c.Cono = s.Cono

                                    AND c.CustId = s.CustId

WHERE   s.cono = 1

        AND s.InvoiceDate BETWEEN '03/01/2012' AND '03/31/2012'                  

       

SELECT OutsideSlsrep, SUM(sales) FROM @tmp1

GROUP BY OutsideSlsrep

 

************************************************

Below is a query that If I use the first select I will get the correct

numbers for salesreps that have the field rptdetail set to 1, and

vice versa

 

DECLARE @tmp1 TABLE

    (

      CustRep VARCHAR(20) ,

      detailrep varchar(20),

      sales MONEY

    )

 

 

 

INSERT  @tmp1

        SELECT

                c.outsideSlsRep,

                s.OutsideSlsrep,

                s.Sales AS SALES

FROM    ord_detail AS S

        INNER JOIN Customer AS c ON c.Cono = s.Cono

                                    AND c.CustId = s.CustId

WHERE   s.cono = 1

        AND s.InvoiceDate BETWEEN '03/01/2012' AND '03/31/2012'                  

 

-- This yields correct numbers for if rptdetail = 1    

SELECT detailRep  ,SUM(sales) FROM @tmp1

GROUP BY detailRep

 

-- This yields correct numbers if rptdetail = 0

SELECT CustRep  ,SUM(sales) FROM @tmp1

GROUP BY CustRep

Any help with this query would be greatly appreciated.  I have been staring at

this for hours and I can't figure out why using a subquery just doesn't work.

Thanks

Dave.

Answer 1

You can simply join your tables:

SELECT  S.* ,
		CASE WHEN u.RptDetail = 1 THEN S.SalesRepId
			 ELSE C.SalesRepId
		END
FROM    ord_detail S
		INNER JOIN Customer C ON C.Cono = S.Cono
								 AND C.CustId = S.CustId
		INNER JOIN tbluser U ON U.SalesRepId = S.SalesRepId;

Customer and your helper table should have one row per order detail row. Otherwise there is a flaw in the model.
Use it with a CTE or sub-query built your sum:

WITH    Data
		  AS ( SELECT   S.Sales ,
						CASE WHEN u.RptDetail = 1 THEN S.SalesRepId
							 ELSE C.SalesRepId
						END DynSalesRepId
			   FROM     ord_detail S
						INNER JOIN Customer C ON C.Cono = S.Cono
												 AND C.CustId = S.CustId
						INNER JOIN tbluser U ON U.SalesRepId = S.SalesRepId
			 )
	SELECT  DynSalesRepId ,
			SUM(Sales)
	FROM    Data
	GROUP BY DynSalesRepId;

Answer 2

Stefan:  Turns out Both your solution and my original query were working correctly.  The issue was that I needed to better understand the data.  I was actually getting three additional records with my dynamic query vs the straight comparison.  The reason was that someone had altered some of the ord_detail records.  In one example I found three records where the ord_detail SalesRepId was 'AS' and the salesRepID in the customer table was 'CJH'.  Note: the rep 'AS' was did not have the field rptdetail = 1 one so the dynamic query used the repid off the Customer table 'CJH' thus adding three additional records to the total for the salesrep 'CJH' and increasing his sales by that amount. 

Thanks again for your help

Dave.