title: Query giving different Values
body: <p>I have a query where I am stripping off a few unwanted fields and putting in one qualifier in the where clause.
  When exported to excel, and all of the fields in Load Size (see first query) are removed, i get 84 rows.
  In the second query, i simply remove a few fields and add the qualifier to get rid of those fields which have a load size of 0. i run it with the same parameters, but get 87 rows. I have no clue as to what removal has triggered this.
  Original Query:</p>
  
  <pre><code>    Declare @start Varchar(20),
              @end VarChar(20),
              @division varchar(2),
              @group varchar(20)
  
              set @start = '02/01/2015'
              set @end = '02/09/2015'
              set @division= '02'
              set @group  = 'anodize'
  
      --Get the most recent order date
  SELECT
      MAX(DATEPHYSICAL) AS 'DATEPHYSICAL',
      ITEMID
  INTO
      #tempOrderDates
  FROM
      dbo.INVENTTRANS
  GROUP BY
      ITEMID
  
  SELECT 
      ITEMID,
      SUM(dbo.CUSTINVOICETRANS.QTY) AS 'Quanity',
      SUM(LINEAMOUNTMST) AS 'Sales',
      COUNT(DISTINCT dbo.CUSTINVOICEJOUR.SALESID) AS 'Total Orders'
  INTO
      #tempItemRevenue
  FROM
      dbo.CUSTINVOICEJOUR INNER JOIN 
      dbo.CUSTINVOICETRANS ON dbo.CUSTINVOICEJOUR.INVOICEID = dbo.CUSTINVOICETRANS.INVOICEID
  WHERE
      dbo.CUSTINVOICETRANS.DIMENSION2_ IN (@division)
      AND
      CONVERT(DATETIME, dbo.CUSTINVOICETRANS.INVOICEDATE, 101) BETWEEN @start AND @end
  GROUP BY
      ITEMID
  
  SELECT
      ITEMGROUPID AS 'Process',
      [DESCRIPTION] AS 'Division',
      dbo.INVENTTABLE.ITEMID AS 'Item ID',
      EXTITEM AS 'Cust Item ID',
      dbo.INVENTTABLE.CUSTNAME AS 'Customer',
      cu.[SalesGroup],
      dbo.USERINFO.NAME AS 'Sales Engineer',
      cu.[Street],
      cu.[City],
      cu.[State],
      cu.[ZipCode],
      ISNULL(PRICE / CASE WHEN PRICEUNIT = 0 THEN NULL ELSE PRICEUNIT END, 0) AS 'Unit Price',
      PRICEDATE AS 'Date of Price',
      LOADQTY AS 'Load Size',
      LOADQTY * ISNULL(PRICE / CASE WHEN PRICEUNIT = 0 THEN NULL ELSE PRICEUNIT END, 0) AS 'Load Value',
      Sales,
      Quanity,
      [Total Orders],
      CEILING(ISNULL(Quanity / CASE WHEN [Total Orders] = 0 THEN NULL ELSE [Total Orders] END, 0)) AS 'Avg. Order Quanity',
      DATEPHYSICAL AS 'Last Order Date'
  FROM
      dbo.INVENTTABLE INNER JOIN 
      dbo.INVENTTABLEMODULE ON dbo.INVENTTABLE.ITEMID = dbo.INVENTTABLEMODULE.ITEMID INNER JOIN 
      #tempOrderDates ON dbo.INVENTTABLE.ITEMID = #tempOrderDates.ITEMID INNER JOIN 
      #tempItemRevenue ON dbo.INVENTTABLE.ITEMID = #tempItemRevenue.ITEMID INNER JOIN 
      dbo.DIMENSIONS ON NUM = dbo.INVENTTABLE.DIMENSION2_ INNER JOIN 
      dbo.CUSTTABLE cu ON ACCOUNTNUM = CUSTACCOUNT LEFT OUTER JOIN 
      dbo.SMMSALESUNITMEMBERS ON SALESUNITID = cu.SALESGROUP AND SALESMANAGER = 1 INNER JOIN 
      dbo.USERINFO ON ID = SALESMANID
  WHERE
      MODULETYPE = 2
      AND
      ITEMGROUPID IN (@group)
      AND
      dbo.INVENTTABLE.DIMENSION2_ IN (@division)
  
  DROP TABLE #tempOrderDates
  DROP TABLE #tempItemRevenue
  </code></pre>
  
  <p>Modified query getting rid of salesman stuff and customer information:</p>
  
  <pre><code>    Declare @start Varchar(20),
          @end VarChar(20),
          @division varchar(2),
          @group varchar(20)
  
          set @start = '02/01/2015'
          set @end = '02/09/2015'
          set @division= '02'
          set @group  = 'anodize'
  
  
  
  SELECT 
      ITEMID,
      SUM(dbo.CUSTINVOICETRANS.QTY) AS 'Quanity',
      SUM(LINEAMOUNTMST) AS 'Sales',
      COUNT(DISTINCT dbo.CUSTINVOICEJOUR.SALESID) AS 'Total Orders'
  INTO
      #tempItemRevenue
  FROM
      dbo.CUSTINVOICEJOUR INNER JOIN 
      dbo.CUSTINVOICETRANS ON dbo.CUSTINVOICEJOUR.INVOICEID = dbo.CUSTINVOICETRANS.INVOICEID
  WHERE
      dbo.CUSTINVOICETRANS.DIMENSION2_ IN (@division)
      AND
      CONVERT(DATETIME, dbo.CUSTINVOICETRANS.INVOICEDATE, 101) BETWEEN @start AND @end
  GROUP BY
      ITEMID
  
  SELECT
      ITEMGROUPID AS 'Process',
      [DESCRIPTION] AS 'Division',
      LOADQTY AS 'Load Size',
      LOADQTY * ISNULL(PRICE / CASE WHEN PRICEUNIT = 0 THEN NULL ELSE PRICEUNIT END, 0) AS 'Load Value',
      Sales,
      Quanity
  
  FROM
      dbo.INVENTTABLE 
      INNER JOIN 
      dbo.INVENTTABLEMODULE ON dbo.INVENTTABLE.ITEMID = dbo.INVENTTABLEMODULE.ITEMID 
      inner JOIN 
      #tempItemRevenue ON dbo.INVENTTABLE.ITEMID = #tempItemRevenue.ITEMID 
      INNER JOIN 
      dbo.DIMENSIONS ON NUM = dbo.INVENTTABLE.DIMENSION2_ 
  
  
  WHERE
      MODULETYPE = 2
      AND
      ITEMGROUPID IN (@group)
      AND
      dbo.INVENTTABLE.DIMENSION2_ IN (@division)
      and LOADQTY &gt;0
  
  
  DROP TABLE #tempItemRevenue
  </code></pre>
  
  <p>I don;t understand what is missing that gives me different results. I have tried picking the query apart bit by bit but i do not see where it breaks.</p>