February 16, 2009 at 3:47 pm
Okay, usually I can find some sort of answer here before I have to post. This time, however, I've looked and looked and looked and can't figure it out. Here's the deal:
I have to build an appointment calendar based on an existing table with the following fields: Date and time. The StartTime and the StopTime are in separate rows of the same table, so I can extrapolate the following data: Date, StartTime, StopTime, and Increments (number of 15 minute increments between the start time and the stop time). It will look something like this:
StartTime StopTime Date Increments
6:30 AM4:00 PM2009-01-27 00:00:0038
Ideally, what I'd like to come up with is a query that returns a row with 64 columns for each date. So, if a user inputs a date range of 02/01/2009 through 02/07/2009, they would get seven rows with columns for 6:00am, 6:15am, 6:30am...9:30pm, 9:45pm, 10:00pm. That way I could loop through the results in ASP (classic) and display the results for each row (or blank if that column is null).
Does that make sense? Does anyone have any ideas or am I giving SQL 2000 more credit than it deserves? Any help would be appreciated, my client is tired of waiting for me to figure it out on my own.
Thanks a million!
Sherm
February 16, 2009 at 4:02 pm
I'm not sure what you mean with 64 columns. Are you saying a column for each 15 minute segment of a day?
If you're looping in ASP, why not track the segments there, loop from the start time until the end time and display what needs to be shown?
It's not really clear what you want from the Db.
February 16, 2009 at 4:12 pm
Steve Jones - Editor (2/16/2009)
I'm not sure what you mean with 64 columns. Are you saying a column for each 15 minute segment of a day?
Exactly, I want 1 column for each 15 minute segment. Sorry, I should have been more clear about that.
Steve Jones - Editor (2/16/2009)
If you're looping in ASP, why not track the segments there, loop from the start time until the end time and display what needs to be shown?
Maybe my ASP skills are worse than my SQL skills, but every attempt I've made at doing just that has ended in failure. I was hoping to figure out a way to just write a query with temp tables (or cursors) that would allow me to return all 64 columns to my ASP page. Again, I may be way out in left field on this, but I thought it would be worth a try.
February 16, 2009 at 4:54 pm
Are you sure you want 64 columns in the resultset?
I think it might be simpler to return 1 row per 15 minute slot, i.e. 64 rows per day.
Also, do you want to flag which 15-minute slots are available for booking and which are already booked?
Would a resultset with the following fields be acceptable to you?
SlotNumber int [1 - 64]
StartTime datetime
EndTime datetime
Available int [0=booked, 1=free]
Can you provide the full schema for your Appointments table.
February 16, 2009 at 5:29 pm
Pity you're using SQL2000 otherwise you could probably pivot the data.
Edit: Doh! http://www.sqlservercentral.com/scripts/Miscellaneous/31719/
Max
February 16, 2009 at 5:30 pm
andrewd.smith (2/16/2009)
Are you sure you want 64 columns in the resultset?I think it might be simpler to return 1 row per 15 minute slot, i.e. 64 rows per day.
No, I'm not sure. 🙂 The problem is that I want to be able to:
a) Differentiate one day's appointment from the next in a manner that is easy to parse, and
b) Return a row/column for each segment.
The problem, for me, is that even though I know there are 5 "segments" in an appointment that starts at 10:00am and 11:15am, I can't figure out how to "bookmark" a spot in the calendar for each segment.
andrewd.smith (2/16/2009)
Also, do you want to flag which 15-minute slots are available for booking and which are already booked?
My plan was to check for an appointment in a given slot to determine whether it is available for booking, but a flag would be acceptable.
andrewd.smith (2/16/2009)
Would a resultset with the following fields be acceptable to you?SlotNumber int [1 - 64]
StartTime datetime
EndTime datetime
Available int [0=booked, 1=free]
Yes.
andrewd.smith (2/16/2009)
Can you provide the full schema for your Appointments table.
Here's the problem: I think that I would be introducing unwanted complications by providing the full schema. The database was built by someone else and it is a complex database that doesn't make sense at first look. For example, I don't actually have an Appointments table. The data is actually stored in a table called MatterField in which each *row* contains the value of a field (plus additional info). The query that I'm using to get the data I posted earlier is as follows:
SELECT
START.Value AS StartTime,
STOP.Value AS StopTime,
MT.StopProjected AS ApptDate,
DATEDIFF(mi, START.Value, STOP.Value)/15 AS Increments
FROM MatterField START
INNER JOIN MatterTask MT
ON MT.MatterID = START.MatterID
AND MT.TaskID IN (82, 83, 84, 100, 108)
INNER JOIN Matter
ON Matter.MatterID = START.MatterID
INNER JOIN MatterField STOP
ON STOP.MatterID = START.MatterID
AND STOP.FieldID = 234
WHERE START.FieldID = 233
AND (START.Value IS NOT NULL AND START.Value <> 'none' AND START.Value <> 'NULL')
AND Matter.Person = 'bobsbigboy@theclient.com'
ORDER BY START.Value ASC
And yes, before you ask, the appointments can only be every 15 minutes. 🙂
February 16, 2009 at 5:46 pm
SELECT
START.Value AS StartTime,
STOP.Value AS StopTime,
MT.StopProjected AS ApptDate,
DATEDIFF(mi, START.Value, STOP.Value)/15 AS Increments
Regarding your StartTime and StopTime fields, are these derived from table columns of type datetime with the date part '1900-01-01'?
Regarding your ApptDate field, is this derived from a table column of type datetime, with the time part '00:00:00'?
February 16, 2009 at 5:55 pm
andrewd.smith (2/16/2009)
SELECT
START.Value AS StartTime,
STOP.Value AS StopTime,
MT.StopProjected AS ApptDate,
DATEDIFF(mi, START.Value, STOP.Value)/15 AS Increments
Regarding your StartTime and StopTime fields, are these derived from table columns of type datetime with the date part '1900-01-01'?
No, they are VarChar fields.
andrewd.smith (2/16/2009)
Regarding your ApptDate field, is this derived from a table column of type datetime, with the time part '00:00:00'?
Actually SmallDateTime, with the time part '00:00:00'
February 16, 2009 at 7:38 pm
The following query will return a resultset with 64 rows for each day that lies within the specified date range (@StartDate to @EndDate).
The columns in the resultset are:
SlotNumber intInteger between 1 and 64 where 1 represents the 15-minute slot at 06:00
StartTime datetimeDate and time of the start of the slot
Available int 0=booked, 1=free
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SELECT @StartDate = '2009-02-01'
SELECT @EndDate = '2009-02-07'
SELECT
SlotNumber = X.SlotIndex - 23,
StartTime = DATEADD(minute, 15 * X.SlotIndex, X.SlotDate),
Available = CASE WHEN (A.ApptDate IS NULL) THEN 1 ELSE 0 END
FROM (
SELECT
SlotDate = C.[Date],
SlotIndex = T.N
FROM (Tally T CROSS JOIN Calendar C)
WHERE (T.N >= 24 AND T.N <= 87)
AND (C.[Date] >= @StartDate AND C.[Date] <= @EndDate)
AND (C.IsWorkingDay = 1)
) X
LEFT OUTER JOIN (
SELECT
ApptDate = MT.StopProjected,
StartSlotIndex = DATEDIFF(minute, 0, START.Value) / 15,
StopSlotIndex = DATEDIFF(minute, 0, STOP.Value) / 15
FROM MatterField START
INNER JOIN MatterTask MT
ON (MT.MatterID = START.MatterID AND MT.TaskID IN (82, 83, 84, 100, 108))
INNER JOIN Matter
ON (Matter.MatterID = START.MatterID)
INNER JOIN MatterField STOP
ON (STOP.MatterID = START.MatterID AND STOP.FieldID = 234)
WHERE (START.FieldID = 233)
AND (START.Value IS NOT NULL AND START.Value <> 'none' AND START.Value <> 'NULL')
AND (Matter.Person = 'bobsbigboy@theclient.com')
) A ON (X.SlotDate = A.ApptDate AND X.SlotIndex >= A.StartSlotIndex AND X.SlotIndex < A.StopSlotIndex)
ORDER BY X.SlotDate, X.SlotIndex
The query uses both a Tally table and a Calendar table.
A Tally table simply contains a consecutive sequence of integers, in this case up to at least 87.
CREATE TABLE dbo.Tally (
[N] int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
)
A Calendar table contains a list of dates. There are many variations of Calendar tables. This one has a bit column IsWorkingDay to indicate whether a particular day is a working day or a holiday.
CREATE TABLE dbo.Calendar (
[Date] datetime NOT NULL PRIMARY KEY CLUSTERED,
[IsWorkingDay] bit NOT NULL
)
The "grid" of dates and slots is generated by the CROSS JOIN on the Tally table and the Calendar table. Which of these slots is already occupied by an appointment is determined by a LEFT OUTER JOIN with the second derived table.
EDIT: Sorry, forgot to add ORDER BY clause to query
February 16, 2009 at 8:31 pm
I believe that Andrew pretty much has it. The only shortcoming would be turning it into a matrix. You only need a Cross-Tab for that...
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2009 at 2:00 am
I *KNEW* someone here would be able to help! Although I haven't got it 100% licked just yet, I'm miles closer than I was before. Thank you, thank you, thank you! I'll let you know the final result as soon as I finish up.
Again, thanks for your help and your patience!
Sherman
February 18, 2009 at 7:31 am
Off topic...
Wow, Jeff....cool avatar!
February 18, 2009 at 10:39 pm
James A. Lawrence (2/18/2009)
Off topic...Wow, Jeff....cool avatar!
Heh... Thanks, James. Everyone kept bugging me to do something in the Star Wars fashion... I ran into a clip of a guy headbutting the "Death Star" and overlaid the RBAR on it. I think it might be a keeper.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply