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 

Missing 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 need data from two tables, as shown below:

 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 accept the date 2015/1/1 , because the date 2015/1/2 missing for the employee identifier '1' For the joy table in the date table
Point 2: I will not accept the date "2015/1/1" because the dates "2015/1/3" and "2015/1/2" are not available for employee identifier "2" for the monitoring date in the table.

I tried a problem like this, it worked fine for my problem, but it took two big runtimes for big data. How can I do this in a different way to get the minimum runtime.

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

Code (modified modification of the Giorgi Nakeuri 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, XD 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 ad >= c.sd AND ad <= c.ed ORDER BY d desc FOR XML PATH('') , TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') ) AS Date FROM cte c JOIN @ee ON e.ID = c.ID WHERE rn = 1 

Answer 2

Here is a demo. It uses some solution on the island, and then an XML query method to concatenate strings into 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 @aa WHERE a.ID = c.ID AND ad >= c.sd AND ad <= c.ed ORDER BY d desc FOR XML PATH('') , TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') ) AS Date FROM cte c JOIN @ee ON e.ID = c.ID WHERE rn = 1 

Output:

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

Answer 3

Your [hello] function queries the database several times in one run. My suggestion is to eliminate this. Create a temporary table to store dates between the start date and end date. And then use a loop or cursor (or something else) to compute the string with all the dates you need. As a result, you will query your database once inside the hello function and increase performance