best approach for searching a datetime with a huge list of dates 1

[1DMF]

Hi,

Can someone help me with attacking my latest requirement for an SP query.

I've been tasked with searching a table where the datetime field could be in a list of over 500 datetimes!

I was thinking of using the 'IN' clause but that doesn't appear to be compatible with DATETIME as the in parameter would need to be NTEXT and SQL is moaning.

I've also found threads that using 'IN' with a large list has limitations and speed issues.

So how do i do it?

How do I write a T-SQL query when i want to get all records that match a huge list of potential dates?

Note: this is not a date range, but a list of specific dates!


Thanks,

1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[PWise]

How about createing a table with the dates that you are scharching for and inner joining

 

[1DMF]

yes I thought of that but my join would be on DOB1 OR DOB2

How do you do that?

So i have a list of DOB's

I have a table where there is a DOB1 & DOB2 , I want to search the table where DOB1 OR DOB2 is in the list of DOB's

I'm struggling to get my head round how you would do that with a join?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[PWise]

the above post would be good for a 1 time thing
if this an ongoing thing and you have a Finite amount of dates
let us say 4 dates create in the sp a temp table or a table varaible and pass the dates as prams

@date1 datetime,
@date2 datetime,
@date4 datetime,
@date3 datetime,

declare @mydates table (Mydate datetime)

insert into @mydates (mydate)
Select @date1
union
Select @date2
union
Select @date3
union
Select @date4


and join on @mydates

 

[PWise]

do a union

Select *
from yourtable
inner join @mydates mydates
on mydate = dob1
union
Select *
from yourtable
inner join @mydates mydates
on mydate = dob2

 

[PWise]

i guess that this table is about born again pepole

 

[1DMF]

I have hundreds of dates that need to be cross referenced against a main table for DOB1 & DOB2

I'm confused over your @mydates syntax , what parameter is @mydates?


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[PWise]

that is a table varible like a temp table

are these dates that the table has cross referenced against alwayes the same?

 

[1DMF]

I think I'm going to have to give up with T-SQL.

The data is spread across two SQL servers so I can't create the DOB list on the same SQL as the main table i wan't to search.

I've created a query in MS Access and used that as a table in another query via a join, but it's damn slow!

and that only performs the join against DOB1

I then need to get tbhe records that join against DOB2 , then somehow merge them to one recordset so i can then spit the result out to XLS!

This is becomming rather complicated, and it's needed in 30 minutes!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

I have a working solution in MS Access but it hurts to run it it is soooo sloooow.

I hope they don't want to run it too often!

I think i need to refactor it so there is a table that is populated with the DOB's which sits on the SQL server where the main table is and then perform a 'UNION ALL'

hey ho, talk about a crash course in SQL design!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[RTag]

You can link the servers and use "union" as proposed above

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[1DMF]

PWISE : Thank you , thank you , thank you!!!

In the end I created a new table on the SQL server that simply stores member DOB's and their membership number (this is now updated everytime a new member joins us as part of the 'make member' function).

i then used a stored procedure with a union all to merge the two results against DOB1 and DOB2 via a nSP using a passthrough query and OMG!!!

It's unbeleivably fast, this is totally awesome!

Since going back to school, listening to the kind folk here on TT and putting in some hard graft to do things differently my apps have come alive and now kick ass!

Thank you for your guidance and pointing me in the right direction.

RTag -> No, i wanted to but one is SQL2000 and the other is SQL2005 and they are both at the end of a poxy ADSL2 connection.

I will be moving tables around once our server is replaced in-house later this month when we get SBS2011 with SQL 2008 R2.

But one step at a time eh!


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta