SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Get a list of Dates Between two Dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chaaru_akilan
Starting Member

India
16 Posts

Posted - 10/08/2012 :  07:55:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/08/2012 :  07:57:07  Show Profile  Reply with Quote
Do you need to exclude Saturdays, Sundays, and Holidays when you expand? Or are those included in the LeaveCount?
Go to Top of Page

chaaru_akilan
Starting Member

India
16 Posts

Posted - 10/08/2012 :  08:01:03  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/08/2012 :  08:04:30  Show Profile  Reply with Quote
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.
Go to Top of Page

chaaru_akilan
Starting Member

India
16 Posts

Posted - 10/08/2012 :  08:28:34  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/08/2012 :  08:38:43  Show Profile  Reply with Quote
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)
Go to Top of Page

chaaru_akilan
Starting Member

India
16 Posts

Posted - 10/08/2012 :  09:18:25  Show Profile  Reply with Quote
Perfect, Thank you so much.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/08/2012 :  09:20:43  Show Profile  Reply with Quote
Quite welcome, glad to help.
Go to Top of Page

chaaru_akilan
Starting Member

India
16 Posts

Posted - 10/11/2012 :  07:18:58  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/11/2012 :  07:48:11  Show Profile  Reply with Quote
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)
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/11/2012 :  07:51:00  Show Profile  Reply with Quote
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)
Go to Top of Page

chaaru_akilan
Starting Member

India
16 Posts

Posted - 10/11/2012 :  08:00:15  Show Profile  Reply with Quote
Thanks a ton. It works.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000