title: SQL query not returning multiple results
body: <p>I am pretty new to SQL and trying to get a SQL query that should return results for multiple days. It wil however only return data for one of the selected days instead of all the dates. I have tried removing the "DISTINCT" statement but then it just returns multiple repeating results, but all for the same day once again. Any suugestions would be greatly appreciated. Thanks! </p>
  
  <pre><code>declare @Check_Date smalldatetime
  set @Check_Date =  convert(varchar(8),  DATEADD( DAY , -3, CURRENT_TIMESTAMP ), 112)
  
  SELECT
       DISTINCT [SIS].[dbo].[Stores].[StoreNbr] AS 'Store'
      ,[SIS].[dbo].[Stores].[StoreName] AS 'Name'
      ,[SIS].[dbo].[STORE_CALENDAR].[TRXN_DATE] AS 'Date'
      ,case when [SIS].[dbo].[STORE_POLLING_EXCEPTIONS].[REASON_MISSED] is null then substring([SIS].[dbo].[STORE_POLLING_EXCEPTIONS].[REASON_MISSED],1,49) else 'Not Yet Polled' end  as 'Reason Missed'
  
  FROM
      [SIS].[dbo].[DAILY_TOTALS], [SIS].[dbo].[Stores], [SIS].[dbo].[STORE_POLLING_EXCEPTIONS], [SIS].[dbo].[STORE_CALENDAR]
  
  WHERE
      [SIS].[dbo].[DAILY_TOTALS].[STORE_NUMBER] = [SIS].[dbo].[Stores].[StoreNbr] 
      AND [SIS].[dbo].[DAILY_TOTALS].[TRXN_DATE] = @Check_Date
      AND [SIS].[dbo].[DAILY_TOTALS].[TRXN_DATE] = [SIS].[dbo].[STORE_CALENDAR].[TRXN_DATE]
      AND [SIS].[dbo].[DAILY_TOTALS].[NET_SALES_AMOUNT] IS NULL
      AND [SIS].[dbo].[Stores].[StoreNbr] IN (
          SELECT [SIS].[dbo].[STORE_CALENDAR].[StoreNbr]
          FROM [SIS].[dbo].[STORE_CALENDAR]
          WHERE [SIS].[dbo].[STORE_CALENDAR].[TRXN_DATE] = @Check_Date
          )
      AND [SIS].[dbo].[Stores].[StoreNbr] IN (
          SELECT [SIS].[dbo].[Stores].[StoreNbr]
          FROM [SIS].[dbo].[Stores]
          WHERE [SIS].[dbo].[Stores].[ClosingOps] IS NULL
          )
      AND [SIS].[dbo].[Stores].[StoreNbr] NOT IN (
          SELECT [SIS].[dbo].[STORE_POLLING_EXCEPTIONS].[STORE_NUMBER]
          FROM [SIS].[dbo].[STORE_POLLING_EXCEPTIONS]
          WHERE [SIS].[dbo].[STORE_POLLING_EXCEPTIONS].[TRXN_DATE] = @Check_Date
          AND [SIS].[dbo].[STORE_POLLING_EXCEPTIONS].[REASON_MISSED] IS NOT NULL
          )
  
  UNION ALL
  
  SELECT
       DISTINCT [SIS].[dbo].[Stores].[StoreNbr] AS 'Store'
      ,[SIS].[dbo].[Stores].[StoreName] AS 'Name'
      ,[SIS].[dbo].[STORE_CALENDAR].[TRXN_DATE] AS 'Date'
      ,[SIS].[dbo].[STORE_POLLING_EXCEPTIONS].[REASON_MISSED] AS 'Reason Missed'
  
  FROM
      [SIS].[dbo].[DAILY_TOTALS], [SIS].[dbo].[Stores], [SIS].[dbo].[STORE_POLLING_EXCEPTIONS], [SIS].[dbo].[STORE_CALENDAR]
  
  WHERE
      [SIS].[dbo].[DAILY_TOTALS].[STORE_NUMBER] = [SIS].[dbo].[Stores].[StoreNbr] 
      AND [SIS].[dbo].[Stores].[StoreNbr] = [SIS].[dbo].[STORE_POLLING_EXCEPTIONS].[STORE_NUMBER]
      AND [SIS].[dbo].[DAILY_TOTALS].[TRXN_DATE] = @Check_Date  
      AND [SIS].[dbo].[DAILY_TOTALS].[TRXN_DATE] = [SIS].[dbo].[STORE_CALENDAR].[TRXN_DATE]
      AND [SIS].[dbo].[STORE_POLLING_EXCEPTIONS].[TRXN_DATE] = @Check_Date
      AND [SIS].[dbo].[DAILY_TOTALS].[NET_SALES_AMOUNT] IS NULL
      AND [SIS].[dbo].[Stores].[StoreNbr] IN (
          SELECT [SIS].[dbo].[STORE_CALENDAR].[StoreNbr]
          FROM [SIS].[dbo].[STORE_CALENDAR]
          WHERE [SIS].[dbo].[STORE_CALENDAR].[TRXN_DATE] = @Check_Date
          )
      AND [SIS].[dbo].[Stores].[StoreNbr] IN (
          SELECT [SIS].[dbo].[Stores].[StoreNbr]
          FROM [SIS].[dbo].[Stores]
          WHERE [SIS].[dbo].[Stores].[ClosingOps] IS NULL
          )
  
  ORDER BY 'Date', 'Store'
  </code></pre>