title: Get ReAssignment time in days for employee on SQL Server
body: <p>I have an assignment table and I need to calculate the reassignment time in days for an employee once their old job has been completed. </p>
  
  <p>Sample data here:</p>
  
  <pre><code>DROP TABLE #temptable
  
  CREATE TABLE #temptable 
  (
       [EmployeeID] INT, 
       [CustomerID] VARCHAR(255),
       [Startdate] SMALLDATETIME,
       [Enddate] SMALLDATETIME, 
       [AssignmentID] VARCHAR(255) 
  )
  
  INSERT INTO #temptable
  VALUES
  ( 260, '75569', N'2019-06-17T00:00:00', N'2019-06-17T00:00:00', '1139597' ), 
  ( 365, '77234', N'2019-08-12T00:00:00', NULL, '1145495' ), 
  ( 365, '77234', N'2019-08-19T00:00:00', NULL, '1146052' ), 
  ( 531, '82224', N'2019-07-12T00:00:00', N'2019-07-16T00:00:00', '1142003' ), 
  ( 531, '73793', N'2019-07-07T00:00:00', N'2019-07-15T00:00:00', '1141124' ), 
  ( 567, '41965', N'2019-07-08T00:00:00', N'2019-07-08T00:00:00', '1141186' ), 
  ( 567, '73793', N'2019-07-05T00:00:00', N'2019-07-07T00:00:00', '1140546' ), 
  ( 626, '73720', N'2019-08-07T00:00:00', N'2019-08-12T00:00:00', '1144431' ), 
  ( 718, '94251', N'2019-07-18T00:00:00', N'2019-07-17T00:00:00', '1142617' ), 
  ( 718, '20116', N'2019-08-12T00:00:00', N'2019-08-12T00:00:00', '1144671' ), 
  ( 718, '20116', N'2019-07-18T00:00:00', N'2019-07-19T00:00:00', '1142808' ), 
  ( 759, '93619', N'2019-07-30T00:00:00', N'2019-07-30T00:00:00', '1143923' ), 
  ( 780, '89916', N'2019-07-15T00:00:00', N'2019-07-15T00:00:00', '1142101' ), 
  ( 780, '89916', N'2019-07-15T00:00:00', N'2019-07-25T00:00:00', '1142249' ), 
  ( 781, '65921', N'2019-09-16T00:00:00', N'2019-09-24T00:00:00', '1147642' ), 
  ( 933, '67299', N'2019-07-02T00:00:00', N'2019-07-02T00:00:00', '1140805' ), 
  ( 936, '93619', N'2019-07-21T00:00:00', N'2019-07-22T00:00:00', '1143013' ), 
  ( 1056, '71469', N'2019-09-25T00:00:00', NULL, '1148611' ), 
  ( 1114, '14617', N'2019-07-16T00:00:00', N'2019-07-25T00:00:00', '1143005' ), 
  ( 1262, '73793', N'2019-09-16T00:00:00', N'2019-09-16T00:00:00', '1147541' ), 
  ( 1262, '93626', N'2019-07-06T00:00:00', N'2019-07-07T00:00:00', '1141098' ), 
  ( 1262, '93626', N'2019-09-14T00:00:00', N'2019-09-14T00:00:00', '1147538' ), 
  ( 1920, '75569', N'2019-06-17T00:00:00', N'2019-06-26T00:00:00', '1139589' ), 
  ( 2019, '93626', N'2019-09-07T00:00:00', N'2019-09-07T00:00:00', '1147220' ), 
  ( 2248, '93626', N'2019-08-31T00:00:00', N'2019-09-01T00:00:00', '1146398' ), 
  ( 2248, '93626', N'2019-08-24T00:00:00', N'2019-08-25T00:00:00', '1145809' ), 
  ( 2248, '93626', N'2019-09-07T00:00:00', N'2019-09-08T00:00:00', '1146865' ), 
  ( 2248, '93626', N'2019-09-14T00:00:00', N'2019-09-15T00:00:00', '1147507' ), 
  ( 2772, '177', N'2019-07-08T00:00:00', N'2019-07-29T00:00:00', '1141670' ), 
  ( 2772, '146', N'2019-06-20T00:00:00', N'2019-06-30T00:00:00', '1139511' ), 
  ( 2772, '90730', N'2019-07-20T00:00:00', N'2019-07-27T00:00:00', '1142917' ), 
  ( 2772, '55154', N'2019-08-16T00:00:00', N'2019-08-16T00:00:00', '1145276' ), 
  ( 2772, '55154', N'2019-07-27T00:00:00', N'2019-07-27T00:00:00', '1143509' ), 
  ( 2772, '77234', N'2019-07-30T00:00:00', N'2019-08-11T00:00:00', '1144407' ), 
  ( 3026, '73793', N'2019-09-24T00:00:00', N'2019-09-24T00:00:00', '1148379' ), 
  ( 3026, '93626', N'2019-09-13T00:00:00', N'2019-09-15T00:00:00', '1147492' ), 
  ( 3158, '82228', N'2019-08-19T00:00:00', N'2019-08-22T00:00:00', '1145372' ), 
  ( 3158, '84283', N'2019-08-13T00:00:00', N'2019-08-16T00:00:00', '1144849' ), 
  ( 3422, '41965', N'2019-08-21T00:00:00', N'2019-09-17T00:00:00', '1145629' ), 
  ( 3666, '93626', N'2019-07-26T00:00:00', N'2019-07-28T00:00:00', '1143451' ), 
  ( 3666, '93626', N'2019-07-19T00:00:00', N'2019-07-21T00:00:00', '1142714' ),
  ( 100, '86985', N'2019-09-23T00:00:00', N'2019-09-26T00:00:00' , '11427d14' ), 
  ( 100, '86985', N'2019-09-23T00:00:00', N'2019-09-26T00:00:00'  ,'11427d14' )
  </code></pre>
  
  <p>I have used below script in order to get the days difference between the startdate and enddate. </p>
  
  <p><strong>Requirement</strong>: I need to get the re-assignment days of an employee on a job for different customer.</p>
  
  <pre><code>SELECT 
      EmployeeId,
      CustomerId,
      StartDate, EndDate,
      DATEDIFF(DAY, A, StartDate) AS duration
  FROM
      (SELECT 
           *,
           LAG(EndDate) OVER (PARTITION BY EmployeeId, CustomerId ORDER BY StartDate) AS A
       FROM #temptable
       WHERE EndDate IS NOT NULL) b
  ORDER BY 1 
  </code></pre>
  
  <p>Here in my sample data:</p>
  
  <ul>
  <li><p>for <code>EmployeeId = 3666</code> there are two assignments for different customers but I do not get the duration between the two assignments.</p></li>
  <li><p>for <code>EmployeeId = 100</code>, it's the same customer of the two assignments so I do not want to show the -3 value.</p></li>
  </ul>