Search engine question - 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

SQL Server Development (2000)

Search engine question

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-08-05 : 03:34:03

Hello,

I am making a search engine for my database.
I have a


tbl_available
id_house   week     year
40          0        2003
40          0        2004
40          13       2003
40          14       2003
40          24       2003
40          25       2003
40          26       2003
41          0        2003
42          0        2003
43          0        2004
43          14       2004

The week 0 is so I know that this year is available for this house.

Now I want to search for available houses.
Let say:
week 12 and 13 no year result = 40, 41, 42, 43 (40 because the year 2004 is still available)
week 13 and 14 no year result = 40, 41, 42
week 12 and 13 year 2003 result = 41, 42, 43
week 12 and 13 year 2003 result = 41, 42
week 14 year 2004 result = 40

I think this should be two seperated query's one with the year and one without the year. This is no problem because I run this query in ASP with an Access database. So I cannot use the variables @.

Thanxx for the help
Bjorn

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-05 : 06:18:50

Bjorn,

plz give more detailed description of what you seek for.
Are those weeks always coupled or not? What mean 0 week value?

- Vit

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-08-05 : 06:27:37

Sorry I forgot to tell.

the week 0 means this house is available in the year .....
If week 13 year 2003 means that this house is allready booked so its not available.

I want the available houses from a selected week till end of the week. And optional is to give a selected year.

I hope this is enough information?

Thanxx
Bjorn

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-05 : 07:10:07

>from a selected week till end of the week
You mean "year"?

Well, anyway it seems not a simple thing to do. In fact, you want
to get lists of available houses for each possible succesive combination
of week numbers (let's say we start from 20th week):


20            >> here the list of houses for this weeks combination
20 21         >> here the list of houses for this weeks combination
20 21 22      >> (and so on)
.... ....
21
21 22
21 22 23
21 22 23 24
.... ....
22
22 23
22 23 24
.... ....

- Vit

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-08-05 : 07:17:58

Oke lets say you want to go on a vacation.
You have 3 weeks of from you bose. Week 23 and 24.
So you go to the search engine and you fill in start week 23 end week 24. If you want you can make your search option smaller by also filling in the year 2003.

Now if you press search you want all available houses in the week 23 and 24 in the year 2003!

Hope you can help me out
Bjorn

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-05 : 07:55:37

OK, Bjorn, I'll think it over.

>can make your search option smaller by also filling in the year 2003

Does it mean that the man doesn't know in what year he's going to...?

- Vit

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-08-05 : 08:01:40

If you want to go from week 52 till next year week 1 you cannot fill in a year. thats why you can enter a year is an optional function.

The weeks I can make the sql like this:

For x = 0 To (weekEnd - weekStart) Step 1
  If x = 0 Then
    addSQL = addSQL & " AND week = "& weekStart + x
  Else
    addSQL = addSQL & " OR week = "& weekStart + x
  End If
Next

The result can be:
And week = 23 or week = 24 or week = 25

And the year I have made something like this:

If jaar <> "" Then addSQL = addSQL & " AND year <> 2003

The result:
And week = 23 or week = 24 or week = 25 and year <> 2003

Ofcours I can change the <> AND OR = etc.

Cya
Bjorn

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-05 : 08:51:33

Now I see what you mean. But your sample case can be easily
splitted into two queries. The 1st will seek for houses available
from 52nd week till 52nd week of 2003 year. And the 2nd query will
seek for houses available from 1st week till 2nd week of 2004 year.
Then we can select from results of these queries only those houses
that are present in results of both queries.
Pity I have no much spare time at the moment. This my query below
is for that simple case of searching within one year only.

SELECT id_house

FROM tbl_available

WHERE
([week]=0 or ([week]>=forms!ff!w1 and [week]<=forms!ff!w2))
AND
[year]=forms!ff!y

GROUP BY id_house

HAVING (count([week])=1+forms!ff!w1-forms!ff!w2) OR (min([week])=0);

- Vit

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-08-05 : 12:51:49

It works allready pretty good i think but not yet 100%
take a look at www.provencemaison.com/index.asp

Houses -> search...there is the query if you insert a date and press search.

I will get back tomorrow....and tell you what is going wrong.

Cya
Bjorn

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-05 : 13:40:50

Great!!
Pity those nice houses are not for me. :)

I copied this select from the frame:

SELECT id_huis FROM tbl_beschikbaarheid WHERE(week=0 or (week >= 7
and week <= 9)) AND jaar = 2003 GROUP BY id_huis HAVING (count(week)
= 1+7-9) OR min(week) = 0

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-05 : 14:21:40

Of course, it's my fault >> HAVING (count(week) = 1+7-9) OR min(week) = 0

Well... just swap w1 and w2 in HAVING clause... Then we'll test it. This must
work within a year. I think that by the 50th week of the year we make it OK.

- Vit

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-05 : 14:27:21

Site is very fast...

Where are you storing the images?

Brett

8-)

SELECT POST=NewId()

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-05 : 14:38:42

Bjorn will be back tomorrow. :)

- Vit

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-08-05 : 16:00:51

I am allready back :-)

I will swap the weekStart and weekEind.
I am storing the images just as a gif on the webserver of
www.vevida.nl I have verry good experience with this company.

The support almost everything and the are quick in responding on your questions. And that for only 34,95 a year.

Cya
Bjorn

I have a view of the tbl_available
http://www.provencemaison.com/available.asp

Its polluted because I just the Refresh button a view time

But you can still see what result you have to have in the search engine.

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-08-06 : 04:08:30

If I want to go on holliday the hole year!

SELECT id_huis 
FROM tbl_beschikbaarheid 
WHERE(week=0 or (week >= 1 and week <= 53)) 
AND jaar = 2004 
GROUP BY id_huis 
HAVING (count(week) = 1+53-1) 
OR min(week) = 0

I get back id: 40, 41, 44
But week 40 is not available the hole year so it looks like the query does not check if the week 1 till 52 is available.

1) I select all houses where status = 1
2) minus the houses that are not available in the selected year
3) check if the given date is still free.

I think this should cover the total question.

Thanxx
Bjorn

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-06 : 05:27:13

Ah, Bjorn, something wrong with me in these days... I forgot that week>0
means the house is UNavailable for this week. So, test this:

SELECT id_huis
FROM tbl_beschikbaarheid
WHERE
jaar = 2004
AND
NOT EXISTS(select 0 from tbl_beschikbaarheid where week>=1 and week<=53)

- Vit

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-08-06 : 05:43:56

Oke I have done that but now if 1 house is not available in the week we select it shows notting!

It pretty hot so I think that has something to do with it

Can you explain what is 0 means in:

select 0 
from tbl_beschikbaarheid 
where week>=8 
and week<=13

cya
Bjorn

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-06 : 05:59:37

Heh... here 0 means... nothing. You may replace it with
what you like (e.g. 'hello!!') or with a column name.
It is just an indicator for is at least one record in
query result or not?
Well, I see what I missed:

SELECT id_huis
FROM tbl_beschikbaarheid
WHERE
jaar = 2004
AND
NOT EXISTS(select 0 from tbl_beschikbaarheid as tt where tt.week>=1 and tt.week<=53 and tt.id_huis=tbl_beschikbaarheid.id_huis)

- Vit

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-08-06 : 06:12:56

Oke its almost for the 100% good. But if I search for 2004 and for week 14 till 15 it does not show house 40. Because week 14 and 15 is not available in the year 2003.

So if this is fixed then its works for 100% if i am not mistaken..

Thanxx
Bjorn

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-06 : 06:38:13

AAAAAAAAAAAHHHHHHHHHHHHHHHHHHHHHH!!!!!!!!!!!!!!!!!!!!!!!!

SELECT id_huis
FROM tbl_beschikbaarheid
WHERE
jaar = 2004 AND NOT(week >= 1 AND week <= 53)

I'm a wreck...

- Vit

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-08-06 : 06:46:43

Great,

If you where a girl I would

But you are not but you get the point.

Now the website is almost finished....

thanxx to your help.

Cya
Bjorn

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources