title: SQL Server : Group By records not on the same day
body: <p>I have a set of tables where we used to maintain transaction histories of bank account.</p>
  
  <p>We have applied grouping on the transaction and get the grouped transactions. This worked fine for normal accounts. I used this query to analyze the users regular transactions which happens on pattern like every week or fortnightly or monthly and it is used to to find the users Salary credit, and living expenses.</p>
  
  <p>When the transactions are based on joint account and if a patterned transaction happens on the same day with same description, the query sums up which is wrong.</p>
  
  <p>Ex:</p>
  
  <p>Husband &amp; Wife works on the same company and gets Salary on 1000 &amp; 500 respectively. The grouping should group 1000 separately and 500 separately. The transaction amount can differ because he or she could get variable pay. Because of this issue, it is calculate pattern wrong. Say if the its fortnightly, it calculates as weekly. Can someone shed some idea to fix this issue.</p>
  
  <p>The requirement is, if a group occurs twice in the same day, it should group them separately.</p>
  
  <p>SCHEMA</p>
  
  <pre><code>1. CustomerBankAccountTransactions
  Column_name                  Type     
  ---------------------------- ---------
  BankAcounttransactionID      nvarchar 
  BankAccountID                nvarchar 
  TransactionDate              datetime 
  Amount                       float    
  IsDebit                      nvarchar 
  RunningBalance               nvarchar 
  Description                  ntext    
  
  2. CustomerBankTransactionGroupLink
  Column_name                Type    
  -------------------------- --------
  ID                         numeric 
  TransactionGroupID         numeric 
  BankAcountTransactionID    nvarchar
  UpdateUser                 nvarchar
  UpdateDateTime             datetime
  
  3. CustomerTransactionGroup
  Column_name         Type    
  ------------------- --------
  ID                  numeric 
  SubCategoryID       numeric 
  GroupName           nvarchar
  UpdateUser          nvarchar
  UpdateDateTime      datetime
  IsDebit             nvarchar
  </code></pre>
  
  <p>The query sample is,</p>
  
  <pre><code>SELECT MAX(tg.ID),
         tg.GroupName,
         COUNT(tg.GroupName) AS GroupCount,
         CASE
             WHEN at.IsDebit = 't' 
                  THEN 'Debit'
             ELSE 'Credit'
         END AS TransactionType,
         MIN(at.Amount) AS MinimumAmount,
         MAX(at.Amount) AS MaximumAmount,
         AVG(at.Amount) AS AverageAmount,
         MIN(at.TransactionDate) AS FirstTransacionDate,
         MAX(at.TransactionDate) AS LastTransacionDate,
         CASE
             WHEN COUNT(at.TransactionDate) &lt;= 1 
                  THEN DATEDIFF(DAY, MIN(at.TransactionDate), 
                                MAX(at.TransactionDate))
             ELSE (DATEDIFF(DAY, MIN(at.TransactionDate), 
                            MAX(at.TransactionDate)) / (COUNT(at.TransactionDate) - 1))
         END AS TransactionInterval,
         DATEDIFF(DAY, MAX(at.TransactionDate), GETDATE()) AS DaysSinceLastTransaction,
         DATEDIFF(DAY, MIN(at.TransactionDate), MAX(at.TransactionDate)) AS TimeSpan
  FROM CustomerBankAccountTransactions AT
  INNER JOIN CustomerBankTransactionGroupLink atgl 
             ON atgl.BankAcountTransactionID = AT.BankAcounttransactionID
  INNER JOIN CustomerTransactionGroup tg 
             ON tg.ID = atgl.TransactionGroupID 
  WHERE AT.BankAccountID = '012083191715294f957d06e-76ad-485a-ad0e-e115a1e479fe'
  GROUP BY tg.GroupName,
           AT.IsDebit
  ORDER BY tg.GroupName
  </code></pre>