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

Transact-SQL (2005)

Query

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-06-21 : 09:40:53

CREATE TABLE #temp
(
employeeId VARCHAR(10),
scheduleddate DATETIME,
isScheduledBothSundayAndSaturday VARCHAR(1)
)

INSERT INTO #temp VALUES(1,'02/12/2011',NULL)
INSERT INTO #temp VALUES(2,'02/12/2011',NULL)
INSERT INTO #temp VALUES(3,'02/12/2011',NULL)
INSERT INTO #temp VALUES(4,'02/12/2011',NULL)
INSERT INTO #temp VALUES(1,'02/13/2011',NULL)
INSERT INTO #temp VALUES(5,'02/17/2011',NULL)
INSERT INTO #temp VALUES(3,'02/13/2011',NULL)
INSERT INTO #temp VALUES(10,'02/16/2011',NULL)
INSERT INTO #temp VALUES(11,'02/19/2011',NULL)
INSERT INTO #temp VALUES(12,'02/22/2011',NULL)

I want to get only those employees which having scheduleddate for both saturday and sunday.

eg. SELECT *,DATENAME(dw,scheduleddate) FROM #temp ORDER BY employeeID

In the above case the result should be EmployeeIDs - 1 and 3

Please give some suggestions....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-21 : 09:56:50

[code]SELECT *
FROM #temp t
WHERE DATENAME(dw,scheduleddate) = 'Saturday'
and exists (select * from #temp t1 where t1.employeeId=t.employeeId
and DATENAME(dw,scheduleddate) ='Sunday')
ORDER BY employeeID
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-06-21 : 10:03:56

Hi,

I want get only those employees which have BOTH DATENAME(dw,scheduleddate) = 'Saturday' AND DATENAME(dw,scheduleddate) = 'Sunday'

Both the conditions should be satisfied i.e.,(Employee is Scheduled for both Sunday & Saturday)

quote:
Originally posted by webfred

SELECT *,DATENAME(dw,scheduleddate)
FROM #temp
WHERE DATENAME(dw,scheduleddate) in ('Saturday','Sunday')
ORDER BY employeeID

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-21 : 10:08:32

Yes - have a look.
I have corrected the solution...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources