How to select multiple rows in one column with a given condition

Question

Employee Table

Id      Name
-------------
1        Joy
2       Moni
3       Evan
4      farhad

Absent table

Date(y/m/d)   Id
-----------------
2015/1/1      1
2015/1/3      1
2015/1/4      1
2015/1/5      1
2015/1/1      2
2015/1/4      2
2015/1/5      2
2015/1/5      3

I have need data from above two tables as like as bellow

Name              Date
Joy     2015/1/5, 2015/1/4, 2015/1/3    
Moni    2015/1/5, 2015/1/4
Evan    2015/1/5

Point 1: I will not take date 2015/1/1 because date 2015/1/2 is missing for employee id '1' For Joy in Date Table
Point 2: I will not take date '2015/1/1' because date '2015/1/3' and '2015/1/2' is missing for employee id '2' for moni in date Table

I have tried the problem like this bellow, this worked fine for my problem but its take two much execution time for big data. How can i do that another way so that i will get minimum execution time.

CODE

select a.Id,a.name , [dbo].[hello] ('2015/1/1','2015/1/5',a.Id)  From
Employee a

    ALTER FUNCTION [dbo].[hello](@start datetime,@End datetime,@Id int)
    returns varchar(1111)
AS

begin
declare
        @TempDate  DateTime,
        @CombainedDate  varchar(1111)= '',


        while(@End>=@start)

        begin


          select @ TempDate  = (select distinct Date from Absent  d where Date=@End and EmployeeId=@Id)

           if
            @ TempDate  = @End
            begin
           set @End = DATEADD(day, -1, @End)
           set @ CombainedDate  +=  ',' + cast(@TempDate  as varchar(1111)) 
          end
            else
            begin
            break
            end
           end
       return @ CombainedDate  
        end                                                                                           

Answer 1

Here is demo. It uses some island solution and then XML query technique for concatenating rows to one string:

DECLARE @sd DATE = '20150101' , @ed DATE = '20150105'

DECLARE @e TABLE
    (
      ID INT ,
      Name NVARCHAR(MAX)
    )
DECLARE @a TABLE ( ID INT, d DATETIME )

INSERT  INTO @e
VALUES  ( 1, 'Joy' ),
        ( 2, 'Moni' ),
        ( 3, 'Evan' ),
        ( 4, 'Farhad' )


INSERT  INTO @a
VALUES  ( 1, '20150101' ),
        ( 1, '20150103' ),
        ( 1, '20150104' ),
        ( 1, '20150105' ),
        ( 2, '20150101' ),
        ( 2, '20150104' ),
        ( 2, '20150105' ),
        ( 3, '20150105' );

WITH    cte
          AS ( SELECT   ID ,
                        sd = MIN(d) ,
                        ed = MAX(d) ,
                        ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY MAX(d)
                                            - MIN(d) DESC, MAX(d) DESC ) AS rn
               FROM     ( SELECT    ID ,
                                    CAST(d AS INT) AS d ,
                                    rn = CAST(d AS INT)
                                    - ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY d )
                          FROM      @a
                          WHERE     d >= @sd
                                    AND d <= @ed
                        ) a
               GROUP BY ID ,
                        rn
             )
    SELECT  e.Name ,
            ( SELECT    STUFF((SELECT   ',' + CONVERT(NVARCHAR(8), d, 112)
                               FROM     @a a WHERE a.ID = c.ID AND a.d >= c.sd AND a.d <= c.ed
                               ORDER BY d desc
                        FOR   XML PATH('') ,
                                  TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
            ) AS Date
    FROM    cte c
            JOIN @e e ON e.ID = c.ID
    WHERE   rn = 1

Output:

Name    Date
Joy     20150105,20150104,20150103
Moni    20150105,20150104
Evan    20150105

Answer 2

Code(Modifying Modifying Giorgi Nakeuri's solution)

DECLARE @sd DATETIME,
    @ed DATETIME 
SET @sd = '20150101' 
SET @ed = '20150106'

DECLARE @e TABLE
(
  ID INT ,
  Name NVARCHAR(MAX)
)

INSERT  INTO @e
  SELECT  1, 'Joy' 
UNION 
SELECT  2, 'Moni' 
UNION 
SELECT  3, 'Evan'
UNION 
SELECT 4, 'Farhad'




DECLARE @a TABLE ( ID INT, d DATETIME )

INSERT  INTO @a (ID, D)
        SELECT  1, '20150101'
        union
        SELECT  1, '20150103'
        union
        SELECT  1, '20150104'
        union
        SELECT  1, '20150105'
        union
        SELECT  2, '20150101'
        union
        SELECT  2, '20150104'
        union
        SELECT  2, '20150105'
        union
        SELECT  3, '20150105';

   DECLARE @T TABLE ( ID INT, d DATETIME )
   INSERT INTO @T(ID,d)
   SELECT X.ID, X.D FROM
  (SELECT ID,d FROM @a
   WHERE d BETWEEN @sd AND @ed) X
 INNER JOIN
(SELECT ID, d FROM @a
WHERE d = @ed) Y  ON X.ID=Y.ID;


WITH    cte
      AS ( SELECT   ID ,
                    sd = MIN(d) ,
                    ed = MAX(d) ,
                    ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY MAX(d)
                                        - MIN(d) DESC, MAX(d) DESC ) AS rn
           FROM     ( SELECT    ID ,
                                CAST(d AS INT) AS d ,
                                rn = CAST(d AS INT)
                                - ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY d )
                      FROM      @T
                      WHERE     d >= @sd
                                AND d <= @ed
                    ) a
           GROUP BY ID ,
                    rn
         )
SELECT  e.Name ,
        ( SELECT    STUFF((SELECT   ',' + CONVERT(NVARCHAR(8), d, 112)
                           FROM     @T a WHERE a.ID = c.ID AND a.d >= c.sd AND a.d <= c.ed
                           ORDER BY d desc
                    FOR   XML PATH('') ,
                              TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
        ) AS Date
FROM    cte c
        JOIN @e e ON e.ID = c.ID
WHERE   rn = 1