Need Help with query for distinct dates

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 2008 Forums

Transact-SQL (2008)

Need Help with query for distinct dates

Author

Topic

hansr
Starting Member

2 Posts

Posted - 2014-09-04 : 11:44:57

Hi

I have a need to only find distinct dates in which a worker worked in the factory using TSQL



RowNumber	 workerstartDate  	workerenddate
1		 2012-08-08 		2012-10-10 
2		 2012-08-10 		2012-08-31 
3		 2012-09-05 		2012-09-15 
4		 2012-10-15 		2012-12-19 
5		 2013-01-02 		2013-03-14 
6		2013-03-15 		2013-05-23

Basically, I am looking for the above to look like this


rownumber	workerstartDate  	workerenddate
1		2012-08-08 		2012-10-10 
4		2012-10-15 		2012-12-19 
5		2013-01-02 		2013-03-14 
6		2013-03-15 		2013-05-23

The below 2 rows have been removed as their dates fell inside the first range of date in row 1 i..e. from 8-8-2012 to 10-10-2012


Rownumber           workerstartdate                 workerenddate
2                    2012-08-10                       2012-08-31
3                   2012-09-05                        2012-09-15

Can you please help.

Thanks,
Hans

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-04 : 12:03:18

[CODE]select *
from MyTable t
where not exists (
select *
from MyTable t1
where t1.workerStartDate <= t.workerStartDate
and t.workerEndDate <= t1.workerEndDate
)[/CODE]In essence, I want this record unless there exists a record that encompasses this record. I'm not clear on what you'd want if the two time intervals partially overlap (e.g., X starts before Y but Y ends later than X).

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

hansr
Starting Member

2 Posts

Posted - 2014-09-04 : 12:10:00

Thanks Bustaz .. I will try this out and let you know..

In case the records partially overlap

I wanted the start date of the earliest record and the end date of the latest record

i.e. for


rownumber	workerstartDate  	workerenddate
7		2013-08-08 		2013-10-10 
8		2013-09-07 		2013-12-19

For this the result should be


rownumber	workerstartDate  	workerenddate
7		2013-08-08 		2013-12-19

Thanks once again,
Hans

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-04 : 14:20:25

Errata: In my code snippet if the two time intervals are identical then neither would be reported. You'd need to account for that somehow.

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources