April 30, 2009 at 7:09 am
Hi..
I have two tables Temployee_leave_application, Tleave_type as Below,
I need to get the leaves taken by an employee for an year month wise.
I use the below query to get sum of leaves for a year of a particular leave.
DECLARE @temployee_leave_application TABLE
(employee_nbr INT,
leave_type_nbr INT,
From_DT DATETIME,
To_DT DATETIME,
Days_NBR INT)
INSERT INTO @temployee_leave_application (Employee_NBR,
Leave_Type_NBR,FROM_DT,To_DT,Days_NBR)
SELECT 1,1,'03/04/2009','03/04/2009',1 UNION
SELECT 1,1,'03/05/2009','03/07/2009',3 UNION
SELECT 1,1,'11/17/2007','11/18/2007',2 UNION
SELECT 1,1,'04/30/2009','05/02/2009',3
DECLARE @TLeave_Type TABLE(
leave_type_NBR INT,
leave_Type_NM VARCHAR(50))
INSERT INTO @TLeave_Type (Leave_Type_NBR ,LEave_Type_NM)
SELECT 1,'Casual Leave'
SELECT ela.Employee_NBR
,lt.Leave_Type_NBR
, SUM(ela.Days_NBR) AS Total_Count
FROM @temployee_leave_application ela
INNER JOIN @TLeave_Type lt
ON ela.Leave_Type_NBR = lt.Leave_Type_NBR
WHERE lt.Leave_Type_NM = 'Casual Leave'
AND DATEPART(yy,ela.From_DT) = 2009
GROUP BY ela.Employee_NBR,lt.Leave_Type_NBR
I need the data in this format
employee_NBR Leave_Type_NBR Jan_Days_ Feb_Days Mar_Days Apr_Days ----- Dec_Days
How can i get the data in this format when the leave From_DT and To_DT belong to different months for a record. Please help me out. i am looking out for some help in this forum.
Thanks in Advance.
April 30, 2009 at 7:16 am
Please observe thelast record inserted in temployee_leave_application.
The From_DT value is 04/30/2009
and TO_DT Value in 02/05/2009
It means that Total Number of leaves in April will be 2
and for may it is 2
April 30, 2009 at 8:10 am
I haven't had a go myself, but it looks like what you need can be achieved using a table valued function, to which you pass a start and end date and it returns a thirteen column (Year, JanDays, FebDays, MarDays.....DecDays) result.
so...
dbo.ufn_MonthDays('2009-03-30', '2009-04-02')
should return
2009, 0, 0, 1, 2, 0, 0, 0, 0, 0, 0, 0, 0
and
dbo.ufn_MonthDays('2008-11-27', '2009-04-02')
should return
2008, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 4, 31
2009, 31, 28, 31, 2, 0, 0, 0, 0, 0, 0, 0, 0
then you can sum this joined to the employee and leave type and get the result you need. (I know you didn't specify the year, but it would probably be a good idea).
It's just a starter for ten, sorry I don't have the time here right now to actually write an example fn, I bet one won't be far behind though! 🙂
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
April 30, 2009 at 8:47 am
swathi.g (4/30/2009)
Please observe thelast record inserted in temployee_leave_application.The From_DT value is 04/30/2009
and TO_DT Value in 02/05/2009
It means that Total Number of leaves in April will be 2
and for may it is 2
April: 1; May: 2
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 30, 2009 at 9:08 am
How's this?
-- first build a small numbers table
declare @Numbers TABLE (Number int PRIMARY KEY CLUSTERED)
;with CTE AS
(
select ROW_NUMBER() OVER (ORDER BY c1.OBJECT_ID) RowNumber
from sys.columns c1, sys.columns c2
)
insert into @Numbers
select top 5000 RowNumber from CTE
-- build a common table expression of all dates from 01/01/2001
;with CTE AS
(
select DATEADD(dd, Number, '20010101') MyDate
from @Numbers
)
-- run the select against the CTE. Evaluate each date to see what month it is in.
select
employee_nbr,
leave_type_nbr,
[January] = SUM(case when month(CTE.MyDate) = 1 then 1 else 0 end),
[February] = SUM(case when month(CTE.MyDate) = 2 then 1 else 0 end),
[March] = SUM(case when month(CTE.MyDate) = 3 then 1 else 0 end),
[April] = SUM(case when month(CTE.MyDate) = 4 then 1 else 0 end),
[May] = SUM(case when month(CTE.MyDate) = 5 then 1 else 0 end),
[June] = SUM(case when month(CTE.MyDate) = 6 then 1 else 0 end),
[July] = SUM(case when month(CTE.MyDate) = 7 then 1 else 0 end),
[August] = SUM(case when month(CTE.MyDate) = 8 then 1 else 0 end),
[September] = SUM(case when month(CTE.MyDate) = 9 then 1 else 0 end),
[October] = SUM(case when month(CTE.MyDate) = 10 then 1 else 0 end),
[November] = SUM(case when month(CTE.MyDate) = 11 then 1 else 0 end),
[December] = SUM(case when month(CTE.MyDate) = 12 then 1 else 0 end)
from @temployee_leave_application tl
INNER JOIN CTE ON MyDate between tl.From_DT and tl.To_DT
where YEAR(tl.From_DT) = 2009
group by employee_nbr, leave_type_nbr
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 1, 2009 at 12:28 am
Thanks WayneS.. It worked Perfectly
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply