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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Removing duplicate values

Author  Topic 

rgjb
Starting Member

19 Posts
Posted - 2008-02-11 : 16:47:03
Hi,
I'm writing a query to report on the leave that was taken over the Christmas period for all employees. But I find when I run the query, for the days when leave was being taken. The dates are duplicated. giving me the following result set:

107847 107847 2007-12-01 00:00:00.000
107847 107847 2007-12-02 00:00:00.000
107847 107847 2007-12-03 00:00:00.000
107847 107847 2007-12-04 00:00:00.000
107847 107847 2007-12-05 00:00:00.000
107847 107847 2007-12-06 00:00:00.000
107847 107847 2007-12-07 00:00:00.000
107847 107847 2007-12-08 00:00:00.000
107847 107847 2007-12-09 00:00:00.000
107847 107847 2007-12-10 00:00:00.000
107847 107847 2007-12-11 00:00:00.000
107847 107847 2007-12-12 00:00:00.000
107847 107847 2007-12-13 00:00:00.000
107847 107847 2007-12-14 00:00:00.000
107847 107847 2007-12-15 00:00:00.000
107847 107847 2007-12-16 00:00:00.000
107847 107847 2007-12-17 00:00:00.000
107847 107847 2007-12-18 00:00:00.000
107847 107847 2007-12-19 00:00:00.000
107847 107847 2007-12-19 00:00:00.000 x
107847 107847 2007-12-20 00:00:00.000
107847 107847 2007-12-20 00:00:00.000 x
107847 107847 2007-12-21 00:00:00.000
107847 107847 2007-12-21 00:00:00.000 x
107847 107847 2007-12-22 00:00:00.000
107847 107847 2007-12-22 00:00:00.000 x
107847 107847 2007-12-23 00:00:00.000
107847 107847 2007-12-23 00:00:00.000 x
107847 107847 2007-12-24 00:00:00.000
107847 107847 2007-12-24 00:00:00.000 x
107847 107847 2007-12-25 00:00:00.000
107847 107847 2007-12-25 00:00:00.000 x
107847 107847 2007-12-26 00:00:00.000
107847 107847 2007-12-26 00:00:00.000 x
107847 107847 2007-12-27 00:00:00.000
107847 107847 2007-12-27 00:00:00.000 x
107847 107847 2007-12-28 00:00:00.000
107847 107847 2007-12-28 00:00:00.000 x
107847 107847 2007-12-29 00:00:00.000
107847 107847 2007-12-29 00:00:00.000 x
107847 107847 2007-12-30 00:00:00.000
107847 107847 2007-12-30 00:00:00.000 x
107847 107847 2007-12-31 00:00:00.000
107847 107847 2007-12-31 00:00:00.000 x
107847 107847 2008-01-01 00:00:00.000
107847 107847 2008-01-01 00:00:00.000 x
107847 107847 2008-01-02 00:00:00.000
107847 107847 2008-01-02 00:00:00.000 x
107847 107847 2008-01-03 00:00:00.000
107847 107847 2008-01-03 00:00:00.000 x
107847 107847 2008-01-04 00:00:00.000
107847 107847 2008-01-04 00:00:00.000 x
107847 107847 2008-01-05 00:00:00.000
107847 107847 2008-01-06 00:00:00.000
107847 107847 2008-01-07 00:00:00.000
107847 107847 2008-01-08 00:00:00.000
107847 107847 2008-01-09 00:00:00.000
107847 107847 2008-01-10 00:00:00.000
107847 107847 2008-01-11 00:00:00.000
107847 107847 2008-01-12 00:00:00.000
107847 107847 2008-01-13 00:00:00.000
107847 107847 2008-01-14 00:00:00.000
107847 107847 2008-01-15 00:00:00.000
107847 107847 2008-01-16 00:00:00.000
107847 107847 2008-01-17 00:00:00.000
107847 107847 2008-01-18 00:00:00.000
107847 107847 2008-01-19 00:00:00.000
107847 107847 2008-01-20 00:00:00.000
107847 107847 2008-01-21 00:00:00.000
107847 107847 2008-01-22 00:00:00.000
107847 107847 2008-01-23 00:00:00.000
107847 107847 2008-01-24 00:00:00.000
107847 107847 2008-01-25 00:00:00.000
107847 107847 2008-01-26 00:00:00.000
107847 107847 2008-01-27 00:00:00.000
107847 107847 2008-01-28 00:00:00.000
107847 107847 2008-01-29 00:00:00.000
107847 107847 2008-01-30 00:00:00.000
107847 107847 2008-01-31 00:00:00.000

The "X" in the far right column marks the days when the employee was on leave. Here then is my view:

select distinct
ea.emp_no,
gb2.emp_no,
gb2.dt,
case when gb2.dt between ea.absence_start_date and ea.absence_end_date then 'x' else '' end as A1
from dbo.emp_absence_tmp ea join
(select top 100 percent p.emp_no,cast('20071201' as datetime) + n -1 as dt
from dbo.nums,person_tmp p
where n <= datediff(day,'20071201', '20080131') + 1
order by emp_no) as gb2 on gb2.emp_no = ea.emp_no
where ea.emp_no ='107847'
order by gb2.dt


And here are the tables that make it up (including the nums one)

--Create the Emp absence Table

CREATE TABLE [dbo].[EMP_ABSENCE_TMP](
[EMP_NO] [char](10) NOT NULL,
[ABSENCE_START_DATE] [datetime] NOT NULL,
[ABSENCE_END_DATE] [datetime] NOT NULL,

) ON [PRIMARY]

--Insert values into the Emp absence Table

INSERT INTO [dbo].[EMP_ABSENCE_TMP] ([EMP_NO],[ABSENCE_START_DATE],[ABSENCE_END_DATE])
VALUES ('107847','20071219','20071231')
INSERT INTO [dbo].[EMP_ABSENCE_TMP] ([EMP_NO],[ABSENCE_START_DATE],[ABSENCE_END_DATE])
VALUES ('107847','20080101','20080104')

--Create the person Table

CREATE TABLE [dbo].[PERSON_TMP]([EMP_NO] [char](10) NOT NULL,) ON [PRIMARY]

-- Insert a value

INSERT INTO [dbo].[PERSON_TMP] ([EMP_NO])
VALUES ('107847')

-- Create & populate the nums table


create table dbo.Nums(n int not null primary key);
declare @max as int, @rc as int;
set @max = 1000000;
set @rc = 1;

insert into nums values(1);
while @rc * 2 <= @max
begin
insert into dbo.nums select n + @rc from dbo.nums;
set @rc = @rc* 2;
end

insert into dbo.nums
select n + @rc from dbo.nums where n + @rc <= @max;

--++++++


I can see why the duplication is occuring. If I add the column
ea.absence_start_date
to the view, I see the following result set:

107847 107847 2007-12-21 00:00:00.000 2007-12-19 00:00:00.000 x
107847 107847 2007-12-21 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2007-12-22 00:00:00.000 2007-12-19 00:00:00.000 x
107847 107847 2007-12-22 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2007-12-23 00:00:00.000 2007-12-19 00:00:00.000 x
107847 107847 2007-12-23 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2007-12-24 00:00:00.000 2007-12-19 00:00:00.000 x
107847 107847 2007-12-24 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2007-12-25 00:00:00.000 2007-12-19 00:00:00.000 x
107847 107847 2007-12-25 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2007-12-26 00:00:00.000 2007-12-19 00:00:00.000 x
107847 107847 2007-12-26 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2007-12-27 00:00:00.000 2007-12-19 00:00:00.000 x
107847 107847 2007-12-27 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2007-12-28 00:00:00.000 2007-12-19 00:00:00.000 x
107847 107847 2007-12-28 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2007-12-29 00:00:00.000 2007-12-19 00:00:00.000 x
107847 107847 2007-12-29 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2007-12-30 00:00:00.000 2007-12-19 00:00:00.000 x
107847 107847 2007-12-30 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2007-12-31 00:00:00.000 2007-12-19 00:00:00.000 x
107847 107847 2007-12-31 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2008-01-01 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-01 00:00:00.000 2008-01-01 00:00:00.000 x
107847 107847 2008-01-02 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-02 00:00:00.000 2008-01-01 00:00:00.000 x
107847 107847 2008-01-03 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-03 00:00:00.000 2008-01-01 00:00:00.000 x
107847 107847 2008-01-04 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-04 00:00:00.000 2008-01-01 00:00:00.000 x
107847 107847 2008-01-05 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-05 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2008-01-06 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-06 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2008-01-07 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-07 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2008-01-08 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-08 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2008-01-09 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-09 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2008-01-10 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-10 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2008-01-11 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-11 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2008-01-12 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-12 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2008-01-13 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-13 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2008-01-14 00:00:00.000 2007-12-19 00:00:00.000
107847 107847 2008-01-14 00:00:00.000 2008-01-01 00:00:00.000
107847 107847 2008-01-15 00:00:00.000 2007-12-19 00:00:00.000

But I just can't see how to remove it and still keep the dates where leave was not taken. Any assistance would be greatly appreciated.

Cheers
Gregg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-02-12 : 00:22:24
This is what you want:-


select distinct 
gb2.emp_no,
gb2.dt,
case when gb2.dt between ea.absence_start_date and ea.absence_end_date then 'x' else '' end as A1
from 
(select top 100 percent p.emp_no,cast('20071201' as datetime) + n -1 as dt
from dbo.nums,person_tmp p
where n <= datediff(day,'20071201', '20080131') + 1 
order by emp_no) as gb2 left join
dbo.emp_absence_tmp ea  
 on gb2.emp_no = ea.emp_no
and gb2.dt between ea.absence_start_date and ea.absence_end_date
where gb2.emp_no ='107847'
order by gb2.dt

output
-----------------------
emp_no     dt                      A1
---------- ----------------------- ----
107847     2007-12-01 00:00:00.000 
107847     2007-12-02 00:00:00.000 
107847     2007-12-03 00:00:00.000 
107847     2007-12-04 00:00:00.000 
107847     2007-12-05 00:00:00.000 
107847     2007-12-06 00:00:00.000 
107847     2007-12-07 00:00:00.000 
107847     2007-12-08 00:00:00.000 
107847     2007-12-09 00:00:00.000 
107847     2007-12-10 00:00:00.000 
107847     2007-12-11 00:00:00.000 
107847     2007-12-12 00:00:00.000 
107847     2007-12-13 00:00:00.000 
107847     2007-12-14 00:00:00.000 
107847     2007-12-15 00:00:00.000 
107847     2007-12-16 00:00:00.000 
107847     2007-12-17 00:00:00.000 
107847     2007-12-18 00:00:00.000 
107847     2007-12-19 00:00:00.000 x
107847     2007-12-20 00:00:00.000 x
107847     2007-12-21 00:00:00.000 x
107847     2007-12-22 00:00:00.000 x
107847     2007-12-23 00:00:00.000 x
107847     2007-12-24 00:00:00.000 x
107847     2007-12-25 00:00:00.000 x
107847     2007-12-26 00:00:00.000 x
107847     2007-12-27 00:00:00.000 x
107847     2007-12-28 00:00:00.000 x
107847     2007-12-29 00:00:00.000 x
107847     2007-12-30 00:00:00.000 x
107847     2007-12-31 00:00:00.000 x
107847     2008-01-01 00:00:00.000 x
107847     2008-01-02 00:00:00.000 x
107847     2008-01-03 00:00:00.000 x
107847     2008-01-04 00:00:00.000 x
107847     2008-01-05 00:00:00.000 
107847     2008-01-06 00:00:00.000 
107847     2008-01-07 00:00:00.000 
107847     2008-01-08 00:00:00.000 
107847     2008-01-09 00:00:00.000 
107847     2008-01-10 00:00:00.000 
107847     2008-01-11 00:00:00.000 
107847     2008-01-12 00:00:00.000 
107847     2008-01-13 00:00:00.000 
107847     2008-01-14 00:00:00.000 
107847     2008-01-15 00:00:00.000 
107847     2008-01-16 00:00:00.000 
107847     2008-01-17 00:00:00.000 
107847     2008-01-18 00:00:00.000 
107847     2008-01-19 00:00:00.000 
107847     2008-01-20 00:00:00.000 
107847     2008-01-21 00:00:00.000 
107847     2008-01-22 00:00:00.000 
107847     2008-01-23 00:00:00.000 
107847     2008-01-24 00:00:00.000 
107847     2008-01-25 00:00:00.000 
107847     2008-01-26 00:00:00.000 
107847     2008-01-27 00:00:00.000 
107847     2008-01-28 00:00:00.000 
107847     2008-01-29 00:00:00.000 
107847     2008-01-30 00:00:00.000 
107847     2008-01-31 00:00:00.000
Go to Top of Page

rgjb
Starting Member

19 Posts
Posted - 2008-02-12 : 16:06:11
Thanks Heaps. It's always something simple. It seems.
Go to Top of Page

anuksundar
Starting Member

4 Posts
Posted - 2008-02-21 : 07:41:53
create table employee
(emp_id int)

create table absent(
emp_id int,
start_date datetime,
end_date datetime)

insert into employee values(1)
insert into employee values(2)
insert into employee values(3)

insert into absent values(1,'12/02/2007','12/26/2007')
insert into absent values(2,'12/12/2007','12/26/2007')
insert into absent values(2,'12/12/2007','12/26/2007')

select distinct(e.emp_id),a.start_date,a.end_date from employee e inner join absent a on e.emp_id = a.emp_id



anuradhak
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site