title: SQL How to count all remains for each date
body: <p>I have the following SQL function</p>
  
  <pre><code>CREATE FUNCTION [dbo].[GetCardDepartRemains] 
  (
      @CardId INT,
      @DepartId INT,
      @Date DATETIME = NULL,
      @DocumentId INT = NULL
  )
  RETURNS INT
  AS
  BEGIN
      DECLARE @Res INT
  
      SELECT
          @Res = ISNULL(SUM(CASE WHEN Operations.[Output] = 0 AND Operations.RecipientId = @DepartId THEN 1 ELSE -1 END), 0)
      FROM dbo.Operations
      WHERE  Operations.CardId = @CardId
          AND (Operations.[Output] = 0 AND Operations.RecipientId = @DepartId OR Operations.Input = 0 AND Operations.SenderId = @DepartId)
          AND (@Date IS NULL OR Operations.[Date] &lt;= @Date)
  
      RETURN @Res
  END
  GO
  </code></pre>
  
  <p>It counts remains for certain product on certain department on certain date.</p>
  
  <p>If it is less then zero it means something's wrong with database</p>
  
  <p>Now I need to find all remains for each card, for each department for all dates in database where result is wrong.</p>
  
  <p>Theoretically speaking we can fing this by calling this procedure in a query like this </p>
  
  <pre><code>SELECT DISTINCT Operations.[Date] as [Date],
          Departments.Id as Depart,
          Cards.Id as [Card],
          [dbo].[GetCardDepartRemains] (Cards.Id, Departments.Id,Operations.[Date],NULL) as Res
  
  FROM [jewellery].[dbo].[Cards] 
      CROSS JOIN [jewellery].[dbo].[Departments] 
      CROSS JOIN [jewellery].[dbo].[Operations]
  WHERE [dbo].[GetCardDepartRemains] (Cards.Id, Departments.Id,Operations.[Date],NULL) = -1  
  </code></pre>
  
  <p>But this query executes more than 2 minutes, so we need to write a new query.</p>
  
  <p>My query can find all remains for each card on each department on certain date (ex. '2016-10-04')</p>
  
  <pre><code>SELECT 
      [Card],
      Depart,
      Res
  FROM          
      (SELECT
          Cards.Id as [Card],
          Departments.Id as Depart,
          ISNULL(SUM(CASE WHEN Operations.[Output] = 0 AND Operations.RecipientId = Departments.Id THEN 1 ELSE -1 END), 0) as Res
      FROM Operations
      CROSS JOIN Cards
      CROSS JOIN Departments
      WHERE  Operations.CardId = Cards.Id
      AND (Operations.[Output] = 0 AND Operations.RecipientId = Departments.Id OR Operations.Input = 0 AND Operations.SenderId = Departments.Id)
      AND (Operations.[Date] &lt;= '2016-10-04') 
  
      GROUP BY Cards.Id, Departments.Id
      ) as X
  WHERE Res = -1
  </code></pre>
  
  <p>Can you help to re-write this query to find remains for all dates?</p>