Between 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)

Between question!

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-07-15 : 03:31:44

Hello I have a table


tbl_available
id_house
year
week1
week2
...
week52

id_house   year   week1  week2   week3    week4
34          2003    free   free    taken    free
34          2004    free   free    free     free
35          2003    taken  taken   free     free

Now I have a selection box where the can give the weeks when the want to go on a holliday.
Lets say between week1 and week2. So I want as result id_house 34.

And if I want between week 3 and week 4 I get as result also 34 and 35.

Thanxx
Bjorn

Edited by - neutcomp on 07/15/2003 03:41:34

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-15 : 05:03:12

declare @w1 int, @w2 int, @s varchar(1000)
set @s=''
set @w1=2 set @w2=7 --- start & end weeks

while @w1<=@w2
begin
set @s=@s+'week'+cast(@w1 as varchar(2))+'=''free'' and '
set @w1=@w1+1
end
set @s=left(@s, len(@s)-4)
set @s='select id_house from tbl_available where '+@s

exec (@s)

- Vit

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-07-15 : 05:13:01

quote:

declare @w1 int, @w2 int, @s varchar(1000)
set @s=''
set @w1=2 set @w2=7 --- start & end weeks

while @w1<=@w2
begin
set @s=@s+'week'+cast(@w1 as varchar(2))+'=''free'' and '
set @w1=@w1+1
end
set @s=left(@s, len(@s)-4)
set @s='select id_house from tbl_available where '+@s

exec (@s)

- Vit

Oke is it possible that access does not understand this code?
Its telling me:
Invalid SQL statement: Expected 'Delete','Insert','Procedure','Select' or 'Update'

And its selecting the "declare" in the SQL.
Thanxx
Bjorn

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-15 : 05:29:59

Mama mia!!
It has to be done for pure Access?

- Vit

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-07-15 : 05:32:47

Yes

I work with VB its going to be an webbased search machine.
I hope you now an other way. Is this query going to work with mysql?

Cya
Bjorn

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-15 : 05:49:09

I can't say nothing about MySQL, never saw it...
Again, I can't guess where it is going to run?
I can't produce an universal solution. :)

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-15 : 07:05:15

Paste this function into a standard module:

Function AvailableHouses(ByVal w1, ByVal w2, ByVal y)
If Not (w1 = "" Or w2 = "" Or y = "") Then
Dim s, ss, rs As DAO.Recordset, w11
s = "": ss = ""
w11 = CByte(w1)
While w11 <= CByte(w2)
s = s & "week" & w1 & "=" & Chr(34) & "free" & Chr(34) & " and "
w11 = w11 + 1
Wend
s = s & "year=" & y
s = "select id_house from tbl_available where " & s
Set rs = CurrentDb.OpenRecordset(s)
While Not rs.EOF
ss = ss & rs(0) & ", "
rs.MoveNext
Wend
If ss = "" Then
AvailableHouses = "No available houses at the time!"
Else
ss = Left(ss, Len(ss) - 2)
AvailableHouses = ss
End If
Else
AvailableHouses = ""
End If
End Function

Then place on your form 4 textboxes, named:

1) w1 - for start week
2) w2 - for end week
3) y - for year
4) AnyName with DataSource property
=AvailableHouses([Forms]![ff]![w1],[Forms]![ff]![w2],[Forms]![ff]![y])

Replace here 'ff' with your form name. In this 4th textbox will be shown
available houses IDs separated by comma.

- Vit

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-07-15 : 07:16:24

Great I am going to try this!!

Cya
Bjorn

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-07-15 : 08:21:00

It works Thanxxx, thanxx thanxxx

cya
Bjorn

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-15 : 09:52:43

Try normalization your database at some point, and queries like this become trivial.

tbl_available
---------
id_house
Week
[Available?]

primary key of (id_house, Week) and [Available?] can be a Yes/No checkbox.

then, you can also store much more data about that particular id_house/week combination, like who has it booked or whatever else you want, which you can't do with your current table structure.

and to query the table and make the results LOOK like you are storing them the old way, just use a cross-tab query.

- Jeff

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-07-15 : 12:15:49

Yes I can, because I have more tables.
tbl_house
tbl_reservation
etc.

So tbl_available is only a related table from tbl_house.

In your way where can you see then if the week 13 was free or not? You cant because you only have 1 colum week!

Cya
Bjorn

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-15 : 13:58:54

There is a field called "Week" on every row, which tells you which week that row is for. Actually, you would need a YEAR field as well, i forgot about that one, that would be part of the primary key.

Then, the data you posted :

id_house year week1 week2 week3 week4
34 2003 free free taken free
34 2004 free free free free
35 2003 taken taken free free

would be stored like this:

id_house, year, Week, [available?]
34,2003,1,yes
34,2003,2,yes
34,2003,3,no
34,2003,4,yes
34,2004,1,yes
34,2004,2,yes
34,2004,3,yes
34,2004,4,yes
35,2003,1,no
35,2003,2,no
35,2003,3,yes
35,2003,4,yes

this returns all free weeks for a house_id and year:

select * from
tbl_available
where
[available?] = True
house_id = 34 and
year = 2003

This returns all weeks that are available for all houses, by year:

select id_house, year, count(*)
from
tbl_available
[available?] = True
group by id_house, year

all houses that are available on week 12, year 2003:

select * from
tbl_available
where Week = 12 and
year = 2003
[Available?]=True

# of available houses per week:

select week, year, count(*)
from tbl_available
where [available?]= true
group by week, year

etc...

"My way" is the standard way of using relational databases. It's called normalization ... do some google searches for more information.

hopefully you can see it's a LITTLE more easier to get some data back if you structure the tables in a normalized fashion.

- Jeff

Edited by - jsmith8858 on 07/15/2003 14:08:41

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-07-15 : 16:12:51

Your way is indeed mutch easier.

Thanxx for the tip, I think I am going to change the structure.

Cya
Bjorn

P.s. if you have 100 houses you have 100 * 53 weeks = 5300 record. Is that not a problem? My way you have only 100 record!

Edited by - neutcomp on 07/15/2003 16:16:29

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-15 : 19:57:30

5300 is nothing, a very small count.

it is better to have a "long skinny" table with many rows and few columns, rather than a "short fat" table with many columns and few rows, in general.

you can also change your design even further, and create a table of "bookings" which has information about each booking -- who took the order, for how much, when it was done, who is booking it, etc ...

Bookings
--------
Booking_id (primary key)
Booked_By
DateTime
Amount
Notes
Order_Taken_By
...etc...

then a table of "booking details", which is the actual dates this booking is for:

Booking_Details
----------
*House_Id
*Year
*Week
Booking_Id

(* indicates the primary key fields.)

thus, if there is no row in the bookings_Details table for a house_id/year/week, then it is open. if there is an entry in the tabel, it is booked -- and you can link to the "bookings" table for information on who booked it, when, for how much, etc.

For reporting, all you need are the following helper tables:

Weeks
-----
*Week

Years
-----
*Year

where Weeks has week numbers from 1-52 in it, and Years has years from 2003 to whenever (say, 2099).

then, to show all house_ids for all weeks and all years:

select house_id, week, year
from houses, weeks, years

save that as a query, called "AllHousesWeeksYears". notice there are NO joins between the tables!! very unusual, this is called a "cross join" (note my title here in these forums

). that causes EVERY possible combination of all 3 tables to be produced in the output, which is what we want -- all possible combinations.

then, to see what is booked and what is open:

SELECT a.house_id, a.week, a.year,
b.Booking_ID
FROM
AllHousesWeeksYears a
LEFT OUTER JOIN
Booking_Details b
on
a.house_id = b.house_Id and
a.week = b.week and
a.year = b.year
WHERE Year = [some year]

this is a little advanced, but play around -- you can learn lots of cool techniques.

- Jeff

Edited by - jsmith8858 on 07/15/2003 19:59:50

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-07-30 : 04:17:11

I hope you can help me out AGAIN.

I have:


id     week	year	status         active
39	0	2003	aangemaakt	0
39	0	2004	aangemaakt	0
38	0	2003	aangemaakt	1
37	0	2003	aangemaakt	1
37	0	2004	aangemaakt	1
38	1	2003	aanvraag	0
38	2	2003	aanvraag	0
37	1	2003	aanvraag	0
37	2	2003	aanvraag	0
37	3	2003	bezet	        0
37	4	2003	bezet         	0

id = id_house

Now if someone search for a available house in week 1 and 2 year 2003.
The result must be notting!

aangemaakt = 'this house exist with this year'
aanvraag = 'someone has made a request to rent this house in this week.
bezet = 'We accepted his request so its taken'

So i want the id from the house, where the week 1 and 2 are available in the year 2003 and the active of the house must me 1.

If i switch on
39 0 2003 aangemaakt 0
To
39 0 2003 aangemaakt 1

The result must me 39.

I hope you understand and can help me out.

Thanxx
Bjorn

one more thing:
In ASP I make an variable addSQL where this is going to stand
AND week <> 1 AND week <> 2
Or if someone search for week 10 till 12
AND week <> 10 AND week <> 11 AND week <> 12

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-07-30 : 04:53:53

I am allready close to the solution I think:


SELECT DISTINCT id_huis
FROM tbl_beschikbaarheid
WHERE id_huis NOT IN
(
  SELECT c.id_huis
  FROM tbl_beschikbaarheid AS c  
  WHERE active = 1
  OR week = 1 
  OR week = 2
)

But the problem is.
If a house have year 2003 and 2004. And week 1 and 2 are still free in year 2004 the house is still not selected because its not available in 2003.

Hope i am allready at the finish line

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-30 : 05:16:48

select t.id from t
where

(select count(*) from t tt where tt.year=@year and
tt.week >= @week1 and tt.week <= @week2 and
active = 1) = @week2 - @week1 + 1

- Vit

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-07-30 : 05:35:11

Its a pitty that I cannot work with variables in Access.

But If I write your code like this:


select id_huis from tbl_beschikbaarheid
where
(
   select count(*) from tbl_beschikbaarheid 
   where jaar = 2003 
   and week >= 1 
   and week <= 2 
   and active = 1
) 
= (2 - 1) + 1

Its not going to work. And what about if the year doesn't mather?

Thanxx
Bjorn

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-30 : 06:03:10

Yes... I forgot a 'main' condition:

select t.id_huis from tbl_beschikbaarheid t
where
(
select count(*) from tbl_beschikbaarheid tt
where tt.jaar = 2003
and tt.week >= 1
and tt.week <= 2
and tt.active = 1
and tt.id_huis = t.id_huis
)
= (2 - 1) + 1

- Vit

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-07-30 : 06:15:34

[code]
select DISTINCT a.id_huis from tbl_beschikbaarheid AS a
where
(
select count(*) from tbl_beschikbaarheid As b
where b.jaar = 2003
and b.week >= 5
and b.week <= 6
and b.active = 1
and a.id_huis = b.id_huis
)
= (6-5) + 1
[/code]

This is for week 5 till 6. The result must be everything. But in your query the result is notthing.

I think you are forgetting that in the tbl there are only the booking weeks. and not every week from 1 till 52 (or 53).

Because your sub query gives 0 as result.

Bjorn

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-30 : 06:35:48

Your guess is right... I couldn't know this fact... :(

- Vit

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources