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
 Need help with bed availability query

Author  Topic 

slydroe
Starting Member

17 Posts

Posted - 2014-02-02 : 02:01:25
Hi there, I was hoping someone could help me figure this out. I have a sql database of BEDS that can be occupied depending on whether the bed is available (end date is null). If the bed has an end date, the bed is out of commission and can no longer be occupied.
When a client occupies a room, it adds a row to a separate table 'BED_Detail' with information like bed_id, occupied_date, client_id etc. When the client checks out, it updates that row again with checkout_date.

While it's easy to count how many beds are currently available, I need to count the available beds on a past date (i.e.: July 1 2013). I can't see a way to do this since there's no table that holds information about the bed status on a daily basis.

There must be a wizard way to do this, and I hope someone can help :)
Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-02 : 02:17:56
So is the same bed record getting updated everytime it gets occupied and later becomes free?
Ideally you should do it like for every allocation you need to add a record with start date and later add an end dates once allocation is done.
If that being case, you can use logic like this


SELECT COUNT(*)
FROM (SELECT DISTINCT BedNo,BedName FROM BEDS)b
WHERE NOT EXISTS
(SELECT 1
FROM BEDS
WHERE BedNo = b.BedNo
AND @Date BETWEEN StartDate AND EndDate
)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

slydroe
Starting Member

17 Posts

Posted - 2014-02-02 : 04:10:50
The BED_Detail table (not the BEDS table) gets updated with every allocation. The same record gets updated with an enddate once the client checks out. Also, each bed record has a dept_key, which I'd like to filter in the query also. Could you pls tell me why I would use a 'where not exist' clause in this logic? Thanks!
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-02-02 : 06:07:19
Like you wrote yourself, there is no way to to find how many beds were available back in time, as the beds_detail is updated. But I see you are refering to a field named client_id. Is there table(s) with clients "booking" of the beds? Maybe this is the way to go.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-02 : 11:56:43
quote:
Originally posted by slydroe

The BED_Detail table (not the BEDS table) gets updated with every allocation. The same record gets updated with an enddate once the client checks out. Also, each bed record has a dept_key, which I'd like to filter in the query also. Could you pls tell me why I would use a 'where not exist' clause in this logic? Thanks!


sorry then its not possible as you're overwriting each time and not preserving history for each bed allocation.
DO you've an audit trigger for table atleast?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

slydroe
Starting Member

17 Posts

Posted - 2014-02-02 : 15:33:23
Yes I believe we have some audit system. I'll have to dig deeper into that. I don't know why, but I have this feeling that there's a solution to this. If I only look at the main BEDS table, it has a startdate and enddate. These dates mark when the bed became available and when it became out of commission in its whole lifetime. Can't I just say a date in the past and see if that date lands between the bed's lifetime date range and repeat the process for all the beds in the system?

These beds' lifetime date range is separate from Bed_Detail start and end dates which mark when the beds were occupied and unoccupied.

Bitsmed - there's another table that records the client ids when they get admitted to the program but it doesn't record the beds they use.

Thanks!
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-02-02 : 17:18:21
I think I didn't understand the task the first time - sorry.
Try this:
select count(*) as beds_available
from beds as b
where b.start_date<=@date
and (b.end_date is null
or b.end_date>=@date
)
and b.dept_key=@department_key
and not exists (select 1
from beds_detail as bd
where bd.bed_id=b.bed_id
and bd.occupied_date<=@date
and (bd.checkout_date is null
or bd.checkout_date>=@date
)
)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-03 : 04:31:50
quote:
Originally posted by slydroe

Yes I believe we have some audit system. I'll have to dig deeper into that. I don't know why, but I have this feeling that there's a solution to this. If I only look at the main BEDS table, it has a startdate and enddate. These dates mark when the bed became available and when it became out of commission in its whole lifetime. Can't I just say a date in the past and see if that date lands between the bed's lifetime date range and repeat the process for all the beds in the system?

These beds' lifetime date range is separate from Bed_Detail start and end dates which mark when the beds were occupied and unoccupied.

Bitsmed - there's another table that records the client ids when they get admitted to the program but it doesn't record the beds they use.

Thanks!



I cant really nderstand the purpose of each but just keep in mind that if you've a table with each record indicating statud of bed while it was occupied then you'll be able to use it to get your output
ie say you've Bed1 and you've three records in table as below
Bed Start End
Bed1 20140102 20140107
Bed1 20140109 20140115
Bed1 20140122 20140125

this means the bed got occupied only for above date ranges in Jan 2014 so based on what date you chose you'll be able to get whether bed was available at that time using logic like this

IF EXISTS(
SELECT 1
FROM BedTable
WHERE @Date BETWEEN Start AND EndDate-1
)
THEN
SELECT 'Occupied'
ELSE SELECT 'Not Occupied'

assuming checkout time was morning

so check for say 20140108 and you'll see there are no records overlapping and so bed was available
similarly for 20140113 you've an overlapping date range (20140109 20140115) and hence bed as not available etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

slydroe
Starting Member

17 Posts

Posted - 2014-02-04 : 13:08:17
Sorry for the delay… I tried the suggestions given, but it's not returning correct results. I have some physical records to compare to, and it's not even close. I think there are some work that needs to be done with the database like adding more determining fields on the bed table in order to track bed usage on a daily basis. I'll keep you guys posted. Thanks for all your help!
Go to Top of Page

slydroe
Starting Member

17 Posts

Posted - 2014-02-06 : 03:17:39
So I went into the tables and corrected a lot of wrong information in the beds table. Once I got those sorted out, bitsmed's query worked flawlessly, although I didn't need to use the 'and not exist' clause.

select count(*) as beds_available
from beds as b
where b.start_date<=@date
and (b.end_date is null
or b.end_date>=@date

Thanks to bitsmed and visakh16 for helping!
Go to Top of Page
   

- Advertisement -