title: Translate Excel business logic to T-SQL
body: <p>I need to 'translate' some business logic from Excel to T-SQL, I'm having a hard time with it.</p>
  
  <p>It's about figures from gates that count how many customers go IN and OUT of the stores.</p>
  
  <p>All DATA you need is in the following table:</p>
  
  <pre><code>CREATE TABLE #ResultsTable 
  (
      Datum DATETIME,
      window CHAR(10),
      countersOUT INT,
      countersIN INT,
      RESULT INT
  )
  
  INSERT INTO #ResultsTable 
  VALUES ('20180104 08:30:00.000', '08:30', 0, 0, 0),
         ('20180104 09:00:00.000', '09:00', 2, 1, 1),
         ('20180104 09:30:00.000', '09:30', 1, 0, 2),
         ('20180104 10:00:00.000', '10:00', 25, 9, 18),
         ('20180104 10:30:00.000', '10:30', 45, 41, 22),
         ('20180104 11:00:00.000', '11:00', 38, 37, 23),
         ('20180104 11:30:00.000', '11:30', 50, 51, 22),
         ('20180104 12:00:00.000', '12:00', 21, 24, 19),
         ('20180104 12:30:00.000', '12:30', 12, 19, 12),
         ('20180104 13:00:00.000', '13:00', 25, 18, 19),
         ('20180104 13:30:00.000', '13:30', 35, 27, 27),
         ('20180104 14:00:00.000', '14:00', 81, 9, 52),
         ('20180104 14:30:00.000', '14:30', 113, 18, 70),
         ('20180104 15:00:00.000', '15:00', 116, 34, 71),
         ('20180104 15:30:00.000', '15:30', 123, 36, 54),
         ('20180104 16:00:00.000', '16:00', 127, 35, 50),
         ('20180104 16:30:00.000', '16:30', 103, 19, 47),
         ('20180104 17:00:00.000', '17:00', 79, 31, 27),
         ('20180104 17:30:00.000', '17:30', 50, 16, 26),
         ('20180104 18:00:00.000', '18:00', 28, 11, 17),
         ('20180104 18:30:00.000', '18:30', 16, 15, 2),
         ('20180104 19:00:00.000', '19:00', 0, 2, 0),
         ('20180104 19:30:00.000', '19:30', 0, 0, 0),
         ('20180104 20:00:00.000', '20:00', 0, 0, 0),
         ('20180104 20:30:00.000', '20:30', 0, 0, 0),
         ('20180104 21:00:00.000', '21:00', 0, 0, 0),
         ('20180104 21:30:00.000', '21:30', 0, 0, 0),
         ('20180104 22:00:00.000', '22:00', 0, 0, 0)
  
  select * from #ResultsTable
  </code></pre>
  
  <p>'RESULT' is the column that should be calculated, based on 'countersOUT and 'countersIN'.</p>
  
  <p>'countersOUT and 'countersIN' is the INPUT data you need for the calculation.</p>
  
  <p>The business user made a <strong>help column</strong> in Excel to make the calculation (column AA)
  .  .  .   .  .  .  .<em>From business point of view: this is the number of customers at the end of each half hour present in the shop.</em></p>
  
  <p><a href="https://i.stack.imgur.com/NBJzO.png" rel="noreferrer"><img src="https://i.stack.imgur.com/NBJzO.png" alt="enter image description here"></a></p>
  
  <p>Then, the <strong>actual calculation</strong> below:   (screenshot also from Excel)</p>
  
  <p>Note: the calculation makes use of the help column (col. AA)
  <a href="https://i.stack.imgur.com/aTfow.png" rel="noreferrer"><img src="https://i.stack.imgur.com/aTfow.png" alt="enter image description here"></a></p>
  
  <p>Now my task is to make over this calculation in T-SQL.</p>
  
  <p>The business user its only input data is 'countersIN' and 'countersOUT', in other words it should be achievable in T-SQL. Only, I don't manage, that's why I came to ask my question.</p>
  
  <p>Lastly, I can advise to have a look at the Excel file (mediafire link) <a href="http://www.mediafire.com/file/mtdvlgmmbj3f8dd/Example_20190725_SQLforum.xlsx/file" rel="noreferrer">http://www.mediafire.com/file/mtdvlgmmbj3f8dd/Example_20190725_SQLforum.xlsx/file</a></p>
  
  <p>Thanks a lot in advance for any help</p>