Date Query - SQL Server Forums

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

Transact-SQL (2000)

Date Query

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-06-29 : 11:42:25

Hello Everyone,

I am writing the application for the training. if the employees date is clashing with some another date in the same table then it should display to me.

i have attaching the script and the same data for it.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TrUnSchldReqEmpTmp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TrUnSchldReqEmpTmp]
GO

CREATE TABLE [dbo].[TrUnSchldReqEmpTmp] (
[TrReqNo] [int] NOT NULL ,
[EmpCrsReqNo] [int] NULL ,
[EmpID] [int] NULL ,
[Course] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[Centre] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[Event] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[FromDate] [datetime] NULL ,
[ToDate] [datetime] NULL ,
[Currency] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
[CourseFees] [money] NULL ,
[OtherExpns] [money] NULL ,
[TotExp] [money] NULL ,
[Consider] [bit] NULL ,
[LocLib1] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[LocLib2] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[LocLib3] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[LocLib4] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[LocLib5] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[SalProfile] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[Qryno] [int] NULL ,
[Requested] [bit] NULL ,
[RecordType] [tinyint] NULL ,
[FeedBackPosted] [bit] NULL ,
[UniqueNo] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO TrUnSchldReqEmpTmp VALUES ( 5,NULL,3,'C010','001','EB-C010','Jan 1 2004 12:00AM','Jan 2 2004 12:00AM',NULL,0.00,NULL,NULL,NULL,'WC0003','DP0011','DV0026','WP0057',NULL,'7',NULL,NULL,NULL,NULL,0)
INSERT INTO TrUnSchldReqEmpTmp VALUES ( 5,NULL,5,'C005','BR001','EV1','Feb 12 2004 12:00AM','Feb 20 2004 12:00AM',NULL,1000.00,NULL,NULL,NULL,'WC0001','DP0003','DV0007','WP00011',NULL,'8',NULL,NULL,NULL,NULL,1)
INSERT INTO TrUnSchldReqEmpTmp VALUES ( 18,NULL,736,'C010','001','EB-C010','Jan 1 2004 12:00AM','Jan 2 2004 12:00AM',NULL,0.00,NULL,NULL,NULL,'WC0001','DP0006','DV0020','WP00011',NULL,'5',NULL,NULL,NULL,NULL,0)
INSERT INTO TrUnSchldReqEmpTmp VALUES ( 20,NULL,5,'C010','BR001','EV7','Jan 11 2004 12:00AM','Jan 21 2004 12:00AM',NULL,4000.00,NULL,NULL,NULL,'WC0001','DP0003','DV0007','WP00011',NULL,'8',NULL,NULL,NULL,NULL,0)
INSERT INTO TrUnSchldReqEmpTmp VALUES ( 21,NULL,2,'C005','001','EB-C005','Jan 1 2004 12:00AM','Jan 2 2004 12:00AM',NULL,0.00,NULL,NULL,NULL,'WC0003','DP0011','DV0026','WP0057',NULL,'7',NULL,NULL,NULL,NULL,0)
INSERT INTO TrUnSchldReqEmpTmp VALUES ( 22,NULL,734,'C005','001','EB-C005','Jan 1 2004 12:00AM','Jan 2 2004 12:00AM',NULL,0.00,NULL,NULL,NULL,'WC0001','DP0003','DV0007','WP000111',NULL,'1',NULL,NULL,NULL,NULL,0)
INSERT INTO TrUnSchldReqEmpTmp VALUES ( 22,NULL,4,'C002','BR002','EV3','Jan 13 2004 12:00AM','Jan 21 2004 12:00AM',NULL,3000.00,NULL,NULL,NULL,'WC0003','DP0011','DV0026','WP0057',NULL,'7',NULL,NULL,NULL,NULL,1)

GO

Now there is Column name FromDate And ToDate, which specify the employee Training Dates and there is Column Name EmpID which specify the employee.

now i want whether a particular employees training dates or clashed or not.

Any help will be great.

Thanks

Complicated things can be done by simple thinking

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-29 : 14:11:28

Thanks for the DDL/DML. Please post the expected results of the query you want based on the data you provided.

Be One with the Optimizer
TG

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-29 : 14:11:38

Not exactly sure what you are trying to do here. Expected resulset would be nice. Im thinking that you want to return any training sessions that overlap in dates?

Does this do what you want. I added a primary key to your table (TrUnSchldReqEmpTmpID) so we could uniquely identify each row. I self-join off empID where the primary key is not equal. This way we are only comparing different entries. You could probably use courseID the same way if you enforce that a empID cannot enroll in the same courseID more than once.

Post with more detail.

- Nathan Skerl

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TrUnSchldReqEmpTmp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TrUnSchldReqEmpTmp]
GO

CREATE TABLE [dbo].[TrUnSchldReqEmpTmp] (
[TrUnSchldReqEmpTmpID] [int] identity (1,1) primary key,
[TrReqNo] [int] NOT NULL ,
[EmpCrsReqNo] [int] NULL ,
[EmpID] [int] NULL ,
[Course] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[Centre] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[Event] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[FromDate] [datetime] NULL ,
[ToDate] [datetime] NULL ,
[Currency] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
[CourseFees] [money] NULL ,
[OtherExpns] [money] NULL ,
[TotExp] [money] NULL ,
[Consider] [bit] NULL ,
[LocLib1] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[LocLib2] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[LocLib3] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[LocLib4] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[LocLib5] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[SalProfile] [varchar] (9) COLLATE Arabic_CI_AS NULL ,
[Qryno] [int] NULL ,
[Requested] [bit] NULL ,
[RecordType] [tinyint] NULL ,
[FeedBackPosted] [bit] NULL ,
[UniqueNo] [int] NULL 
) ON [PRIMARY]
GO

INSERT INTO TrUnSchldReqEmpTmp (TrReqNo, EmpCrsReqNo, EmpID, Course, Centre, Event, FromDate, ToDate, Currency, CourseFees, OtherExpns, TotExp, Consider, LocLib1, LocLib2, LocLib3, LocLib4, LocLib5, SalProfile, Qryno, Requested, RecordType, FeedBackPosted, UniqueNo)
	SELECT 5,NULL,3,'C010','001','EB-C010','Jan 1 2004 12:00AM','Jan 2 2004 12:00AM',NULL,0.00,NULL,NULL,NULL,'WC0003','DP0011','DV0026','WP0057',NULL,'7',NULL,NULL,NULL,NULL,0 UNION ALL
	SELECT 5,NULL,5,'C005','BR001','EV1','Feb 12 2004 12:00AM','Feb 20 2004 12:00AM',NULL,1000.00,NULL,NULL,NULL,'WC0001','DP0003','DV0007','WP00011',NULL,'8',NULL,NULL,NULL,NULL,1 UNION ALL
	SELECT 18,NULL,736,'C010','001','EB-C010','Jan 1 2004 12:00AM','Jan 2 2004 12:00AM',NULL,0.00,NULL,NULL,NULL,'WC0001','DP0006','DV0020','WP00011',NULL,'5',NULL,NULL,NULL,NULL,0 UNION ALL
	SELECT 20,NULL,5,'C010','BR001','EV7','Jan 11 2004 12:00AM','Jan 21 2004 12:00AM',NULL,4000.00,NULL,NULL,NULL,'WC0001','DP0003','DV0007','WP00011',NULL,'8',NULL,NULL,NULL,NULL,0 UNION ALL
	SELECT 21,NULL,2,'C005','001','EB-C005','Jan 1 2004 12:00AM','Jan 2 2004 12:00AM',NULL,0.00,NULL,NULL,NULL,'WC0003','DP0011','DV0026','WP0057',NULL,'7',NULL,NULL,NULL,NULL,0 UNION ALL
	SELECT 22,NULL,734,'C005','001','EB-C005','Jan 1 2004 12:00AM','Jan 2 2004 12:00AM',NULL,0.00,NULL,NULL,NULL,'WC0001','DP0003','DV0007','WP000111',NULL,'1',NULL,NULL,NULL,NULL,0 UNION ALL
	SELECT 22,NULL,4,'C002','BR002','EV3','Jan 13 2004 12:00AM','Jan 21 2004 12:00AM',NULL,3000.00,NULL,NULL,NULL,'WC0003','DP0011','DV0026','WP0057',NULL,'7',NULL,NULL,NULL,NULL,1
	-- insert conflict for empId = 4
	union all
	SELECT 22,NULL,4,'C002','BR002','EV3','Jan 15 2004 12:00AM','Jan 17 2004 12:00AM',NULL,3000.00,NULL,NULL,NULL,'WC0003','DP0011','DV0026','WP0057',NULL,'7',NULL,NULL,NULL,NULL,1
	
GO

select TrUnSchldReqEmpTmpID, empid, fromDate, toDate
from TrUnSchldReqEmpTmp

select t1.empID, t1.fromDate, t1.toDate
from TrUnSchldReqEmpTmp t1 inner join TrUnSchldReqEmpTmp t2
	on  t1.empID = t2.empID
		and t1.TrUnSchldReqEmpTmpID <> t2.TrUnSchldReqEmpTmpID
where (t1.fromDate between t2.fromDate and t2.todate) or (t1.toDate between t2.fromDate and t2.todate)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-06-30 : 01:57:05

Thanks for the reply.

EmpID Event Start Date End Date
5 EC-001 05/01/2004 01/05/04
5 EC-002 05/02/2004 06/05/04

see in the above Case the EmpID 5 is under 2 differents event, but the dates of this event are clashing.
now 1 employee can not go 2 different place at the same time for training, i want to evoke the exception for the same.

so i want to list all the employees whoese dates are clashing,according to their events.

Awaiting for the positive reply

Complicated things can be done by simple thinking

andy8979
Starting Member

36 Posts

Posted - 2005-06-30 : 03:59:55

The query mentioned in the above post by "nathans" should work fine also you can use the (Exists) option I guess this should work :

select t1.empID, t1.fromDate, t1.toDate
from TrUnSchldReqEmpTmp t1
Where exists (Select 1 from TrUnSchldReqEmpTmp t2 where t1.empID = t2.empID
and t1.TrUnSchldReqEmpTmpID <> t2.TrUnSchldReqEmpTmpID
And ((t1.fromDate between t2.fromDate and t2.todate) or (t1.toDate between t2.fromDate and t2.todate)))

Anuj.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-06-30 : 04:19:32

Thanks a lot .. Anuj and Nathans
The quries worked for me

Complicated things can be done by simple thinking

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-30 : 09:18:10

quote:
EmpID Event Start Date End Date
5 EC-001 05/01/2004 01/05/04
5 EC-002 05/02/2004 06/05/04

The first entry also is in conflict with itself.

StartDate > EndDate

Do you need to account for this as well, or is this just a typo?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-06-30 : 09:26:52

hey
Thanks. yours earlier query worked for me very perfectly.

Thanks once again

Complicated things can be done by simple thinking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources