checking for availability - 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

General SQL Server Forums

New to SQL Server Programming

checking for availability

Author

Topic

christina_rules
Starting Member

23 Posts

Posted - 2007-05-22 : 22:50:32

i'm not fluent with sql and i have a question to ask..

if i want to check whether a product is available between two dates, how can i do so? for example, i want to check whether it's available between 5/23/2007 to 5/25/2007..

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-22 : 22:55:12

[code]
where datecol is between '20070523' and '20070525'
[/code]

christina_rules
Starting Member

23 Posts

Posted - 2007-05-22 : 23:01:36

what if it's like i have a fetch_date and return_date..then i want to check my database to return the results that allow users to use the product on 5/23/2007 to 5/25/2007?

hey001us
Posting Yak Master

185 Posts

Posted - 2007-05-22 : 23:13:29

you need to check both date fields as below:
where (fetch_date between '20070523' and '20070525' or return_date between '20070523' and '20070525')

hey

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-22 : 23:18:46

you wanted both fetch_date and return_date must falls in between 5/23/2007 and 5/25/2007 ?

christina_rules
Starting Member

23 Posts

Posted - 2007-05-23 : 09:03:36

thanks khtan and hey001us..your replies help

christina_rules
Starting Member

23 Posts

Posted - 2007-05-29 : 22:50:23

erm..i'm having problem retrieving what i want..i want to retrieve results that will show that the product is available but in my table my fetch_date and return_date means that the product is reserved..what i tried was:

select prod_name from product_table where prod_id not in(select prod_id from prod_reserve_table where (fetch_date between '20070525' and '20070529') or (return_date between '20070525' and '20070529'))

one more thing, must we use '20070525' and not '5/25/2007'? because i've been using the later format and it seems like it doesn't affect anything but i might be wrong

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-30 : 00:04:26

please post some data and required output

christina_rules
Starting Member

23 Posts

Posted - 2007-05-30 : 00:13:12

let's say in my prod_reserve_table has following data

prod_reserve_name | fetch_date | return_date | prod_id |
_________________________________________________________
A | 5/25/2007 | 5/31/2007 | 1 |
B | 5/23/2007 | 6/30/2007 | 2 |
C | 5/28/2007 | 5/29/2007 | 1 |

and now, i want to check whether product C is available on 5/25/2007 to 5/29/2007..and the output i want is that it is NOT available since the product has previously being reserved and will only be returned on 5/29/2007

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-30 : 00:18:07

[code]Select p.prod_name
from product_table p
LEFT JOIN
(Select prod_id from prod_reserve_table
where fetch_date between '20070525' and '20070529' and return_date between '20070525' and '20070529') t
on p.prod_id = t.Prod_id
Where t.Prod_ID IS NULL[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-30 : 00:49:48

declare @t table(prod_reserve_name char(1), fetch_date datetime, return_date datetime, prod_id int)
insert @t
select 'A','5/25/2007','5/31/2007',1 union
select 'B','5/23/2007','6/30/2007',2 union
select 'C','6/28/2007','6/30/2007',1

Select Availability = Case when count(*) > 0 Then 'Available' else 'Not available' end from @t
where prod_reserve_name = 'C' and
'5/25/2007' not between fetch_date and return_date and
'6/27/2007' not between fetch_date and return_date

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-30 : 04:33:52

SELECT prod_id FROM prod_reserve_table
WHERE fetch_date >= '20070525' and return_date <= '20070529'

I think this is what you are looking for.

Necessity is the mother of all inventions!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources