title: I want to get record between two dates for specific employee in a stored procedure
body: <p>I have two tables. The problem is the check out time is showing only one date data, I want multiple records</p>
  
  <pre><code>CREATE TABLE [dbo].[ras_AttRecord]
  (
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [DN] [smallint] NULL,
      [DIN] [bigint] NOT NULL,
      [Clock] [datetime] NOT NULL,
      [VerifyMode] [tinyint] NULL,
      [AttTypeId] [char](3) NOT NULL,
      [CollectDate] [datetime] NOT NULL,
      [LastUpdatedUID] [int] NULL,
      [LastUpdatedDate] [datetime] NULL,
      [Remark] [nvarchar](64) NULL
  )
  
  CREATE TABLE [dbo].[ras_Users]
  (
      [UID] [int] NOT NULL,
      [DIN] [bigint] NOT NULL,
      [PIN] [varchar](32) NOT NULL,
      [UserName] [nvarchar](64) NULL,
      [Sex] [char](1) NOT NULL,
      [Password] [nvarchar](32) NULL,
      [PasswordQuestion] [nvarchar](64) NULL,
      [PasswordAnswer] [nvarchar](32) NULL,
      [IsApproved] [bit] NOT NULL,
      [IsLockedOut] [bit] NOT NULL,
      [CreateDate] [datetime] NULL,
      [LastLoginDate] [datetime] NULL,
      [DeptId] [varchar](64) NOT NULL,
      [AttId] [char](4) NULL,
      [RuleId] [char](4) NULL,
      [WeekendId] [char](4) NULL,
      [LastUpdatedUID] [int] NULL,
      [LastUpdatedDate] [datetime] NOT NULL,
      [Comment] [nvarchar](128) NULL
  )
  </code></pre>
  
  <p>and I have this stored procedure:</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER  PROCEDURE [dbo].[GetLateComersmonthly]       
      @FromDate DATE = '05/22/2017', 
      @ToDate DATE = '06/06/2017',       
      @Code VARCHAR(6) = '3155'      
  AS       
  BEGIN        
      SET NOCOUNT ON;       
  
      IF @Code = '0'        
          SET @Code = NULL
  
      SELECT 
          T1.UserName, T1.DIN AS [DIN], 
          MIN(T1.Date) AS [Date], MIN(T1.Time) AS [Time], 
          CASE   
             WHEN MAX(T2.Time) = MAX(T1.Time) 
                THEN '' 
                ELSE MAX(T2.Time) 
          END AS [Timee],  
          MIN(T1.Day) AS [Day],
          CASE   
             WHEN CONCAT(CONVERT(VARCHAR, DATEDIFF(MINUTE, MIN(T1.Time), MAX(t2.Time)), 108) / 60, ':', CONVERT(VARCHAR, DATEDIFF(MINUTE, MIN(T1.Time), MAX(t2.Time)) % 60, 108)) = '0:0' 
                THEN 'No Check Out' 
                ELSE CONCAT(CONVERT(VARCHAR, DATEDIFF(MINUTE, MIN(T1.Time), MAX(t2.Time)), 108) / 60, ':', CONVERT(VARCHAR, DATEDIFF(MINUTE, MIN(T1.Time), MAX(t2.Time)) % 60, 108)) 
          END AS hrs
      FROM
          (SELECT 
               UserName, ru.DIN, 
               CONVERT(VARCHAR, Att.Clock, 101) AS Date, 
               CONVERT(VARCHAR, Att.Clock, 108) AS Time, 
               att.VerifyMode as Mode, 
               DATENAME(dw, Att.Clock) AS Day        
           FROM
               ras_Users AS ru        
           INNER JOIN
               ras_AttRecord AS att ON ru.DIN = att.Din        
           WHERE
               CONVERT(DATE, Clock) &gt;= @FromDate 
               AND CONVERT(DATE, Clock) &lt;= @ToDate 
               AND att.DIN = COALESCE (@Code, att.DIN)  
               AND att.VerifyMode = 15) as T1 
      LEFT JOIN
         (SELECT  
              ru.DIN, MAX(CONVERT(VARCHAR, Att.Clock, 108)) AS Time  
          FROM
              ras_Users as ru        
          INNER JOIN
              ras_AttRecord AS att ON ru.DIN = att.Din        
          WHERE
              CONVERT(DATE, Clock) &gt;= @FromDate 
              AND CONVERT(DATE, Clock) &lt;= @ToDate 
              AND att.DIN = COALESCE (@Code, att.din) 
          GROUP BY 
              ru.DIN, att.Clock) as  T2 ON T1.DIN = T2.DIN 
                               --and DATEDIFF(MINUTE,T1.Time,t2.Time)/60 &lt; 9
      GROUP BY
          T1.Username, T1.din, T1.Day      
      -- having CONVERT(varchar, min(T1.Time), 108) &gt; '09:30:00'   
      ORDER BY 
          T1.din
  END 
  </code></pre>
  
  <p>Current records displaying</p>
  
  <pre><code>Userid  Date        CheckIn     CheckOut    Day       total hours
      3155    05/26/2017  09:15:10    17:15:00    Friday    8:0
      3155    05/29/2017  09:08:36    17:15:00    Monday    8:7
      3155    05/25/2017  09:34:28    17:15:00    Thursday  7:41
      3155    05/30/2017  09:15:12    17:15:00    Tuesday   8:0
      3155    05/31/2017  09:27:37    17:15:00    Wednesday 7:48
  </code></pre>
  
  <p>The problem is the check out time is showing only one date data, I want multiple
  and the following</p>
  
  <p>Expected result</p>
  
  <pre><code>Userid  Date        CheckIn     CheckOut    Day       total hours
      3155    05/26/2017  09:15:10    17:00:00    Friday    8:0
      3155    05/29/2017  09:08:36    17:05:00    Monday    8:7
      3155    05/25/2017  09:34:28    17:30:00    Thursday  7:41
      3155    05/30/2017  09:15:12    17:18:00    Tuesday   8:0
      3155    05/31/2017  09:27:37    17:10:00    Wednesday 7:48
  </code></pre>