Outer Join To Get Blank Appointment Times

Question

Outer Join To Get Blank Appointment Times

Lowell

SSC Guru

Points: 323518
More actions
August 25, 2003 at 10:52 am

#81026

Trying to get my arms around the idea of a outlook style calendar, and how to get the hourly appointments that would be blank.;
I've tried joining against an hourly table, but as soon as I introduce the date, I'm getting data the same as if i didn't join at all:
For example, I want output like this:
12am Interview James
1 00
2 00 Meet Manager
3 00 Company Meeting
4 00
assuming a table structure and data like this:
CREATE TABLE OUTLOOKCALENDAR (
CalDateDateTime,
CalHourint,
CalNoteVarchar(500) )
Insert into outlookcalendar(CalDate,CalHour,CalNote) values('08/25/2003',12,'Interview James')
Insert into outlookcalendar(CalDate,CalHour,CalNote) values('08/25/2003',14,'Meet Manager')
Insert into outlookcalendar(CalDate,CalHour,CalNote) values('08/25/2003',16,'Company Meeting')
select CalDate,CalHour,Calnote from outlookcalendar
Should I create a table with 24 hourly entries, and join against it like below, or is there a smarter way to do it?:
Create Table OutLookHours (
CalHourint,
CalDescrvarchar(30) )
/*
--just business hours for testing?
Insert into OutlookHours(CalHour,CalDescr) values(0,'12:00am')
Insert into OutlookHours(CalHour,CalDescr) values(1,'1:00am')
Insert into OutlookHours(CalHour,CalDescr) values(2,'2:00am')
Insert into OutlookHours(CalHour,CalDescr) values(3,'3:00am')
Insert into OutlookHours(CalHour,CalDescr) values(4,'4:00am')
Insert into OutlookHours(CalHour,CalDescr) values(5,'5:00am')
Insert into OutlookHours(CalHour,CalDescr) values(6,'6:00am')
*/
Insert into OutlookHours(CalHour,CalDescr) values(7,'7:00am')
Insert into OutlookHours(CalHour,CalDescr) values(8,'8:00am')
Insert into OutlookHours(CalHour,CalDescr) values(9,'9:00am')
Insert into OutlookHours(CalHour,CalDescr) values(10,'10:00am')
Insert into OutlookHours(CalHour,CalDescr) values(11,'11:00am')
Insert into OutlookHours(CalHour,CalDescr) values(12,'12:00pm')
Insert into OutlookHours(CalHour,CalDescr) values(13,'1:00pm')
Insert into OutlookHours(CalHour,CalDescr) values(14,'2:00pm')
Insert into OutlookHours(CalHour,CalDescr) values(15,'3:00pm')
Insert into OutlookHours(CalHour,CalDescr) values(16,'4:00pm')
Insert into OutlookHours(CalHour,CalDescr) values(17,'5:00pm')
Insert into OutlookHours(CalHour,CalDescr) values(18,'6:00pm')
/*
Insert into OutlookHours(CalHour,CalDescr) values(19,'7:00pm')
Insert into OutlookHours(CalHour,CalDescr) values(20,'8:00pm')
Insert into OutlookHours(CalHour,CalDescr) values(21,'9:00pm')
Insert into OutlookHours(CalHour,CalDescr) values(22,'10:00pm')
Insert into OutlookHours(CalHour,CalDescr) values(23,'11:00pm')
Insert into OutlookHours(CalHour,CalDescr) values(24,'12:00am')
*/
select outlookcalendar.CalDate,outlookhours.CalHour,outlookcalendar.Calnote from outlookhours
left outer join outlookcalendar
on OutlookHours.CalHour = outlookcalendar.calHour
--where CalDate = '08/25/2003'
order by OutlookHours.CalHour
With the limited data in the example, the above statement gives me the data I'm looking for, but when I uncomment the WHERE statement, I exclude everything from the join all over again.
Can someone help point me in the right direction? I may be going down the wrong path logically altogether.
Thanks
Lowell
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

try:



DECLARE @Date SMALLDATETIME

SET @Date = '8/25/2003'

--

SELECT 

  @Date AS "Date"

, oh.CalDescr AS "Hour"

, ISNULL(oc.Calnote, '') AS "Note"

FROM #OutlookCalendar oc

  RIGHT JOIN #OutlookHours oh

    ON oc.CalHour = oh.CalHour

WHERE oc.CalDate = @Date

OR oc.CalDate IS NULL

Edited by - jpipes on 08/25/2003 11:41:09 AM

hurcane SSChasing Mays Points: 623 More actions · Answer 2

quote:
With the limited data in the example, the above statement gives me the data I'm looking for, but when I uncomment the WHERE statement, I exclude everything from the join all over again.

The previous suggestion works, but you might also consider including the date in your "Hours" table and include the same date in all your insert statements. Your where clause would then be changed to use the date from the hours table instead of the calendar table.

I noticed that you have some blocks of code commented out while you're testing. Here's a bonus tip about easily commenting/uncommenting blocks of code in SQL. We use this format for debug code that we want to include as needed during testing.

/* Debug code here...

print 'Some debug data here.'

--*/

When I want to include the debug data, which may be a long block of print statements or a series of select statements, I simply add "--" to the beginning of the first line of the block, so it looks like this.

--/* Debug code here...

print 'Some debug data here.'

--*/

In just two keystrokes after positioning the selection point at the beginning of the block, I can uncomment and comment the whole block. You can use the Comment/Uncomment commands in Query Analyzer, but you have to highlight the entire block first.

jpipes SSChampion Points: 12230 More actions · Answer 3

Or, if you are in SQL2K QA, just press Ctrl-Shift-C to Comment Out, or Ctrl-Shift-R to Un Comment.

JteL SSC Enthusiast Points: 131 More actions · Answer 4

Include the where as part of the join.

select outlookcalendar.CalDate,outlookhours.CalHour,outlookcalendar.Calnote from outlookhours

left outer join outlookcalendar

on OutlookHours.CalHour = outlookcalendar.calHour

and outlookcalendar.CalDate = '08/25/2003'

order by OutlookHours.CalHour