Month-Wise Report.

Hi All,
I have a table with the following structure
EMP_ID VARCHAR(5)
START_DATE DATETIME
END_DATE DATETIME
NO_OF_DAYS INT
This table tracks the leave taken by employees.
Say, I have a record like this
emp_id start_date end_date no_of_days
---
10005 Jan 26, 2005 Feb 02, 2005 8 (total
number of days on leave inclusive of start_date and end_date)
I want a report like this
10005 Jan - 6 Days Feb - 2 Days
Is there any way I can split a date range like this using SQL?
Thanks in advance...
Raju JosephTry this and let me know if it does what you require:
-- BEGIN SCRIPT
declare @.tbl table
(emp_id varchar(5)
, start_date datetime
, end_date datetime
, no_of_days int
)
insert into @.tbl
values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
insert into @.tbl
values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
select emp_id
, substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
day(start_date)+1) vacation
, substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
day(end_date))
, no_of_days
from @.tbl
where month(start_date)< month(end_date)
-- END SCRIPT
"Raju Joseph" wrote:

> Hi All,
> I have a table with the following structure
> EMP_ID VARCHAR(5)
> START_DATE DATETIME
> END_DATE DATETIME
> NO_OF_DAYS INT
> This table tracks the leave taken by employees.
> Say, I have a record like this
> emp_id start_date end_date no_of_day
s
> ---
> 10005 Jan 26, 2005 Feb 02, 2005 8 (total
> number of days on leave inclusive of start_date and end_date)
> I want a report like this
> 10005 Jan - 6 Days Feb - 2 Days
> Is there any way I can split a date range like this using SQL?
> Thanks in advance...
> Raju Joseph
>
>|||That's what I need.
Thanks a lot Valdez
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:0F266F38-6CCA-4654-9CB4-1E28C75D653E@.microsoft.com...
> Try this and let me know if it does what you require:
> -- BEGIN SCRIPT
> declare @.tbl table
> (emp_id varchar(5)
> , start_date datetime
> , end_date datetime
> , no_of_days int
> )
> insert into @.tbl
> values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
> insert into @.tbl
> values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
> select emp_id
> , substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
> day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
> day(start_date)+1) vacation
> , substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
> day(end_date))
> , no_of_days
> from @.tbl
> where month(start_date)< month(end_date)
> -- END SCRIPT
> "Raju Joseph" wrote:
>|||Hi Valdez,
Is there any way I can make the sql more generic
ie. if an employee has been on leave for say more than 2 months (Jun 23,
2005 to Aug 5, 2005), I would like all the 3 months to show up. Can I do
this using just a single sql?
Thanks in advance
Raju Joseph
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:0F266F38-6CCA-4654-9CB4-1E28C75D653E@.microsoft.com...
> Try this and let me know if it does what you require:
> -- BEGIN SCRIPT
> declare @.tbl table
> (emp_id varchar(5)
> , start_date datetime
> , end_date datetime
> , no_of_days int
> )
> insert into @.tbl
> values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
> insert into @.tbl
> values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
> select emp_id
> , substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
> day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
> day(start_date)+1) vacation
> , substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
> day(end_date))
> , no_of_days
> from @.tbl
> where month(start_date)< month(end_date)
> -- END SCRIPT
> "Raju Joseph" wrote:
>|||Hi There,
You can have one calendar table which will store all the dates from say
'1-jan-2000' to '31-dec-2009'
then your query would become as easy as this
Select Month(caldate),Year(caldate),count(*) Days From Calendar C ,
(Select * from yourtable where empid=@.empid) EMP Where caldate between
EMP.startdate and
emp.enddate group by Year(caldate),Month(caldate)
With Warm regards
Jatinder Singh|||Sorry to ask again.
Any way to display the result like this
emp_id Jan Feb Mar Apr
.... Dec
----
--
101 2 1
102 3
.
.
.
Thanks in advance...
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:0F266F38-6CCA-4654-9CB4-1E28C75D653E@.microsoft.com...
> Try this and let me know if it does what you require:
> -- BEGIN SCRIPT
> declare @.tbl table
> (emp_id varchar(5)
> , start_date datetime
> , end_date datetime
> , no_of_days int
> )
> insert into @.tbl
> values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
> insert into @.tbl
> values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
> select emp_id
> , substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
> day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
> day(start_date)+1) vacation
> , substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
> day(end_date))
> , no_of_days
> from @.tbl
> where month(start_date)< month(end_date)
> -- END SCRIPT
> "Raju Joseph" wrote:
>|||Hi there,
I am bit worried abut the leave extending from one year to another like
'31-Dec' to '4-Jan' .How would you like to show this '
If it is OK to ignore Year then You can try this .
Select empid,
Count(Case When Month(caldate) = 1 Then 1 else null ENd ) Jan,
Count(Case When Month(caldate) = 2 Then 1 else null ENd ) Feb,... And
SoON
>From Emp,calendar where caldate between startdate and enddate
Group by empid;
With Warm regards
Jatinder Singh|||Thanks a lot Jatinder and Valdez...
Coola Boola...
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1144239010.621694.145890@.u72g2000cwu.googlegroups.com...
> Hi there,
> I am bit worried abut the leave extending from one year to another like
> '31-Dec' to '4-Jan' .How would you like to show this '
> If it is OK to ignore Year then You can try this .
> Select empid,
> Count(Case When Month(caldate) = 1 Then 1 else null ENd ) Jan,
> Count(Case When Month(caldate) = 2 Then 1 else null ENd ) Feb,... And
> SoON
> Group by empid;
> With Warm regards
> Jatinder Singh
>