title: Sum of days in specific when a specific row is present
body: <p>I have a table named dbo.build_process. Usually I need to sum the Step Time in Days grouped by Step and this is simple. So, now was requested to to the same but only when step &quot;2_Saw&quot; is available.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Product</th>
  <th>ProductCode</th>
  <th>OperatorName</th>
  <th>Step</th>
  <th>StepSequence</th>
  <th>StepDate</th>
  <th>StepTimeinDays</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>0_Start</td>
  <td>0</td>
  <td>02/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>1_Material</td>
  <td>1</td>
  <td>02/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>2_Saw</td>
  <td>2</td>
  <td>02/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>3_Build</td>
  <td>3</td>
  <td>03/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>4_Paint</td>
  <td>4</td>
  <td>04/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>5_Varnish</td>
  <td>5</td>
  <td>04/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>6_Packing</td>
  <td>6</td>
  <td>04/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>0_Start</td>
  <td>0</td>
  <td>02/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>1_Material</td>
  <td>1</td>
  <td>02/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>3_Build</td>
  <td>3</td>
  <td>02/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>4_Paint</td>
  <td>4</td>
  <td>02/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>5_Varnish</td>
  <td>5</td>
  <td>02/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>6_Packing</td>
  <td>6</td>
  <td>03/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>0_Start</td>
  <td>0</td>
  <td>03/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>1_Material</td>
  <td>1</td>
  <td>03/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>2_Saw</td>
  <td>2</td>
  <td>04/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>3_Build</td>
  <td>3</td>
  <td>04/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>4_Paint</td>
  <td>4</td>
  <td>05/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>0_Start</td>
  <td>0</td>
  <td>04/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>1_Material</td>
  <td>1</td>
  <td>04/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>3_Build</td>
  <td>3</td>
  <td>05/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>4_Paint</td>
  <td>4</td>
  <td>05/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>John Doe</td>
  <td>0_Start</td>
  <td>0</td>
  <td>04/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>John Doe</td>
  <td>1_Material</td>
  <td>1</td>
  <td>04/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>John Doe</td>
  <td>3_Build</td>
  <td>3</td>
  <td>05/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>John Doe</td>
  <td>4_Paint</td>
  <td>4</td>
  <td>05/05/2019</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>When I run the query</p>
  <pre><code>select 
      Step, 
      sum(StepTimeinDays) as SumofStepTimeinDays
  from 
      dbo.build_process
  group by 
      Step
  order by 
      Step asc
  </code></pre>
  <p>The return is:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Step</th>
  <th>SumofStepTimeinDays</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0_Start</td>
  <td>0</td>
  </tr>
  <tr>
  <td>1_Material</td>
  <td>3</td>
  </tr>
  <tr>
  <td>2_Saw</td>
  <td>1</td>
  </tr>
  <tr>
  <td>3_Build</td>
  <td>2</td>
  </tr>
  <tr>
  <td>4_Paint</td>
  <td>3</td>
  </tr>
  <tr>
  <td>5_Varnish</td>
  <td>1</td>
  </tr>
  <tr>
  <td>6_Packing</td>
  <td>2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I need to return the sum to Step Time in Days only for &quot;blocks&quot; where I have the Step 2_Saw:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Product</th>
  <th>ProductCode</th>
  <th>OperatorName</th>
  <th>Step</th>
  <th>StepSequence</th>
  <th>StepDate</th>
  <th>StepTimeinDays</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>0_Start</td>
  <td>0</td>
  <td>02/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>1_Material</td>
  <td>1</td>
  <td>02/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>2_Saw</td>
  <td>2</td>
  <td>02/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>3_Build</td>
  <td>3</td>
  <td>03/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>4_Paint</td>
  <td>4</td>
  <td>04/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>5_Varnish</td>
  <td>5</td>
  <td>04/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>6_Packing</td>
  <td>6</td>
  <td>04/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>0_Start</td>
  <td>0</td>
  <td>03/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>1_Material</td>
  <td>1</td>
  <td>03/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>2_Saw</td>
  <td>2</td>
  <td>04/05/2019</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>3_Build</td>
  <td>3</td>
  <td>04/05/2019</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>4_Paint</td>
  <td>4</td>
  <td>05/05/2019</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The expected result is:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Step</th>
  <th>Sum of StepTimeinDays</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0_Start</td>
  <td>0</td>
  </tr>
  <tr>
  <td>1_Material</td>
  <td>1</td>
  </tr>
  <tr>
  <td>2_Saw</td>
  <td>1</td>
  </tr>
  <tr>
  <td>3_Build</td>
  <td>2</td>
  </tr>
  <tr>
  <td>4_Paint</td>
  <td>1</td>
  </tr>
  <tr>
  <td>5_Varnish</td>
  <td>0</td>
  </tr>
  <tr>
  <td>6_Packing</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I tried to use over partition but couldn't get the expected result.</p>
  <pre><code>select 
      Step, 
      sum(StepTimeinDays) as SumofStepTimeinDays
  from(
      select 
          t.*, 
          row_number() over(partition by ProductCode, OperatorName, Step order by StepSequence asc)
      from 
          dbo.build_process
  ) as t
  group by 
      Step
  order by 
      Step asc
  </code></pre>
  <p>Below the DDL to create the table and populate in SQL Server:</p>
  <p>--Create the table</p>
  <pre><code>CREATE TABLE [dbo].[build_process](
      [Product] varchar(50) NOT NULL,
      [ProductCode] varchar(50) NOT NULL,
      [OperatorName] varchar(50) NOT NULL,
      [Step] varchar(15) NULL,
      [StepSequence] int NULL,
      [StepDate] date NULL,
      [StepTimeinDays] int NULL
  ) ON [PRIMARY]
  GO
  </code></pre>
  <p>--Populate the table</p>
  <pre><code>INSERT INTO [dbo].[build_process] VALUES
      ('Chair','Black_Chair','Joseph Smith','0_Start',0,'2019-05-02',0),
      ('Chair','Black_Chair','Joseph Smith','1_Material',1,'2019-05-02',0),
      ('Chair','Black_Chair','Joseph Smith','2_Saw',2,'2019-05-02',1),
      ('Chair','Black_Chair','Joseph Smith','3_Build',3,'2019-05-03',1),
      ('Chair','Black_Chair','Joseph Smith','4_Paint',4,'2019-05-04',0),
      ('Chair','Black_Chair','Joseph Smith','5_Varnish',5,'2019-05-04',0),
      ('Chair','Black_Chair','Joseph Smith','6_Packing',6,'2019-05-04',1),
      ('Table','Table_2350','John Doe','0_Start',0,'2019-05-02',0),
      ('Table','Table_2350','John Doe','1_Material',1,'2019-05-02',0),
      ('Table','Table_2350','John Doe','3_Build',3,'2019-05-02',0),
      ('Table','Table_2350','John Doe','4_Paint',4,'2019-05-02',0),
      ('Table','Table_2350','John Doe','5_Varnish',5,'2019-05-02',1),
      ('Table','Table_2350','John Doe','6_Packing',6,'2019-05-03',1),
      ('Chair','Black_Chair','Joseph Doe','0_Start',0,'2019-05-03',0),
      ('Chair','Black_Chair','Joseph Doe','1_Material',1,'2019-05-03',1),
      ('Chair','Black_Chair','Joseph Doe','2_Saw',2,'2019-05-04',0),
      ('Chair','Black_Chair','Joseph Doe','3_Build',3,'2019-05-04',1),
      ('Chair','Black_Chair','Joseph Doe','4_Paint',4,'2019-05-05',1),
      ('Chair','Yellow_Chair','Joseph Smith','0_Start',0,'2019-05-04',0),
      ('Chair','Yellow_Chair','Joseph Smith','1_Material',1,'2019-05-04',1),
      ('Chair','Yellow_Chair','Joseph Smith','3_Build',3,'2019-05-04',0),
      ('Chair','Yellow_Chair','Joseph Smith','4_Paint',4,'2019-05-05',1),
      ('Chair','Black_Chair','John Doe','0_Start',0,'2019-05-04',0),
      ('Chair','Black_Chair','John Doe','1_Material',1,'2019-05-04',1),
      ('Chair','Black_Chair','John Doe','3_Build',3,'2019-05-05',0),
      ('Chair','Black_Chair','John Doe','4_Paint',4,'2019-05-05',1)
  GO
  </code></pre>