title: TSQL Determine Correct Day
body: <p>I found an issue yesterday with either my table design or a query that I pull data from and I need some guidance/suggestions on how to proceed and fix my problem.</p>
  
  <p>My table is as follows:</p>
  
  <pre><code>CREATE TABLE 
  [DT].[CountTotals](
      [Acct] [varchar](8) NOT NULL,
      [TCount] [smallint] NOT NULL ,
      [TDate] [smalldatetime] NOT NULL,
      [PostDate] [smalldatetime] NOT NULL ,
      [DayCount] [tinyint] NULL ,
      [AggCount] [smallint] NULL ,
      [AggNumber] [numeric](10, 0) NULL,
      [IsReadyToArchive] [bit] NOT NULL 
  )
  </code></pre>
  
  <p>I pull this data using the following view:</p>
  
  <pre><code>CREATE VIEW [DT].[vwGetCountHistory]
  AS
  
      SELECT     
          C.Acct, C.TCount, C.TDate, C.PostDate
          , C.DayCount, C.AggCount, C.AggNumber
      FROM DT.CountTotals C
  
      UNION
  
      SELECT     
          AC.Acct, AC.TCount, AC.TDate, AC.PostDate
          , AC.DayCount, AC.AggCount, AC.AggNumber
      FROM ARCHIVE.DTA.CountTotals AC
  </code></pre>
  
  <p>All of that works fine.  The table has about 300k records total records including about 5000 new records added to it each day, and the PostDate is the date the records are added. My issue is coming from the field DayCount - this field gets increased by 1 each day it is in the table, until the <code>PostDate is &lt; GetDate()-90</code>.  My problem is in the following query:</p>
  
  <pre><code>SELECT     
          Acct
          , SUM(AggCount) AS SumofAggCount
  
      FROM DT.vwGetCountHistory
      WHERE (DayCount &lt;= 5)
          AND 
          (
              PostDate &gt;= @BusinessDate - 90
              AND PostDate &lt;= @BusinessDate
          )
      GROUP BY Acct
      HAVING (SUM(AggCount) &gt;= 4)
  </code></pre>
  
  <p>This query works wonderfully if I am searching for the current date because the DayCount would be correct.  But stupid me forgot that they can search for the data from previous days so my DayCount will have increased each day since the PostDate. </p>
  
  <p>I am looking for suggestions about how to remedy this problem.  Is there a way to determine the correct daycount?  </p>