| Author |
 Topic  |
|
|
chaaru_akilan
Starting Member
India
15 Posts |
 Posted - 10/08/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/08/2012 : 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
India
15 Posts |
Posted - 10/08/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/08/2012 : 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
India
15 Posts |
Posted - 10/08/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/08/2012 : 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
India
15 Posts |
Posted - 10/08/2012 : 09:18:25
|
| Perfect, Thank you so much. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/08/2012 : 09:20:43
|
| Quite welcome, glad to help. |
|
|
|
chaaru_akilan
Starting Member
India
15 Posts |
Posted - 10/11/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/11/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/11/2012 : 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
India
15 Posts |
Posted - 10/11/2012 : 08:00:15
|
| Thanks a ton. It works. |
|
|
| |
Topic |
|
|
|
Get a list of Dates Between two Dates
