Hey Guys,
I am trying to create a web app that uses an SQL db to handle employee
scheduling. I can design a table to hold the appointments with no problems
but I need to be able to search for free time for the employees.
all bookings are based on 30 minutes blocks.
So far I managed to create all tables and this is what I have concerning the
booking tables "after seeing this post on
http://www.webservertalk.com/archiv...-9-1206387.html due to my lack
of DB design I am having a hard time understanding the solution.
eventually I need to be able to achieve the search using similar statement
to this
select min(starttime)
from schedule
where usedflag='free'
and duration >= :desired_duration
but I am having a problem with tables structure.
would someone explain the structure posted please?
thanks in advancesammy
It ishard to suggest soemthing without seeing the actual data and some DDL
Take a look at Itzik Ben-Gan's example showing the intreval connections to
the internet and resturns the connections was made less than 5 minutes
create table tblConnection
(
StartTimeCon datetime not null,
EndTimeCon datetime not null
)
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:00','20000610 10:10')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:20','20000610 10:22')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:23','20000610 10:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:27','20000610 10:45')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
11:57','20000610 12:00')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:01','20000610 12:04')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:04','20000610 12:40')
select * from tblConnection
SELECT
StartTimeCon,
ISNULL(
(SELECT MIN(EndTimeCon)
FROM tblConnection AS S3
WHERE S3.StartTimeCon >= S1.StartTimeCon
AND ISNULL(
DATEDIFF(
minute,
S3.EndTimeCon,
(SELECT MIN(StartTimeCon)
FROM tblConnection AS S4
WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
EndTimeCon) AS EndTimeCon
FROM tblConnection AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTimeCon)
FROM tblConnection AS S2
WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
"sammy" <sammy1971@.hotmail.com> wrote in message
news:ejgXXazbGHA.4672@.TK2MSFTNGP04.phx.gbl...
> Hey Guys,
> I am trying to create a web app that uses an SQL db to handle employee
> scheduling. I can design a table to hold the appointments with no problems
> but I need to be able to search for free time for the employees.
> all bookings are based on 30 minutes blocks.
> So far I managed to create all tables and this is what I have concerning
> the booking tables "after seeing this post on
> http://www.webservertalk.com/archiv...-9-1206387.html due to my lack
> of DB design I am having a hard time understanding the solution.
> eventually I need to be able to achieve the search using similar statement
> to this
> select min(starttime)
> from schedule
> where usedflag='free'
> and duration >= :desired_duration
> but I am having a problem with tables structure.
> would someone explain the structure posted please?
> thanks in advance
>
>|||Thanks for the response Uri,
what I am thinking of best way to to describe the data is like this. I will
create a table to hold sku's refering to the services to be booked the
skutable should be something like this
SKuID --PK
Description -- char
Durration -->30 minutes blocks number field where 1 = 30 and 2=60 and so on
The employee table should be like this
EmpID -->PK
Fname
Lname
location
ScheduleID -->FK
the scheduletable should be like this
SID -->PK
sundayStart --> ie 12/06/2006 9:30 AM
SundayEnd --> 12/06/2006 6:00 PM
MondayStart
MondayEnd
TuesdayStart
TuesdayEnd
WedStart
WedEnd
ThurStart
ThursEnd
FridayStart
FridayEnd
SaturedayStart
SatureDayEnd
what I am trying to achieve can be described best as calenday with all
employees, the workdays, starttime, endtime and the times they are available
to take calls "calls are based on 30 minutes blocks incremening by 30
minutes.
I hope that explains the issue better
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uij3ouzbGHA.536@.TK2MSFTNGP02.phx.gbl...
> sammy
> It ishard to suggest soemthing without seeing the actual data and some DDL
> Take a look at Itzik Ben-Gan's example showing the intreval connections to
> the internet and resturns the connections was made less than 5 minutes
> create table tblConnection
> (
> StartTimeCon datetime not null,
> EndTimeCon datetime not null
> )
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:00','20000610 10:10')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:20','20000610 10:22')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:23','20000610 10:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:27','20000610 10:45')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 11:57','20000610 12:00')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:01','20000610 12:04')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:04','20000610 12:40')
> select * from tblConnection
> SELECT
> StartTimeCon,
> ISNULL(
> (SELECT MIN(EndTimeCon)
> FROM tblConnection AS S3
> WHERE S3.StartTimeCon >= S1.StartTimeCon
> AND ISNULL(
> DATEDIFF(
> minute,
> S3.EndTimeCon,
> (SELECT MIN(StartTimeCon)
> FROM tblConnection AS S4
> WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
> EndTimeCon) AS EndTimeCon
> FROM tblConnection AS S1
> WHERE ISNULL(
> DATEDIFF(
> minute,
> (SELECT MAX(EndTimeCon)
> FROM tblConnection AS S2
> WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
>
>
> "sammy" <sammy1971@.hotmail.com> wrote in message
> news:ejgXXazbGHA.4672@.TK2MSFTNGP04.phx.gbl...
>|||sammy
Take a look at http://www.aspfaq.com/show.asp?id=2519
"sammy" <sammy1971@.hotmail.com> wrote in message
news:OdtVFi0bGHA.1208@.TK2MSFTNGP04.phx.gbl...
> Thanks for the response Uri,
> what I am thinking of best way to to describe the data is like this. I
> will create a table to hold sku's refering to the services to be booked
> the skutable should be something like this
> SKuID --PK
> Description -- char
> Durration -->30 minutes blocks number field where 1 = 30 and 2=60 and so
> on
> The employee table should be like this
> EmpID -->PK
> Fname
> Lname
> location
> ScheduleID -->FK
> the scheduletable should be like this
> SID -->PK
> sundayStart --> ie 12/06/2006 9:30 AM
> SundayEnd --> 12/06/2006 6:00 PM
> MondayStart
> MondayEnd
> TuesdayStart
> TuesdayEnd
> WedStart
> WedEnd
> ThurStart
> ThursEnd
> FridayStart
> FridayEnd
> SaturedayStart
> SatureDayEnd
> what I am trying to achieve can be described best as calenday with all
> employees, the workdays, starttime, endtime and the times they are
> available to take calls "calls are based on 30 minutes blocks incremening
> by 30 minutes.
> I hope that explains the issue better
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uij3ouzbGHA.536@.TK2MSFTNGP02.phx.gbl...
>
Showing posts with label hold. Show all posts
Showing posts with label hold. Show all posts
Thursday, March 22, 2012
Saturday, February 25, 2012
BLOB fields - questions
Hi,
Ok, I am pretty new to BLOB's on SQL SERVER 200 so I need some answeres.
1. Can BLOB Fields hold .doc and/or .pdf documents?
2. If yes, can the contents of the documents be searchable.
3. If documents are updated do BLOB Fields lengtht change/grow dynamically?
I would apreciate any answers in this matter and if possible examples were I can get a better idea on how to configure the BLOB fieds on my DB and/or documentation regarding BLOB's.
Thank you, Pepe.I am far from an expert in BLOB fields but here is my understanding of them. If there are any other gurus out there I will follow with interest as it is not something I have had cause to use/do yet.
1. Being Binary Large Objects I believe a BLOB field should be able to hold a pointer to a .doc or .pdf file.
2. I don't think you can search on the BLOB field as it is only a pointer to the actuall file. I seem to remember form somewhere though that you may be able to search them if it is full text indexed. (Don't quote me on that though)
3. As the table only stores a pointer to the BLOB the length of the field doesn't change when the file changes.
Sorry can't supply any examples for using or configuring BLOB fields|||If you want to learn more on this topic check the following link:
Brief tutorial on using text, ntext, and image data types (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp)|||Hi,
I need to upload .doc files to a BLOB field.
Does anyone know a way to do this? I guess I could use plain Insert commands from SQL Query Analyzer. Alternately, I want to use VB.NET to create an interface for the user to upload this .docs and to retrieve them.
Thank you and I would appreciate some examples if possible.
Pepe.|||ok, since you're saying whatever the website shows is not enough, here is what i came up with when i needed something similar to store various types of documents.
Ok, I am pretty new to BLOB's on SQL SERVER 200 so I need some answeres.
1. Can BLOB Fields hold .doc and/or .pdf documents?
2. If yes, can the contents of the documents be searchable.
3. If documents are updated do BLOB Fields lengtht change/grow dynamically?
I would apreciate any answers in this matter and if possible examples were I can get a better idea on how to configure the BLOB fieds on my DB and/or documentation regarding BLOB's.
Thank you, Pepe.I am far from an expert in BLOB fields but here is my understanding of them. If there are any other gurus out there I will follow with interest as it is not something I have had cause to use/do yet.
1. Being Binary Large Objects I believe a BLOB field should be able to hold a pointer to a .doc or .pdf file.
2. I don't think you can search on the BLOB field as it is only a pointer to the actuall file. I seem to remember form somewhere though that you may be able to search them if it is full text indexed. (Don't quote me on that though)
3. As the table only stores a pointer to the BLOB the length of the field doesn't change when the file changes.
Sorry can't supply any examples for using or configuring BLOB fields|||If you want to learn more on this topic check the following link:
Brief tutorial on using text, ntext, and image data types (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp)|||Hi,
I need to upload .doc files to a BLOB field.
Does anyone know a way to do this? I guess I could use plain Insert commands from SQL Query Analyzer. Alternately, I want to use VB.NET to create an interface for the user to upload this .docs and to retrieve them.
Thank you and I would appreciate some examples if possible.
Pepe.|||ok, since you're saying whatever the website shows is not enough, here is what i came up with when i needed something similar to store various types of documents.
Subscribe to:
Posts (Atom)