title: Getting fixed T-SQL for Warning: Null value is eliminated by an aggregate or other SET operation
body: <p>I have created the following T-SQL code in SQL Server Management Studio. In this, I am trying to acheive items details from <strong>each</strong> Customer Management station database which is in  remote server 770Main.</p>
  
  <p>All the customer databases have same database structure/design. But there are about 60 customers. So using T-SQL I put the entire script into a template string, with {dbname} placeholders and execute <code>@SQLSCRIPT</code>within a loop. When I execute this I am getting Warning:</p>
  
  <blockquote>
    <p>Null value is eliminated by an aggregate or other SET operation.</p>
  </blockquote>
  
  <p>However, when I execute SQL template script (See Below) separately for one customer only it runs absolutely fine. </p>
  
  <pre><code>SELECT DISTINCT vend.contact
                     ,C.customername
                      ,item.vendoritem
                      ,item.description1
                      ,item.issuecost
                      ,bin.station
                      ,ST.CRIBSTTYPE
                      ,R.RBSTTYPE
                      ,Bin.packQty
                      ,item.orderqty
                      ,weeklysales.lastweeksales
                      ,weeklysales.wtdsales
                      ,st.cribmin 
                      ,st.cribmax
                      ,R.MYMIN
                      ,R.MYMAX
                      ,acbin.binqoh [AutoCrib QOH]
      FROM   [770main].{dbname}.dbo.bin Bin 
  
         LEFT OUTER JOIN [770main].{dbname}.dbo.item Item 
                      ON bin.item = item.code 
          inner join [770main].{dbname}.dbo.release as rls on rls.itemid=item.code and item.vendor=rls.vendorid
         LEFT OUTER JOIN [770main].{dbname}.dbo.company Company 
                      ON bin.aspprocessed = company.aspprocessed 
          left join (select sum(onhand) binqoh,aspprocessed,item from [770main].{dbname}.dbo.bin where station not like ''ods%''  group by aspprocessed,item ) acbin 
                      on acbin.item = Item.code and acbin.aspprocessed = Company.aspprocessed
         LEFT OUTER JOIN [770main].{dbname}.dbo.vend Vend 
                      ON item.vendor = vend.myno 
         LEFT OUTER JOIN customer C 
                      ON C.customercode collate SQL_Latin1_General_CP1_CI_AS  = vend.contact collate SQL_Latin1_General_CP1_CI_AS
  
          left join 
      (select productcode
             , ordh.companyid
             ,sum( CASE WHEN  (invoicedate &gt;= dateadd(wk, datediff(wk, 0, getdate())-1, 0) and invoicedate &lt;= DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6)) 
                     THEN (ordhl.NetPrice*ordhl.shippedquantity/ coalesce(ordhl.UnitOfMeasurePriceFactor,1) / coalesce(ordh.CurrencyRate,1))
                   ELSE Null END) lastweeksales
                   ,
          sum( CASE WHEN   (invoicedate &gt;= dateadd(wk, datediff(wk, 0, getdate()), 0) and invoicedate &lt;= getdate())
                   THEN (ordhl.NetPrice*ordhl.shippedquantity/ coalesce(ordhl.UnitOfMeasurePriceFactor,1) / coalesce(ordh.CurrencyRate,1))
                   ELSE Null END) wtdsales
       from orderhistory ordh inner join orderhistoryline ordhl on
       ordh.CompanyID = ordhl.companyid and
       ordh.ordernumber = ordhl.ordernumber
       where 
       (invoicedate &gt;= dateadd(wk, datediff(wk, 0, getdate())-1, 0) and invoicedate &lt;= getdate()) and
       ShippedQuantity &lt;&gt; 0 and ordh.CompanyID = ''002''
       group by productcode, ordh.companyid
       ) weeklysales on
       weeklysales.productcode = item.vendoritem 
      left join [770main].{dbname}.dbo.station s on bin.station = s.myno
      FULL JOIN STNMM St ON Bin.Item=St.ITEM 
      FULL JOIN ROBOMM R ON Bin.Item=R.ITEM
      WHERE 
      C.COMPANYID = ''002''
      GROUP BY vend.contact
                     ,C.customername
                      ,item.vendoritem
                      ,bin.station
                      ,ST.CRIBSTTYPE
                      ,R.RBSTTYPE
                      ,Bin.packQty
                      ,item.orderqty
                      ,weeklysales.lastweeksales
                      ,weeklysales.wtdsales
                      ,st.cribmin 
                      ,st.cribmax
                      ,R.MYMIN
                      ,R.MYMAX
                      ,item.description1
                      ,acbin.binqoh 
                      ,item.issuecost
  </code></pre>
  
  
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  
  
  IF (object_id('TMPProductbyCustomer')) IS NOT NULL
  BEGIN
    DROP TABLE TMPProductbyCustomer
  print('dropped')
  
  
  END
  
  
  
  DECLARE @sql_script VARCHAR(max)
  
  DECLARE @template VARCHAR(max)
  DECLARE @Dbname varchar(100)
  DECLARE @table table(id int identity(1,1),dbname varchar(100))
  
  insert into @table
  
  SELECT 'Abipa'
  union select 'ascoac'
  union SELECT 'BourgaultE' 
  UNION SELECT 'Avcorp'
  UNION SELECT 'Blackcat'
  
  DECLARE @COUNTER INT = 1
  DECLARE @END int = (select count(*) from @table)
  
  CREATE table TMPProductbyCustomer ( 
  CustomerID varchar(6)
  ,CompanyName       varchar(100) null
  ,ItemVendorItem    varchar(100)
  ,Decription1 varchar(35) null
  ,IssueCost money 
  ,Station varchar(4)
  ,CribStationType int null
  ,RoboStationType int null
  ,PackQty int
  ,ORDERQTY int 
  ,LastWeekSales money null
  ,WTDSales money null
  ,CRIBMIN int null
  ,CRIBMAX int null
  ,MYMIN int null
  ,MYMAX int null
  ,OnHand int 
       )
  WHILE @COUNTER &lt;= @END
  
  BEGIN
  
  set @DBNAME = (select dbname from @table where ID = @COUNTER )
  set @template ='
  WITH STNMM (item, station, cribsttype,name,packqty,onhand,burnqty,cribmin,cribmax)
  
  AS
  (
      select
      bin.item
      ,bin.station
      ,station.[type] AS CRIBSTTYPE
      ,station.name
      ,bin.packqty
      ,bin.onhand
      ,bin.burnqty
      ,bin.minimum as cribmin
      ,bin.maximum as cribmax
      from [770main].{dbname}.dbo.bin as bin
      left join [770main].{dbname}.dbo.station as station on bin.station=station.myno
      where station.[type] &lt;&gt; 6
  ),
  ROBOMM (item, station, RBSTTYPE,name,pkgqty,mymin,mymax)
  AS
  (
      SELECT 
      MM.ITEM
      ,MM.STATION
      ,station.[type] as RBSTTYPE
      ,STATION.NAME
      ,MM.PKGQTY
      ,MM.MYMIN
      ,MM.MYMAX
      FROM [770MAIN].{dbname}.DBO.ROBOMINMAX AS MM
      LEFT JOIN [770MAIN].{dbname}.DBO.STATION AS STATION ON MM.STATION=STATION.MYNO
      WHERE station.[type] = 6 )
  
  INSERT INTO TMPProductbyCustomer SELECT DISTINCT vend.contact
                 ,C.customername
                 ,item.vendoritem
                  ,item.description1
                  ,item.description2
                  ,item.issuecost
                  ,bin.station
                  ,ST.CRIBSTTYPE
                  ,R.RBSTTYPE
                  ,Bin.packQty
                  ,item.orderqty
                  ,weeklysales.lastweeksales
                  ,weeklysales.wtdsales
                  ,st.cribmin 
                  ,st.cribmax
                  ,R.MYMIN
                  ,R.MYMAX
                  ,acbin.binqoh [AutoCrib QOH]
  
  FROM   [770main].{dbname}.dbo.bin Bin 
  
     LEFT OUTER JOIN [770main].{dbname}.dbo.item Item 
                  ON bin.item = item.code 
      inner join [770main].{dbname}.dbo.release as rls on rls.itemid=item.code and item.vendor=rls.vendorid
     LEFT OUTER JOIN [770main].{dbname}.dbo.company Company 
                  ON bin.aspprocessed = company.aspprocessed 
      left join (select sum(onhand) binqoh,aspprocessed,item from [770main].{dbname}.dbo.bin where station not like ''ods%''  group by aspprocessed,item ) acbin 
                  on acbin.item = Item.code and acbin.aspprocessed = Company.aspprocessed
     LEFT OUTER JOIN [770main].{dbname}.dbo.vend Vend 
                  ON item.vendor = vend.myno 
     LEFT OUTER JOIN customer C 
                  ON C.customercode collate SQL_Latin1_General_CP1_CI_AS  = vend.contact collate SQL_Latin1_General_CP1_CI_AS
  LEFT JOIN 
  (select productcode
    , ordh.companyid
  ,       sum( CASE WHEN  (invoicedate &gt;= dateadd(wk, datediff(wk, 0, getdate())-1, 0) and invoicedate &lt;= DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6)) 
                 THEN (ordhl.NetPrice*ordhl.shippedquantity/ coalesce(ordhl.UnitOfMeasurePriceFactor,1) / coalesce(ordh.CurrencyRate,1))
               ELSE 0 END) lastweeksales
               ,
      sum( CASE WHEN   (invoicedate &gt;= dateadd(wk, datediff(wk, 0, getdate()), 0) and invoicedate &lt;= getdate())
               THEN (ordhl.NetPrice*ordhl.shippedquantity/ coalesce(ordhl.UnitOfMeasurePriceFactor,1) / coalesce(ordh.CurrencyRate,1))
               ELSE 0 END) wtdsales
   from orderhistory ordh inner join orderhistoryline ordhl on
   ordh.CompanyID = ordhl.companyid and
   ordh.ordernumber = ordhl.ordernumber
   where 
   (invoicedate &gt;= dateadd(wk, datediff(wk, 0, getdate())-1, 0) and invoicedate &lt;= getdate()) and
   ShippedQuantity &lt;&gt; 0 and ordh.CompanyID = ''002''
   group by productcode, ordh.companyid
   ) weeklysales on
   weeklysales.productcode = item.vendoritem 
  left join [770main].{dbname}.dbo.station s on bin.station = s.myno
  FULL JOIN STNMM St ON Bin.Item=St.ITEM 
  FULL JOIN ROBOMM R ON Bin.Item=R.ITEM
  WHERE 
  C.COMPANYID = ''002''
  GROUP BY vend.contact
                 ,C.customername
                  ,item.vendoritem
                  ,bin.station
                  ,ST.CRIBSTTYPE
                  ,R.RBSTTYPE
                  ,Bin.packQty
                  ,item.orderqty
                  ,weeklysales.lastweeksales
                  ,weeklysales.wtdsales
                  ,st.cribmin 
                  ,st.cribmax
                  ,R.MYMIN
                  ,R.MYMAX
                  ,item.description1
                  ,acbin.binqoh 
                  ,item.issuecost
  '
  SET @SQL_SCRIPT = REPLACE(@template, '{dbname}', @DBNAME)
  
  EXECUTE (@sql_script)
  
  set @COUNTER = @COUNTER +1
  
  end
  
  select * from TMPProductbyCustomer 
  </code></pre>