Returning records that match a list

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)

Returning records that match a list

Author

Topic

robc
Yak Posting Veteran

60 Posts

Posted - 2003-10-01 : 18:17:37

I have a range of dates in a comma delimited format.
eg. 2003-09-01,2003-09-02 ..

I'm stuck. I know how to pass that to the SP , but what i'm looking for is to return only that satisfy all of those dates.

example. If i want someone who's available on datea,dateb,datec, i want it to make sure that "USERA" is available on dateA,dateB,dateC.

it's a simple table . userID, dateAvailable.

?? cries help.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-01 : 19:03:17

easiest to create a function to parse the csv string and return a table of dates then

declare @dtes table (dte datetime)
insert @dtes select * from dbo.fn_getcsvdate(@dates)
join @dtes d
on tbl.dateAvailable = d.dte
group by userID
having count(*) = (select count(*) from @dtes)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-01 : 19:49:26

Information about creating a function can be found in SQL Server Books Online. There are examples in there. We do not like to duplicate information that can be found in the manual.

Tara

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-10-02 : 03:12:31

Rob, the part of nr's answer where it says having count(*) = (select count(*) from @dtes) should have given you the ALL matches instead of the ANY. If you're not getting that, perhaps you could post your current SELECT statement and the source code for the function for us to review.

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

robc
Yak Posting Veteran

60 Posts

Posted - 2004-04-01 : 18:10:29

I'm revisiting this problem because I was unable to resolve it. So i'm hoping that someone will be able to offer some insight to what is going wrong.

I've created the function that successfully passes me a table of dates.

My error is here;

on tbl.dateAvailable = d.dte

should tbl = the original table containing my date. Because it gives me an error if i replace tbl with ucAvailability (my table containing the original data)

"column prefix "ucAvailability" does not match an alias or table name used in the query"

i'm missing something. Hope this helps.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-01 : 18:33:02

Do you think it might be a little easier for us to troubleshoot if we can see the query that generates the error?

- Jeff

robc
Yak Posting Veteran

60 Posts

Posted - 2004-04-01 : 19:04:13

quote:
Originally posted by jsmith8858

Do you think it might be a little easier for us to troubleshoot if we can see the query that generates the error?

- Jeff

sure Jeff,

Here it is:

declare @dtes table (dte datetime)
insert @dtes select * from dbo.parse_date_range(@dates)
join @dtes d
on tbl.dateAvailable = d.dte
group by userID
having count(*) = (select count(*) from @dtes)

Now i'm scratching my head while looking at this. If I substitute "tbl.dateavailable" with "ucAvailability.dateAvailable" i get the error mentioned in my previous post.

The ucAvailability table structure is this
userID smallint, dateAvailable dateTime.

I don't see anything in this code that brings back the userID.
The reason I'm doing this is to bring back all the users who satisfy the range of dates supplied. And i'd assume that i'd compare the table of supplied dates to my "ucAvailabilty" table somehow. I'm just a bit unclear still. Thanks!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-01 : 19:19:12

where is "tbl" defined? you have not defined it anywhere in your SQL.

- Jeff

robc
Yak Posting Veteran

60 Posts

Posted - 2004-04-01 : 19:30:39

quote:
Originally posted by jsmith8858

where is "tbl" defined? you have not defined it anywhere in your SQL.

- Jeff

That's what im' wondering too. If you read the second post in this thread, NR gave me that code. I've yet to get it to work. I'm unsure what he means by tbl. I know it's undefined, but what is it supposed to represent? The table returned from the function? or my "Ucavailability" table. I'm working feversishly on this but my sql skill is not up to par.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-01 : 22:46:52

DECLARE @dates VARCHAR(55)

SELECT @dates = '09/01/2003,09/02/2003'

SELECT
u.userID
FROM
dbo.parse_date_range(@dates) AS d
INNER JOIN user_availability u ON u.dateAvailable = d.dateAvailable
GROUP BY
u.userID
HAVING
COUNT(u.userID) = (SELECT COUNT(dateAvailable) FROM dbo.parse_date_range(@dates))

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

robc
Yak Posting Veteran

60 Posts

Posted - 2004-04-01 : 23:34:58

Thank you very much. That works perfectly.
Cheers Derrick!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-02 : 02:42:33

tbl was your table that held the userIDs and dates available.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources