Get a list of Dates Between two Dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get a list of Dates Between two Dates

Author

Topic

chaaru_akilan
Starting Member

16 Posts

Posted - 2012-10-08 : 07:55:58

Hi,
I have a table as follows :
Leave table
UserId,FromDate,ToDate,LeavCount

USerTable
UserID,UserName.

I need a query which returs the users with their leave details.
UserName,LeaveCount,AppliedDates.

I am able to get the UserName and Leave Count, But am unable to expand the dates between from date and Todate.

Thanks in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-08 : 07:57:07

Do you need to exclude Saturdays, Sundays, and Holidays when you expand? Or are those included in the LeaveCount?

chaaru_akilan
Starting Member

16 Posts

Posted - 2012-10-08 : 08:01:03

I have done all the check and validations already, I just need to expand th dates.
Hope this helps:
Name Count Leave Applied Dates
Dinesh 10 01/30/2012, 02/20/2012, 03/16/2012, 03/17/2012, 03/18/2012, 03/19/2012, 03/20/2012, 04/30/2012, 05/17/2012

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-08 : 08:04:30

quote:
Name Count Leave Applied Dates
Dinesh 10 01/30/2012, 02/20/2012, 03/16/2012, 03/17/2012, 03/18/2012, 03/19/2012, 03/20/2012, 04/30/2012, 05/17/2012

Can you also post the input data that resulted in this output? From your initial post, I thought the fromdate and todate indicate a date range and you wanted to expand the range. But the sample output data doesn't seem to indicate that.

chaaru_akilan
Starting Member

16 Posts

Posted - 2012-10-08 : 08:28:34

There is no input data, I am just trying to querying this for a report purpose,
Leave Table:
UserId, FromDate, ToDate, LeavCount
4 02/27/2012 02/29/2012 3
4 04/03/2012 04/03/2012 1
4 05/16/2012 05/18/2012 3

User Table:
UserId UserName
4 Dinesh

Output
Name LeaveCount Applied Dates
Dinesh 7 02/27/2012, 02/28/2012, 02/29/2012, 04/03/2012, 05/16/2012, 05/17/2012, 05/18/2012

Hope this helps,
Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-08 : 08:38:43

It is easiest to do this if you have a numbers table in your database. If you don't have one, create one for now like this:

CREATE TABLE #N(n INT NOT NULL PRIMARY KEY CLUSTERED);

;WITH cte AS
(
	SELECT 1 AS n UNION ALL SELECT n+1 FROM cte WHERE n < 100
)
INSERT INTO #N SELECT * FROM cte;

Then, you can expand the dates like shown below, where I am creating a temporary test table just for testing purposes. In your case, you wouldn't need to create the temp table - you would use the real table that you have:

CREATE TABLE #tmp (
UserId INT , FromDate DATETIME, ToDate DATETIME, LeavCount INT )
INSERT INTO #tmp VALUES 
(4,'02/27/2012','02/29/2012', 3),
(4,'04/03/2012','04/03/2012',1),
(4,'05/16/2012','05/18/2012',3)

SELECT
	UserId,
	DATEADD(dd,n-1,FromDate)
FROM
	#tmp t
	INNER JOIN #N n
		ON n <= 1+DATEDIFF(dd,FromDate,Todate);

Once you have the expanded dates, you can concatenate them like shown below, where I am using the query above as a cte.

;WITH cte AS
(
SELECT
	UserId,
	DATEADD(dd,n-1,FromDate) AS VacationDate
FROM
	#tmp t
	INNER JOIN #N n
		ON n <= 1+DATEDIFF(dd,FromDate,Todate)
)
SELECT
	a.UserId,
	STUFF(b.Dates,1,1,'') AS Dates
FROM
	(SELECT DISTINCT userId FROM cte) AS  a
	CROSS APPLY
	(
		SELECT ','+CONVERT(VARCHAR(10),VacationDate,101)
		FROM cte b
		WHERE b.UserId = a.UserId
		FOR XML PATH('')
	) b(Dates)

chaaru_akilan
Starting Member

16 Posts

Posted - 2012-10-08 : 09:18:25

Perfect, Thank you so much.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-08 : 09:20:43

Quite welcome, glad to help.

chaaru_akilan
Starting Member

16 Posts

Posted - 2012-10-11 : 07:18:58

Hi, I need to exclude Saturday,sundays and holidays also for the above CTE query.Sorry dint look at it earlier. I tried writing a function separetely for it but it dint workk. Please Help.

Thanks in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-11 : 07:48:11

Do you have a calendar table with the holidays listed? That is the best (and perhaps the only reliable) way to exclude holidays. If you just want to exclude Saturdays and Sundays, that is easy - make the change shown in red below:

;WITH cte AS
(
SELECT
	UserId,
	DATEADD(dd,n-1,FromDate) AS VacationDate
FROM
	#tmp t
	INNER JOIN #N n
		ON n <= 1+DATEDIFF(dd,FromDate,Todate)
)
SELECT
	a.UserId,
	STUFF(b.Dates,1,1,'') AS Dates
FROM
	(SELECT DISTINCT userId FROM cte) AS  a
	CROSS APPLY
	(
		SELECT ','+CONVERT(VARCHAR(10),VacationDate,101)
		FROM cte b
		WHERE b.UserId = a.UserId
		AND DATEDIFF(dd,0,b.VacationDate)%7 NOT IN (5,6)
		FOR XML PATH('')
	) b(Dates)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-11 : 07:51:00

If you don't have a calendar table with the holidays, create one like shown below:

CREATE TABLE #HolidayCalendar(Dt Date);
INSERT INTO #HolidayCalendar VALUES
('20120102'),('20120116'),('20120220'),
('20120406'),('20120528'),('20120704'),
('20120903'),('20121122'),('20121225');

Then, make the change shown in red below:

....
	CROSS APPLY
	(
		SELECT ','+CONVERT(VARCHAR(10),VacationDate,101)
		FROM cte b
		WHERE b.UserId = a.UserId
		AND DATEDIFF(dd,0,b.VacationDate)%7 NOT IN (5,6)
		AND b.VacationDate NOT IN (SELECT Dt FROM #HolidayCalendar)
		FOR XML PATH('')
	) b(Dates)

chaaru_akilan
Starting Member

16 Posts

Posted - 2012-10-11 : 08:00:15

Thanks a ton. It works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources