February 1, 2013 at 9:37 am
Hi All
I want to create one table
Date,Doctor_id,Free_Slot
But in 1 day,there may be multiple free slots for one doctor,so how do i store them?
Please reply soon.
Thanks in advance!!!
February 1, 2013 at 10:11 am
sonikavitark
(2/1/2013)
Hi AllI want to create one table
Date,Doctor_id,Free_Slot
But in 1 day,there may be multiple free slots for one doctor,so how do i store them?
Without additional information, it is difficult to assist you.
a. Is the Free_Slot some time period (hours/minutes for example 1 hr 10 minuts) that is free on the date given ?
b. Is the Free_Slot a time span? That is the Free_Slot is from 8:15 AM to 9:25 AM ?
February 1, 2013 at 10:13 am
sonikavitark (2/1/2013)
Hi AllI want to create one table
Date,Doctor_id,Free_Slot
But in 1 day,there may be multiple free slots for one doctor,so how do i store them?
Please reply soon.
Thanks in advance!!!
It seems like maybe what you really need is an appointments table. You can figure out the open time slots using the gaps and islands logic. In other words, if there is no appointment, it is an open slot.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 1, 2013 at 10:15 am
I'd recommend you create a lookup table containing available slot times, and store your records for the main table with the FK to the slots table that point to them
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 1, 2013 at 11:04 am
You need to go thru the process of designing tables to store what you need, including a process called "normalization".
Under no circumstances should you attempt to store multiple values in a single column.
Typically you start the process by identifying "entities" (things) about which you need to store or use data.
Here we have at least these different entities:
doctors -- seems like you've already got a table for this
patients -- seems like you're aleady got a table for this(?)
appointments
doctor availability
Whether or not "time slot" or the equivalent is a separate entity is dependent on further details about your specific situation.
First list data you need about each entity.
Then use the "normalization" process to turn those entities into tables.
The only way to end up with a good design is to go thru a full design process. Try to short-cut this will just cost you much more time down the road.
With someone experienced to guide you, you could probably do this in a couple of days anyway.
Naturally with no experience it will be a much longer process. But don't try to skip it! It WILL burn you later if you do.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 1, 2013 at 12:27 pm
Presumably we're talking multiple time slots to multiple doctors?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 1, 2013 at 1:04 pm
I would think it would be multiple doctors, each of whom works a different schedule on different days of the week. Then they'd have their own appointments of varying lengths. There's not enough information here to even start. If you're going to try to do this in a single table, please ask more questions to save yourself significant pain later.
February 1, 2013 at 1:06 pm
It seems like maybe what you really need is an appointments table. You can figure out the open time slots using the gaps and islands logic. In other words, if there is no appointment, it is an open slot.
Your term "Gaps and Islands" is one I've never heard before. I've done gap analysis, but this describes it very well. Kudos and thanks!
February 1, 2013 at 1:24 pm
Ed Wagner (2/1/2013)
It seems like maybe what you really need is an appointments table. You can figure out the open time slots using the gaps and islands logic. In other words, if there is no appointment, it is an open slot.
Your term "Gaps and Islands" is one I've never heard before. I've done gap analysis, but this describes it very well. Kudos and thanks!
Not my term. I stole it from others who got it from...
At any rate here is an article that explains quite nicely how to group contiguous dates into islands. http://www.sqlservercentral.com/articles/T-SQL/71550/[/url]
Search for gaps and islands on this site and will find lots of people discussing this over and over. It is a common challenge that has a number of ways to solve it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply