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
>

Money to Varchar

From query analyzer how can I change the field datatype from money to varchar?
Alter table tablenaame alter column columnname varchar(30)--Is not working.Can you describe "not working" in a bit more detail? Does the command appear to run, but the column datatype doesn't change? Does it give an error message? Does your server crash and spit blue smoke?

-PatP|||You cannot change the datatype to money from (n)varchar, because this conversion cannot be done implicitly. You'll have to create a new table with desired datatype (money), use CONVERT/CAST function to insert the data into it, then drop the original table, create a new table with the same name, and insert the data back in.

Money to Varchar

How can I alter a field datatype from money to varchar?To change the column type you will have to create a new table and copy the data over using the convert function, example below.

--create table x
--(a int null,
--b money null)

CREATE TABLE dbo.Tmp_x
(
a int NULL,
b nvarchar(50) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.x)
EXEC('INSERT INTO dbo.Tmp_x (a, b)
SELECT a, CONVERT(nvarchar(50), b) FROM dbo.x')
GO
DROP TABLE dbo.x
GO
EXECUTE sp_rename N'dbo.Tmp_x', N'x', 'OBJECT'
GO|||You can also add a temporary column to your table of the varchar type and fill it with the data from the money column to see if all values can be converted. If so, empty the money column, change the datatype of the money column to varchar and fill it with the data of the temporary column. Finally delete the temporary column.

To add a column:

alter table owner.your_table
add temp_column varchar(10)

To modify a column datatype:

alter table owner.your_table
alter column your_column varchar(10)

To drop a column:

alter table owner.your_table
drop column temp_column