SELECT statement where the criteria varies? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

SELECT statement where the criteria varies?

Author

Topic

mark1504
Posting Yak Master

103 Posts

Posted - 2009-10-08 : 14:15:22

Another puzzle for SQLTeam's transact maestros...

Let's say there's a waiter called Tom working for a catering company and he works in different locations from day to day. I need to SELECT for next week his working shifts and the names of any other staff working with him on the same location and day. Here's some play data...

CREATE TABLE Working
(
theday datetime,
Person varchar(10),
Location varchar(10)
)
GO
INSERT INTO Working VALUES ('12-Oct-2009','Tom','Savoy')
INSERT INTO Working VALUES ('13-Oct-2009','Tom','Ritz')
INSERT INTO Working VALUES ('14-Oct-2009','Tom','Dorchester')
INSERT INTO Working VALUES ('15-Oct-2009','Tom','Dorchester')
INSERT INTO Working VALUES ('12-Oct-2009','Dick','Savoy')
INSERT INTO Working VALUES ('13-Oct-2009','Dick','Savoy')
INSERT INTO Working VALUES ('14-Oct-2009','Dick','Dorchester')
INSERT INTO Working VALUES ('15-Oct-2009','Dick','Ritz')
INSERT INTO Working VALUES ('12-Oct-2009','Harry','Ritz')
INSERT INTO Working VALUES ('13-Oct-2009','Harry','Ritz')
INSERT INTO Working VALUES ('14-Oct-2009','Harry','Dorchester')
INSERT INTO Working VALUES ('15-Oct-2009','Harry','Savoy')

To do it for one day is easy enough...
SELECT * FROM Working WHERE theday='12-Oct-2009' AND Location IN (SELECT Location FROM Working WHERE Person='Tom' AND theday='12-Oct-2009')

... but what about several days, when the criteria - Tom's location - changes from day to day? That's stumped me. Any solutions or ideas would be most appreciated.

Mark

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-08 : 14:30:25

Add three parameters to your stored procedure/query: Location, BeginDate and EndDate.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-08 : 14:33:31

You'd have to add some parameters

DECLARE @startDate datetime
DECLARE @EndDate datetime

SET @StartDate = '12-Oct-2009'
SET @EndDate = @StartDAte

DECLARE @working TABLE
(
theday datetime,
Person varchar(10),
Location varchar(10)
)

INSERT INTO @working VALUES ('12-Oct-2009','Tom','Savoy')
INSERT INTO @working VALUES ('13-Oct-2009','Tom','Ritz')
INSERT INTO @working VALUES ('14-Oct-2009','Tom','Dorchester')
INSERT INTO @working VALUES ('15-Oct-2009','Tom','Dorchester')
INSERT INTO @working VALUES ('12-Oct-2009','Dick','Savoy')
INSERT INTO @working VALUES ('13-Oct-2009','Dick','Savoy')
INSERT INTO @working VALUES ('14-Oct-2009','Dick','Dorchester')
INSERT INTO @working VALUES ('15-Oct-2009','Dick','Ritz')
INSERT INTO @working VALUES ('12-Oct-2009','Harry','Ritz')
INSERT INTO @working VALUES ('13-Oct-2009','Harry','Ritz')
INSERT INTO @working VALUES ('14-Oct-2009','Harry','Dorchester')
INSERT INTO @working VALUES ('15-Oct-2009','Harry','Savoy')

SELECT * FROM @working WHERE theday between @StartDate and @EndDate
AND Location IN
(SELECT Location FROM @working WHERE Person='Tom' AND theday between @StartDate and @EndDate)

Jim

Everyday I learn something that somebody else already knew

mark1504
Posting Yak Master

103 Posts

Posted - 2009-10-08 : 14:51:15

Thanks for the quick response

...but sorry, I don't understand the @working thing, as Working is an existing data table, not something I'm creating as a variable in the stored procedure. Jim, it looks like your example would give all the working staff at any location Tom has worked at during the given dates, and I'm trying to return only the staff at the same location and same day as Tom.

Thanks, Mark

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-08 : 15:10:45

Always listen to the word of an Almighty SQL Goddess over mine. You will then need the location parameter. The @working is table variable I created, rather than creating a table "working" on my database and then having to delete it.

Jim

If I'm still not clear, give me an example of a result you'd expect
SELECT w.*
FROM @working w

inner join
(SELECT Location,theday
FROM @working WHERE Person='Tom' AND theday between @StartDate and @EndDate
) w1

ON
w.theDay = w1.theDay
and w.Location = w1.Location

ORDER BY 1

Jim

Everyday I learn something that somebody else already knew

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-08 : 15:12:38

@working is just a table variable so Jim hasn't to create unwanted tables in his system.

You can copy and paste his code to new query window and hit F5 and see the result!
If it is ok, you can replace @working by your wanted tablename - that's it.

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

mark1504
Posting Yak Master

103 Posts

Posted - 2009-10-09 : 06:24:02

Thank you so much Jim, this was exactly what I needed.

CASE CLOSED

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources