Getting day wise record using data leave start date between end date

Question

1.00/5 (1 vote)

See more:

My Table Record

Employee Id	Start Date	EndDate	Text
1	2019-09-01	2019-09-03	Demo1 Text
2	2019-09-04	2019-09-04	Demo2 Text
3	2019-09-05	2019-09-08	Demo3 Text

#Result

Employee Id	Date	Text
1	2019-09-01	Demo1 Text
1	2019-09-02	Demo1 Text
1	2019-09-03	Demo1 Text
2	2019-09-04	Demo2 Text
3	2019-09-05	Demo3 Text
3	2019-09-06	Demo3 Text
3	2019-09-07	Demo3 Text
3	2019-09-08	Demo3 Text

What I have tried:

SQL

CREATE TABLE #EmployeeAttandance
(
	Employee_Id int,
	StartDate datetime,
	EndDate datetime,
	Text VARCHAR (50)
)

Insert Into #EmployeeAttandance Values(1,'2019-09-01', '2019-09-03','Demo1 Text')
Insert Into #EmployeeAttandance Values(2, '2019-09-04', '2019-09-04', 'Demo2 Text')
Insert Into #EmployeeAttandance Values(3, '2019-09-05', '2019-09-08', 'Demo3 Text')

select * from #EmployeeAttandance

drop table #EmployeeAttandance

Posted 17-Sep-19 2:17am

Member 11322120

Updated 17-Sep-19 22:53pm

ChauhanAjay

v2

Add a Solution

Comments

Maciej Los 17-Sep-19 11:10am

DO you have any question?

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2019-09-17T22:54:00

I'd suggest to use CTE[^]. See:

SQL

DECLARE @tmp TABLE(Employee_Id int, StartDate datetime, EndDate datetime, [Text] VARCHAR (50)) 

INSERT INTO @tmp(Employee_Id, StartDate, EndDate, [Text])
VALUES(1, '2019-09-01', '2019-09-03', 'Demo1 Text'),
(2, '2019-09-04', '2019-09-04', 'Demo2 Text'),
(3, '2019-09-05', '2019-09-08', 'Demo3 Text')

;WITH CTE AS
(
	--initial part
	SELECT Employee_Id, 1 AS DayNo, StartDate, EndDate, [Text]
	FROM @tmp
	--recursive part
	UNION ALL
	SELECT Employee_Id, DayNo + 1 AS DayNo, DATEADD(DD, 1, StartDate) AS StartDate, EndDate, [Text]
	FROM CTE
	WHERE DATEADD(DD, 1, StartDate)<=EndDate
)
SELECT *
FROM CTE
ORDER BY Employee_Id, DayNo

For further details, please see:
WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs[^]
Common Table Expressions (Introduction to CTE's) - Essential SQL[^]
SQL Server CTE Basics - Simple Talk[^]
SQL Server Common Table Expressions (CTE)[^]

Getting day wise record using data leave start date between end date

1 solution

Solution 1

Add your solution here

Preview 0

Getting day wise record using data leave start date between end date

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us