SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help with bed availability query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

slydroe
Starting Member

17 Posts

Posted - 02/02/2014 :  02:01:25  Show Profile  Reply with Quote
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!

Edited by - slydroe on 02/02/2014 02:15:06

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/02/2014 :  02:17:56  Show Profile  Reply with Quote
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 - 02/02/2014 :  04:10:50  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

359 Posts

Posted - 02/02/2014 :  06:07:19  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/02/2014 :  11:56:43  Show Profile  Reply with Quote
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 - 02/02/2014 :  15:33:23  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

359 Posts

Posted - 02/02/2014 :  17:18:21  Show Profile  Reply with Quote
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
                          )
                  )

Edited by - bitsmed on 02/02/2014 17:21:46
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/03/2014 :  04:31:50  Show Profile  Reply with Quote
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 - 02/04/2014 :  13:08:17  Show Profile  Reply with Quote
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 - 02/06/2014 :  03:17:39  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000