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 thisSELECT COUNT(*)FROM (SELECT DISTINCT BedNo,BedName FROM BEDS)bWHERE NOT EXISTS(SELECT 1 FROM BEDS WHERE BedNo = b.BedNo AND @Date BETWEEN StartDate AND EndDate) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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! |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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! |
|
|
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 ) ) |
|
|
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 outputie say you've Bed1 and you've three records in table as belowBed Start EndBed1 20140102 20140107Bed1 20140109 20140115Bed1 20140122 20140125this 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 thisIF EXISTS(SELECT 1FROM BedTableWHERE @Date BETWEEN Start AND EndDate-1)THEN SELECT 'Occupied'ELSE SELECT 'Not Occupied' assuming checkout time was morningso check for say 20140108 and you'll see there are no records overlapping and so bed was availablesimilarly for 20140113 you've an overlapping date range (20140109 20140115) and hence bed as not available etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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! |
|
|
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>=@dateThanks to bitsmed and visakh16 for helping! |
|
|
|