title: Create a SQL query based Daily report with Incremental Records
body: <p>I have a requirement to create a crosstab using a database which loads only incremental records. Please consider the following sample, Table1:</p>
  
  <pre><code>DAY         ACCOUNT NO  SALES   GENDER
  1-Apr-2019  ACC01       5000    MALE
  1-Apr-2019  ACC02       6000    FEMALE
  1-Apr-2019  ACC03       7000    FEMALE
  1-Apr-2019  ACC04       8000    MALE
  2-Apr-2019  ACC01       7500    MALE
  2-Apr-2019  ACC02       4000    FEMALE
  2-Apr-2019  ACC03       10000   FEMALE
  2-Apr-2019  ACC04       20000   MALE
  3-Apr-2019  ACC01       8500    FEMALE
  3-Apr-2019  ACC05       3500    FEMALE
  4-Apr-2019  ACC02       9500    FEMALE
  4-Apr-2019  ACC06       3600    FEMALE
  4-Apr-2019  ACC07       4800    MALE
  5-Apr-2019  ACC01       15000   FEMALE
  5-Apr-2019  ACC03       12500   FEMALE
  </code></pre>
  
  <p>Table1 entries explained below:</p>
  
  <p>1st April there are 4 accounts with their sale nos and gender of the account holder.<br>
  On 2nd April, there is some change in sale nos for all the accounts and thus they have been loaded.<br>
  But on 3rd April, we only load incremental accounts. As can be seen, the sales of ONLY ACC01 has changed and also there is a new account ACC05. Thus only these 2 accounts will be loaded.<br>
  Again on 4th April, existing account ACC02 has an increase in sale from its previous value and thus it gets loaded. Along with this, there are 2 new accounts, ACC06 and ACC07 which also gets loaded.<br>
  Finally on 5th April, we load existing accounts which have change in sale nos from their last value.  </p>
  
  <p>Now, I have to do a Daily MIS reporting and the result should be as follows, considering All my Accounts are Active as on 5th April 2019.</p>
  
  <p><a href="https://i.stack.imgur.com/hqxbQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hqxbQ.png" alt="enter image description here"></a></p>
  
  <p>NO OF ACCOUNTS is actually the count of the Active Accounts at the end of each Day.</p>
  
  <p>The above list Report 1, I was able to achieve using the following sub-query using Cognos Query/SQL items in Report Studio:</p>
  
  <pre><code>SELECT  DISTINCT D1.DAY, (SELECT COUNT(DISTINCT D2.ACCN_NO) FROM
  DAILY_TABLE D2 WHERE D2.DAY &lt;= D1.DAY) AS NO_OF_ACCOUNTS FROM
  DAILY_TABLE D1 GROUP BY D1.DAY
  </code></pre>
  
  <p><strong><em>Now, my requirement is to generate the following crosstab with a cut on the Gender.</em></strong></p>
  
  <p><a href="https://i.stack.imgur.com/mtW0w.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mtW0w.png" alt="enter image description here"></a></p>
  
  <p>Best Regards</p>