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

Do you have non clustered index for table Absent which includes Date and EmployeeId fields ? If not , it can increase this query performance. — Disappointed
– Disappointed, Commented Mar 23, 2015 at 10:37
What will be result for Moni if the data is 2015/1/1 2 2015/1/4 2 2015/1/5 2 2015/1/8 2 2015/1/9 2? — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Mar 23, 2015 at 10:56
Thanks, [2015/1/9,2015/1/8 if enddate=2015/1/9] [2015/1/5,2015/1/4 if enddate=2015/1/5] @ Giorgi Nakeuri – — Joy Acharya
– Joy Acharya, Commented Mar 23, 2015 at 11:59

Giorgi Nakeuri · Accepted Answer · 2015-03-23 12:30:25Z

2

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

answered Mar 23, 2015 at 12:30

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joy Acharya Over a year ago

If the @ed do not match in Absent Table Then i want to break here. suppose in your solution @ed='20150106' then your output will be same.

Joy Acharya Over a year ago

if @ed do not match in absent table i will show null for particular employee. if @ed=20150205 then output will Joy 20150105,20150104,20150103 Moni 20150105,20150104 Evan 20150105 where your solution is correct. But if @ed=20150106 in this case i will not show anything for employee there is no date like '20150106' in absent table.

Giorgi Nakeuri Over a year ago

@JoyAcharya, can't edit my answer now. Look at this fiddle sqlfiddle.com/#!6/f4dae/9 Pay attention on cross apply.

Joy Acharya Over a year ago

Thanks, i have already solved the problem by modifying your code. The solution is bellow.

Joy Acharya · Accepted Answer · 2015-06-25 05:59:04Z

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

Disappointed · Accepted Answer · 2015-03-23 11:04:46Z

1

Your function [hello] query database a few times per one execution. My proposal is to eliminate it. Create temporary table for storing dates between start date and end date. And then use loop or cursor(or something else) to calculate string with all dates you need. As result you will query your database once inside function hello and increase performance

answered Mar 23, 2015 at 11:04

Disappointed

1,1201 gold badge9 silver badges24 bronze badges

2 Comments

Joy Acharya Over a year ago

Thanks,Now i have not right to create temporary table in my project. I will do it next.without temporary table is there any way?

Disappointed Over a year ago

@Joy Acharya . It is very strange ... I mean not temporary table in database, but temporary table variables. Example here [mssqltips.com/sqlservertutorial/2910/…. Please double check

Collectives™ on Stack Overflow

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

Employee Table

Absent table

CODE

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Employee Table

Absent table

CODE

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related