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.
| Author |
Topic |
|
swathigardas
Posting Yak Master
149 Posts |
 Posted - 2009-04-30 : 07:40:08
|
| 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 UNIONSELECT 1,1,'03/05/2009','03/07/2009',3 UNIONSELECT 1,1,'11/17/2007','11/18/2007',2 UNIONSELECT 1,1,'04/30/2009','05/02/2009',3DECLARE @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_CountFROM @temployee_leave_application ela INNER JOIN @TLeave_Type lt ON ela.Leave_Type_NBR = lt.Leave_Type_NBRWHERE lt.Leave_Type_NM = 'Casual Leave'AND DATEPART(yy,ela.From_DT) = 2009GROUP BY ela.Employee_NBR,lt.Leave_Type_NBRI need the data in this formatemployee_NBR Leave_Type_NBR Jan_Days_ Feb_Days Mar_Days Apr_Days ----- Dec_DaysHow can i get the data in this format when the leave From_DT and To_DT belong to different months for a record.Thanks in Advance. |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-04-30 : 08:04:19
|
| is this u wantSELECT ela.Employee_NBR,lt.Leave_Type_NBR,sum(case when month(from_dt) = 1 then ela.Days_NBR end) as jan_days,sum(case when month(from_dt) = 2 then ela.Days_NBR end) as feb_days,sum(case when month(from_dt) = 3 then ela.Days_NBR end) as mar_days,sum(case when month(from_dt) = 4 then ela.Days_NBR end )as apr_days,sum(case when month(from_dt) = 5 then ela.Days_NBR end) as may_days,sum(case when month(from_dt) = 6 then ela.Days_NBR end) as jun_days,sum(case when month(from_dt) = 7 then ela.Days_NBR end) as july_days,sum(case when month(from_dt) = 8 then ela.Days_NBR end )as aug_days,sum(case when month(from_dt) = 9 then ela.Days_NBR end )as sep_days,sum(case when month(from_dt) = 10 then ela.Days_NBR end) as oct_days,sum(case when month(from_dt) = 11 then ela.Days_NBR end) as nov_days,sum(case when month(from_dt) = 12 then ela.Days_NBR end )as dec_daysFROM @temployee_leave_application ela INNER JOIN @TLeave_Type lt ON ela.Leave_Type_NBR = lt.Leave_Type_NBRWHERE lt.Leave_Type_NM = 'Casual Leave'AND DATEPART(yy,ela.From_DT) = 2009GROUP BY ela.Employee_NBR,lt.Leave_Type_NBR |
 |
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-04-30 : 08:11:11
|
| He has implemented the Cross-Tab query. otherwise u can use Pivot table concept. Moreover ur column having no of days leave taken not required in @temployee_leave_application. Iam a slow walker but i never walk back |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-04-30 : 08:15:59
|
| check this link toohttp://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx |
|
|
|
swathigardas
Posting Yak Master
149 Posts |
Posted - 2009-04-30 : 08:25:26
|
quote:
Originally posted by bklr
is this u wantSELECT ela.Employee_NBR,lt.Leave_Type_NBR,sum(case when month(from_dt) = 1 then ela.Days_NBR end) as jan_days,sum(case when month(from_dt) = 2 then ela.Days_NBR end) as feb_days,sum(case when month(from_dt) = 3 then ela.Days_NBR end) as mar_days,sum(case when month(from_dt) = 4 then ela.Days_NBR end )as apr_days,sum(case when month(from_dt) = 5 then ela.Days_NBR end) as may_days,sum(case when month(from_dt) = 6 then ela.Days_NBR end) as jun_days,sum(case when month(from_dt) = 7 then ela.Days_NBR end) as july_days,sum(case when month(from_dt) = 8 then ela.Days_NBR end )as aug_days,sum(case when month(from_dt) = 9 then ela.Days_NBR end )as sep_days,sum(case when month(from_dt) = 10 then ela.Days_NBR end) as oct_days,sum(case when month(from_dt) = 11 then ela.Days_NBR end) as nov_days,sum(case when month(from_dt) = 12 then ela.Days_NBR end )as dec_daysFROM @temployee_leave_application ela INNER JOIN @TLeave_Type lt ON ela.Leave_Type_NBR = lt.Leave_Type_NBRWHERE lt.Leave_Type_NM = 'Casual Leave'AND DATEPART(yy,ela.From_DT) = 2009GROUP BY ela.Employee_NBR,lt.Leave_Type_NBR
Thanks for u r solution. But if u see the last record inserted in the temployee_leave_application table the fromDT value is 04/30/2009To_DT - 05/02/2009which means that one leave in the month of april and two leaves in the month of may.i need to check with the to_dt also . |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-04-30 : 23:02:41
|
| try this one swathiSELECT ela.Employee_NBR,lt.Leave_Type_NBR,sum(case when month(from_dt) = 1 then bal when month(to_dt)= 1 then diff end) as jan_days,sum(case when month(from_dt) = 2 then bal when month(to_dt)= 2 then diff end) as feb_days,sum(case when month(from_dt) = 3 then bal when month(to_dt)= 3 then diff end) as mar_days,sum(case when month(from_dt) = 4 then bal when month(to_dt)= 4 then diff end )as apr_days,sum(case when month(from_dt) = 5 then bal when month(to_dt)= 5 then diff end) as may_days,sum(case when month(from_dt) = 6 then bal when month(to_dt)= 6 then diff end) as jun_days,sum(case when month(from_dt) = 7 then bal when month(to_dt)= 7 then diff end) as july_days,sum(case when month(from_dt) = 8 then bal when month(to_dt)= 8 then diff end )as aug_days,sum(case when month(from_dt) = 9 then bal when month(to_dt)= 9 then diff end )as sep_days,sum(case when month(from_dt) = 10 then bal when month(to_dt)=10 then diff end) as oct_days,sum(case when month(from_dt) = 11 then bal when month(to_dt)=11 then diff end) as nov_days,sum(case when month(from_dt) = 12 then bal when month(to_dt)=12 then diff end )as dec_daysFROM (select *,days_nbr-isnull(diff,0) as bal from (select *,case when month(from_dt) <> month(to_dt) then datediff(d,from_dt,to_dt) end as diff from @temployee_leave_application )s) ela INNER JOIN @TLeave_Type lt ON ela.Leave_Type_NBR = lt.Leave_Type_NBRWHERE lt.Leave_Type_NM = 'Casual Leave'AND DATEPART(yy,ela.From_DT) = 2009GROUP BY ela.Employee_NBR,lt.Leave_Type_NBR |
|
|
|
swathigardas
Posting Yak Master
149 Posts |
Posted - 2009-05-01 : 02:34:29
|
quote:
Originally posted by bklr
try this one swathiSELECT ela.Employee_NBR,lt.Leave_Type_NBR,sum(case when month(from_dt) = 1 then bal when month(to_dt)= 1 then diff end) as jan_days,sum(case when month(from_dt) = 2 then bal when month(to_dt)= 2 then diff end) as feb_days,sum(case when month(from_dt) = 3 then bal when month(to_dt)= 3 then diff end) as mar_days,sum(case when month(from_dt) = 4 then bal when month(to_dt)= 4 then diff end )as apr_days,sum(case when month(from_dt) = 5 then bal when month(to_dt)= 5 then diff end) as may_days,sum(case when month(from_dt) = 6 then bal when month(to_dt)= 6 then diff end) as jun_days,sum(case when month(from_dt) = 7 then bal when month(to_dt)= 7 then diff end) as july_days,sum(case when month(from_dt) = 8 then bal when month(to_dt)= 8 then diff end )as aug_days,sum(case when month(from_dt) = 9 then bal when month(to_dt)= 9 then diff end )as sep_days,sum(case when month(from_dt) = 10 then bal when month(to_dt)=10 then diff end) as oct_days,sum(case when month(from_dt) = 11 then bal when month(to_dt)=11 then diff end) as nov_days,sum(case when month(from_dt) = 12 then bal when month(to_dt)=12 then diff end )as dec_daysFROM (select *,days_nbr-isnull(diff,0) as bal from (select *,case when month(from_dt) <> month(to_dt) then datediff(d,from_dt,to_dt) end as diff from @temployee_leave_application )s) ela INNER JOIN @TLeave_Type lt ON ela.Leave_Type_NBR = lt.Leave_Type_NBRWHERE lt.Leave_Type_NM = 'Casual Leave'AND DATEPART(yy,ela.From_DT) = 2009GROUP BY ela.Employee_NBR,lt.Leave_Type_NBR
Thanks a Lot bklr.. It Worked perfectly for the above data.. But when i give the from_DT Value as '05/30/2009' and To_DT Value as '06/01/2009'. It gives me the value for may_days as 1 where as May has days 30,31 which makes th count as 2.I dint know how to modify the code. Can u Please help me out. |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-05-01 : 03:20:53
|
| [code]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 UNIONSELECT 1,1,'03/05/2009','03/07/2009',3 UNIONSELECT 1,1,'11/17/2007','11/18/2007',2 UNIONSELECT 1,1,'9/30/2009','10/4/2009',5 UNIONSELECT 1,1,'5/30/2009','6/1/2009',3 UNIONSELECT 1,1,'5/11/2009','5/15/2009',5 UNIONSELECT 1,1,'4/29/2009','5/1/2009',3DECLARE @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(case when month(from_dt) = 1 AND diff IS NOT NULL then diff when month(to_dt) = 1 then bal end) as jan_days,sum(case when month(from_dt) = 2 AND diff IS NOT NULL then diff when month(to_dt) = 2 then bal end) as feb_days,sum(case when month(from_dt) = 3 AND diff IS NOT NULL then diff when month(to_dt) = 3 then bal end) as mar_days,sum(case when month(from_dt) = 4 AND diff IS NOT NULL then diff when month(to_dt) = 4 then bal end )as apr_days,sum(case when month(from_dt) = 5 AND diff IS NOT NULL then diff when month(to_dt) = 5 then bal end) as may_days,sum(case when month(from_dt) = 6 AND diff IS NOT NULL then diff when month(to_dt) = 6 then bal end) as jun_days,sum(case when month(from_dt) = 7 AND diff IS NOT NULL then diff when month(to_dt) = 7 then bal end) as july_days,sum(case when month(from_dt) = 8 AND diff IS NOT NULL then diff when month(to_dt) = 8 then bal end )as aug_days,sum(case when month(from_dt) = 9 AND diff IS NOT NULL then diff when month(to_dt) = 9 then bal end )as sep_days,sum(case when month(from_dt) = 10 AND diff IS NOT NULL then diff when month(to_dt) = 10 then bal end) as oct_days,sum(case when month(from_dt) = 11 AND diff IS NOT NULL then diff when month(to_dt) =11 then bal end) as nov_days,sum(case when month(from_dt) = 12 AND diff IS NOT NULL then diff when month(to_dt) =12 then bal end )as dec_daysFROM (select *,days_nbr-isnull(diff,0) as bal from (select *,case when month(from_dt) <> month(to_dt) then datediff(d,from_dt,to_dt) end as diff from @temployee_leave_application )s) ela INNER JOIN @TLeave_Type lt ON ela.Leave_Type_NBR = lt.Leave_Type_NBRWHERE lt.Leave_Type_NM = 'Casual Leave'AND DATEPART(yy,ela.From_DT) = 2009GROUP BY ela.Employee_NBR,lt.Leave_Type_NBR[/code] |
|
|
|
swathigardas
Posting Yak Master
149 Posts |
Posted - 2009-05-01 : 04:23:08
|
Wow.. That Looks simply superb Solution.... thanks a Lot bklr..  |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-05-01 : 05:21:48
|
quote:
Originally posted by swathigardas
Wow.. That Looks simply superb Solution.... thanks a Lot bklr..
ur welcome |
|
|
|
|
|
|
|
|
Need Help in Query..