May 27, 2014 at 1:55 am
Hi,
I've One table that contains all current appointments booked as below.
RNappt_idclinic_idderived_location_idprovider_idappt_date_timeappt_date_endtime
1102186011435/27/14 8:305/27/14 9:00 AM
2102196011435/27/14 9:305/27/14 10:00 AM
3102206011435/27/14 10:305/27/14 11:00 AM
4102216011435/27/14 14:005/27/14 2:30 PM
5102226011435/27/14 16:005/27/14 4:30 PM
6102236011435/27/14 16:305/27/14 5:00 PM
for Same Clinic,location and Provider there's a schedule data for same day 24 hours
having every five mintues as below:
clinic_idderived_location_idprovider_idstart_timeendtimeunblock
601143 8:01 8:040
6011438:05 8:100
601143
60114313:0113:041
601143
60114313:5514:001
601143 1
60114317:5518:000
60114323:550:001
for rows having no start and endtime assume it as regular intervals.
So i need to show available appointment with duration one hour with the available schedule which is for every five minutes
Like My first appointment for today starts at 8:30 but 8- 8:30 is unblock so there could be an appointment but as this chunk is less than 60 so need to create it
For Schedule table below is what i've to create for temporary basis as this will be available in nightly load in a table.
DECLARE @num int=5
,@LASTtime TIME =CAST('23:55' as TIME)
,@Time TIME =CAST('00:00' as TIME)
,@Timeprev TIME =CAST('00:00' as TIME)
WHILE ( @Time<>@LASTtime)
BEGIN
SET @Time=CAST('00:00' as TIME)
SET @Time=DATEADD(MI,@num,@Time)
INSERT DoctorScheduleFortoday
(Clinic
,locationid
,ScheduleDate
,provider_id
,starttime
,endtime)
SELECT
601,
'01'
,CAST(GETDATE() AS DATE)
,43
,@Timeprev
,@Time
SET @num=@num+5
SET @Timeprev=@Time
END
update DoctorScheduleFortoday set Unitstatus=1 where starttime>='13:00'and starttime<='13:55'--scheduledate=GETDATE()+1--
update DoctorScheduleFortoday set Unitstatus=1 where (starttime>='00:00'and starttime<='08:00')
OR (starttime>='18:00'and starttime<='23:55')
Please suggest and let me know if some other info is needed
Thanks & Regards
May 27, 2014 at 3:50 am
How about creating VIEW? will it work?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 27, 2014 at 7:43 am
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
May 28, 2014 at 12:29 am
USE [Scratch]
GO
/*THIS TABLE WILL CONTAIN ALL BOOK APPOINTMENTS FOR A DOCTOR/PROVIDER WITH PARTICULAR CLINIC OF A LOCATION */
/* RELEVANT COLUMNS ARE Clinic_id,location_id,provider_id,appt_date_time,app_duration*/
/****** Object: Table [dbo].[AppointmentForNextTwentyDays_InProcess] Script Date: 05/27/2014 22:55:16 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AppointmentForNextTwentyDays_InProcess]') AND type in (N'U'))
DROP TABLE [dbo].[AppointmentForNextTwentyDays_InProcess]
GO
/****** Object: Table [dbo].[AppointmentForNextTwentyDays_InProcess] Script Date: 05/27/2014 22:55:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AppointmentForNextTwentyDays_InProcess](
[appt_id] [int] IDENTITY(1,1) NOT NULL,
[clinic_id] [smallint] NOT NULL,
[location_id] [char](2) NULL,
[provider_id] [smallint] NOT NULL,
[unique_id] [int] NOT NULL,
[appt_date_time] [datetime] NOT NULL,
[appt_column] [smallint] NOT NULL,
[appt_duration] [smallint] NULL,
[appt_confirmation] [char](1) NULL,
[appt_type] [char](2) NULL,
[appt_desc_line1] [char](28) NULL,
[appt_desc_line2] [char](28) NULL,
[appt_desc_line3] [char](28) NULL,
[appt_emp_create] [smallint] NULL,
[appt_emp_modify] [smallint] NULL,
[appt_phone] [char](13) NULL,
[appt_date_created] [datetime] NOT NULL,
[appt_date_modified] [datetime] NOT NULL,
[tp_uniquer] [int] NULL,
[derived_location_id] [char](2) NULL,
[unblock] [bit] NULL,
CONSTRAINT [pk_appointments] PRIMARY KEY CLUSTERED
(
[appt_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/*INSERT FOR SAME*/
/****** Object: Table [dbo].[AppointmentForNextTwentyDays_InProcess] Script Date: 05/27/2014 23:00:25 ******/
SET IDENTITY_INSERT [dbo].[AppointmentForNextTwentyDays_InProcess] ON
INSERT [dbo].[AppointmentForNextTwentyDays_InProcess] ([appt_id], [clinic_id], [location_id], [provider_id], [unique_id], [appt_date_time], [appt_column], [appt_duration], [appt_confirmation], [appt_type], [appt_desc_line1], [appt_desc_line2], [appt_desc_line3], [appt_emp_create], [appt_emp_modify], [appt_phone], [appt_date_created], [appt_date_modified], [tp_uniquer], [derived_location_id], [unblock]) VALUES (10218, 601, N'01', 43, 64620, CAST(0x0000A338008C1360 AS DateTime), 1, 30, NULL, N'H1', N'H 00 $0000 RECALL EXAM ', N'GM ', NULL, 910, 0, N'(562)305-1919', CAST(0x0000A32200000000 AS DateTime), CAST(0x0000A32200000000 AS DateTime), NULL, N'01', NULL)
INSERT [dbo].[AppointmentForNextTwentyDays_InProcess] ([appt_id], [clinic_id], [location_id], [provider_id], [unique_id], [appt_date_time], [appt_column], [appt_duration], [appt_confirmation], [appt_type], [appt_desc_line1], [appt_desc_line2], [appt_desc_line3], [appt_emp_create], [appt_emp_modify], [appt_phone], [appt_date_created], [appt_date_modified], [tp_uniquer], [derived_location_id], [unblock]) VALUES (10219, 601, N'01', 43, 2429, CAST(0x0000A338009C8E20 AS DateTime), 1, 30, NULL, N'H1', N'F 01 $0000 RCXM HYG 1ST ', N'AG ', NULL, 6, 0, N'(714)316-3920', CAST(0x0000A2DD00000000 AS DateTime), CAST(0x0000A2DD00000000 AS DateTime), NULL, N'01', NULL)
INSERT [dbo].[AppointmentForNextTwentyDays_InProcess] ([appt_id], [clinic_id], [location_id], [provider_id], [unique_id], [appt_date_time], [appt_column], [appt_duration], [appt_confirmation], [appt_type], [appt_desc_line1], [appt_desc_line2], [appt_desc_line3], [appt_emp_create], [appt_emp_modify], [appt_phone], [appt_date_created], [appt_date_modified], [tp_uniquer], [derived_location_id], [unblock]) VALUES (10220, 601, N'01', 43, 5647, CAST(0x0000A33800AD08E0 AS DateTime), 1, 30, NULL, N'H1', N'F 86 $0050 RECALL ', N'KG ', NULL, 910, 0, N'(949)533-4257', CAST(0x0000A32200000000 AS DateTime), CAST(0x0000A32200000000 AS DateTime), NULL, N'01', NULL)
INSERT [dbo].[AppointmentForNextTwentyDays_InProcess] ([appt_id], [clinic_id], [location_id], [provider_id], [unique_id], [appt_date_time], [appt_column], [appt_duration], [appt_confirmation], [appt_type], [appt_desc_line1], [appt_desc_line2], [appt_desc_line3], [appt_emp_create], [appt_emp_modify], [appt_phone], [appt_date_created], [appt_date_modified], [tp_uniquer], [derived_location_id], [unblock]) VALUES (10221, 601, N'01', 43, 122015, CAST(0x0000A33800E6B680 AS DateTime), 1, 30, NULL, N'H1', N'F 00 $0000 HYGIENE PATIENT ', N'RECALL ', N'EA ', 23, 0, N'(949)548-3096', CAST(0x0000A30900000000 AS DateTime), CAST(0x0000A30900000000 AS DateTime), NULL, N'01', NULL)
INSERT [dbo].[AppointmentForNextTwentyDays_InProcess] ([appt_id], [clinic_id], [location_id], [provider_id], [unique_id], [appt_date_time], [appt_column], [appt_duration], [appt_confirmation], [appt_type], [appt_desc_line1], [appt_desc_line2], [appt_desc_line3], [appt_emp_create], [appt_emp_modify], [appt_phone], [appt_date_created], [appt_date_modified], [tp_uniquer], [derived_location_id], [unblock]) VALUES (10222, 601, N'01', 43, 5625, CAST(0x0000A3380107AC00 AS DateTime), 1, 30, NULL, N'H1', N'F 01 $0000 RECALL EXAM ', N'GM ', NULL, 910, 0, N'(714)365-6755', CAST(0x0000A32200000000 AS DateTime), CAST(0x0000A32200000000 AS DateTime), NULL, N'01', NULL)
INSERT [dbo].[AppointmentForNextTwentyDays_InProcess] ([appt_id], [clinic_id], [location_id], [provider_id], [unique_id], [appt_date_time], [appt_column], [appt_duration], [appt_confirmation], [appt_type], [appt_desc_line1], [appt_desc_line2], [appt_desc_line3], [appt_emp_create], [appt_emp_modify], [appt_phone], [appt_date_created], [appt_date_modified], [tp_uniquer], [derived_location_id], [unblock]) VALUES (10223, 601, N'01', 43, 5626, CAST(0x0000A338010FE960 AS DateTime), 1, 30, NULL, N'H1', N'F 01 $0000 RECALL EXAM ', N'GM ', NULL, 910, 0, N'(714)365-6755', CAST(0x0000A32200000000 AS DateTime), CAST(0x0000A32200000000 AS DateTime), NULL, N'01', NULL)
SET IDENTITY_INSERT [dbo].[AppointmentForNextTwentyDays_InProcess] OFF
GO
/*THIS TABLE IS SCHEDULE FOR SAME DOCTOR FOR PARTICULAR CLINIC AND LOCATION FOR NEXT 21DAYS,WHICH ARE 5 MIN SLOTS, BUT I WILL INSERT FOR ONLY ONE DAY*/
/*AS ABOVE APPT FOR 27 MAY 2014, SO WE WILL CREATE SCHEDULE FOR DATE 27 MAY 2014 ONLY*/
/*THIS WILL BE PROVIDED IN TXT FILE WHICH AN NIGHTLY SSIS PACKAGE WILL LOAD TO TABLE*/
/*I AM JUST GIVING DEMO OF RECORD LOOK LIKE IN THIS TABLE*/
/****** Object: Table [dbo].[DoctorScheduleFortoday] Script Date: 05/27/2014 23:02:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoctorScheduleFortoday]') AND type in (N'U'))
DROP TABLE [dbo].[DoctorScheduleFortoday]
GO
/****** Object: Table [dbo].[DoctorScheduleFortoday] Script Date: 05/27/2014 23:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DoctorScheduleFortoday](
[Clinic] [smallint] NULL,
[locationid] [char](2) NULL,
[ScheduleDate] [date] NULL,
[provider_id] [smallint] NULL,
[Column] [smallint] NULL,
[UnitNumber] [int] NULL,
[UnitStatus] [bit] NULL,
[starttime] [varchar](10) NULL,
[endtime] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/*[UnitStatus] [bit] NULL, column will show slots for five mintues available or not for one means block and for 0 means unblock*/
DECLARE @num int=4
,@LASTtime TIME =CAST('23:59' as TIME)
,@Time TIME =CAST('00:00' as TIME)
,@Timeprev TIME =CAST('00:00' as TIME)
WHILE ( @Time<>@LASTtime)
BEGIN
SET @Time=CAST('00:00' as TIME)
SET @Time=DATEADD(MI,@num,@Time)
INSERT DoctorScheduleFortoday
(Clinic
,locationid
,ScheduleDate
,provider_id
,starttime
,endtime)
SELECT
601,
'01'
,CAST(GETDATE() AS DATE)
,43
,@Timeprev
,@Time
SET @num=@num+5
SET @Timeprev=DATEADD(MI,1,@Time)
END
update DoctorScheduleFortoday set Unitstatus=1 where starttime>='13:00'and starttime<'14:00';--scheduledate=GETDATE()+1--
update DoctorScheduleFortoday set Unitstatus=1 where (starttime>='00:00'and starttime<'08:00')
OR (starttime>='18:00');
update DoctorScheduleFortoday set Unitstatus=0 where UnitStatus is null;
/*We need to Create Slots using Schedule Table for each slot 60 min duration but keeping in mind that schedule should not be booked earlier*/
/*THIS [AppointmentForNextTwentyDays_InProcess] WILL BE POPULATED FROM MAIN DATAMINER TABLE EVERY 5 MIN FOR NEXT 21 DAYS*/
/*AVAILABLE SLOTS CAN BE INSERTED IN THIRD TABLE */
/****** Object: Table [dbo].[AvailableAppointmentSlots] Script Date: 05/27/2014 23:25:47 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AvailableAppointmentSlots]') AND type in (N'U'))
DROP TABLE [dbo].[AvailableAppointmentSlots]
GO
/****** Object: Table [dbo].[AvailableAppointmentSlots] Script Date: 05/27/2014 23:25:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AvailableAppointmentSlots](
[appt_id] [int] IDENTITY(1,1) NOT NULL,
[clinic_id] [smallint] NOT NULL,
[location_id] [char](2) NULL,
[provider_id] [smallint] NOT NULL,
[unique_id] [int] NOT NULL,
[appt_startdate_time] [datetime] NOT NULL,
[appt_enddate_time] [datetime] NOT NULL,
[Isavailable] [bit] NULL,
CONSTRAINT [pk_appointments1] PRIMARY KEY CLUSTERED
(
[appt_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Please let me know if there something missing in the info provided now.
May 28, 2014 at 7:11 am
Nice job posting ddl and consumable data. I just don't have a clue what you want to do with this. We have two tables of data (AppointmentForNextTwentyDays_InProcess and DoctorScheduleFortoday). What is the desired output and what are the rules?
_______________________________________________________________
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/
May 29, 2014 at 6:05 am
Appreciated for your reply and letting me know .
Below Are Business Rules...
Target- To Show available slots for Doctor/provider for a clinic location
1) Lets Says today is 27 May 2014 and AppointmentForNextTwentyDays_Inprocess Contains Booked Appointment for today , which is for clinic 601 and location '01' and for provider or Doctor = 43.
having slots like
appt_date_time
09:30
10:00
13:00
15:00
16:00
16:30
2) We need to create available slots in between with minimum duration of 60 min only.
3) for this there is a DoctorScheduleFortoday for today table mentioning availability for that doctor of that clinic
for each five minutes
like
starttime
00:00
00:05
4) for example moring 00:00 to 7:55 starttime and 18:00 to 23:59 in this table unitstatus=1 which means it is not available in this buffer so there can not be any slots in this buffer
and there is lunck break from 13:00 to 14:00 so all these slots also block and unitstatus=1 for this also..
so you have 8-13:00 and 14:00-18:00 to create one hour each slots but before creating slots from this schedule table it should be checked that there should be no booked appointment in this duration.
like your first slot 8-9 there is no booked appointment so this is available and is available in for booking
so your next starting time will be 9-10 but we have booked appointment in this at 9:30 so this slot is not available
but for next slots to be created for availability starttime will be after 9:30 appointment duration completion which for 30 mintues so it will be 10:00
and soon
though i've created cursor for same and sending you right away but i want to achieve this with
some select query for which i am rolling with ideas with all darkness.
USE Scratch
GO
SET NOCOUNT ON
GO
TRUNCATE TABLE [Scratch].[dbo].[AvailableAppointmentSlots];
DECLARE @clinic_id int=NULL
,@location_id char(2)=NULL
,@provider_id smallint=NULL
,@schedule date=NULL
DECLARE C CURSOR FAST_FORWARD FOR
SELECT DISTINCT clinic,locationid,provider_id,scheduledate
FROM DoctorScheduleFortoday
ORDER BY clinic,locationid,provider_id,scheduledate
OPEN C
FETCH NEXT FROM C INTO @clinic_id,@location_id,@provider_id,@schedule
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @a time=null
,@maxtime time=null
,@b time=null
,@scheduledate datetime=CAST(@schedule AS DATETIME)
SELECT @a=MIN(starttime),@maxtime=DATEADD(MI,1,MAX(endtime))
FROM DoctorScheduleFortoday
WHERE UnitStatus=0
AND
Clinic=@clinic_id and locationid=@location_id and provider_id=@provider_id and ScheduleDate=@schedule
--SET @a=CAST('17:00' AS TIME)
SET @b-2=DATEADD(MI,60,@a)
WHILE (@a<@maxtime)
BEGIN
Checkone:
IF(SELECT COUNT(1) FROM DoctorScheduleFortoday WHERE UnitStatus=1 AND
Clinic=@clinic_id and locationid=@location_id and provider_id=@provider_id and ScheduleDate=@schedule
AND
starttime>=@a AND starttime<@b and @a<@maxtime and @b-2<=@maxtime )>0
BEGIN
SELECT @a= MIN(starttime) FROM DoctorScheduleFortoday WHERE UnitStatus=0 AND starttime>@a AND starttime<=@b
IF(@a=DATEADD(MI,-60,@b) OR (@a IS NULL))
SET @a=@b
SET @b-2=DATEADD(MI,60,@a)
GOTO Checkone
END
ELSE
IF(@a<@maxtime )
BEGIN
INSERT INTO [Scratch].[dbo].[AvailableAppointmentSlots]
([clinic_id]
,[location_id]
,[provider_id]
,[appt_startdate_time]
,[appt_enddate_time]
,[Isavailable])
SELECT DISTINCT clinic_id,derived_location_id,provider_id
,appt_date_time
,DATEADD(MI,appt_duration,appt_date_time)
,0
FROM AppointmentForNextTwentyDays_Inprocess
WHERE
provider_id=@provider_id
and clinic_id=@clinic_id
and derived_location_id=@location_id
and appt_date_time >=CAST(@schedule AS DATETIME)
and appt_date_time<CAST(DATEADD(D,1,@schedule) AS DATETIME)
and CAST(appt_date_time AS TIME)>=@a and CAST(appt_date_time AS TIME)<@b
IF(@@ROWCOUNT=0 AND @b-2<=@maxtime)
BEGIN
INSERT INTO [Scratch].[dbo].[AvailableAppointmentSlots]
([clinic_id]
,[location_id]
,[provider_id]
,[appt_startdate_time]
,[appt_enddate_time]
,[Isavailable])
SELECT 601
,'01'
,43
,@scheduledate+@a
,@scheduledate+@b
,1
SELECT @a=DATEADD(MI,0,@b)
SELECT @b-2=DATEADD(MI,60,@a)
--SELECT @a,@b
GOTO Checkone
END
ELSE
BEGIN
SELECT @a=DATEADD(MI,0,MAX(CAST(DATEADD(MI,appt_duration,appt_date_time) AS TIME)))
FROM
AppointmentForNextTwentyDays_Inprocess
WHERE
provider_id=@provider_id
and clinic_id=@clinic_id
and derived_location_id=@location_id
and appt_date_time >=CAST(@schedule AS DATETIME)--CAST(GETDATE()-1 AS DATE)
and appt_date_time<CAST(DATEADD(D,1,@schedule) AS DATETIME)
and CAST(appt_date_time AS TIME)>=@a
and CAST(appt_date_time AS TIME)<@b
SELECT @b-2=DATEADD(MI,60,@a)
--SELECT @a,@b
GOTO Checkone
END
END
END
FETCH NEXT FROM C INTO @clinic_id,@location_id,@provider_id,@schedule
END
CLOSE C
DEALLOCATE C
SELECT * FROM AvailableAppointmentSlots
/*Above is select is my desired Output*/
if we can achieve this something like given in below URL
http://www.manning.com/nielsen/SampleChapter5.pdf/
will be appreciated
🙂
Thanks & Regards
June 3, 2014 at 12:13 pm
Any suggestions.
June 4, 2014 at 12:54 am
There must be some other technique for this but from what i understand, i come up with below code..
;WITH CTE AS(
SELECT DATEPART(HH,starttime)S,COUNT(*)cnt,ScheduleDate,MIN(starttime)start, DATEADD(mi,1,MAX(endtime))endt
FROM [DoctorScheduleFortoday] WHERE UnitStatus = 0 GROUP BY ScheduleDate,DATEPART(HH,starttime)
HAVING COUNT(*) = 12
)
INSERT AvailableAppointmentSlots
(
[clinic_id],
[location_id],
[provider_id],
--[unique_id],
[appt_startdate_time],
[appt_enddate_time],
[Isavailable]
)
SELECT DISTINCT Clinic,
locationid,
provider_id,
CONVERT(VARCHAR(30), DS.ScheduleDate) + ' ' + start,
CONVERT(VARCHAR(30), DS.ScheduleDate) + ' ' + endt,
1
FROM CTE C
LEFT JOIN [DoctorScheduleFortoday] DS ON DATEPART(HH, DS.starttime) = C.S AND C.ScheduleDate = DS.ScheduleDate
SELECT * FROM AvailableAppointmentSlots
July 24, 2014 at 6:38 pm
I am not sure how well I follow as there are details being lost in translation that I am not getting. It seems though you could benefit from a Calendar table with a low level of granularity, say one row for every hour all time, or for every half hour. You can add attributes that you think would be helpful such as , <off hour>. It seems your solution would be not one that could adjust very well to changes in business logic. Try practising what Sean said earlier about presenting consumable data and only what we need to understand the problem, so we dont have to sift through unneccessary detail. Just my opinion on this post. I am sure you would want someone to present a clear answer, we need you to present your problem clearly as well.
----------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply