return each hour for appointments? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

return each hour for appointments?

Author

Topic

brendita
Starting Member

38 Posts

Posted - 2006-03-23 : 16:23:51

Hello. I'm trying to create an appointment calendar type thing, but can't figure out the query to run. I need all hours from 8am-6pm to display, even if there is no appointment that hour. I've tried this:

SELECT theHour = CASE
WHEN DATEPART(hh,ApptDate) = 8
THEN Occasion
Else ' '
END
FROM BrendaCalendar

UNION

SELECT theHour = Case
WHEN DATEPART(hh,ApptDate) = 9
THEN Occasion
Else ' '
END
FROM BrendaCalendar

But that isn't what I want. I need it to return 11 rows (each hour). ANyone ever tried that before?

Thanks!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-23 : 16:46:38

How about

SELECT theHour = CASE
WHEN DATEPART(hh,ApptDate) between 8 and 18
THEN Occasion
Else ' '
END
FROM BrendaCalendar

brendita
Starting Member

38 Posts

Posted - 2006-03-23 : 16:53:21

But I need 11 rows (for each hour, even if no appt.) Does that make sense?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-03-23 : 16:53:33

I would create a temp table or table variable containing the hours, then create a query that links this table to BrendaCalendar.
Alternately, you could have a reference table that just contains the hours you want. For example, your ref table would contain 11 rows; one for each hour. The beauty of this is you can easily modify the hours you return rather than editing your stored proc.

HTH,

Tim

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-03-23 : 17:04:57

Well done!

You need a table of Hours (0 to 23 or 1 to 24). Then you need to Join to that table.



--Create your tally table once
Create Table TallyTable(
       Tally int
)

--Generate some data for it
SET NOCOUNT ON
Declare @i int
Set @i = 0
WHILE @i <= 23
     BEGIN
         Insert Into #TallyTable Values (@i)
         Set @i = @i + 1
     END

select * from TallyTable
SET NOCOUNT OFF  


--Now that you  have your tally table, cross join that to your appointment table.
--Something like this
SELECT t.Tally as theHour, bc.ApptDate
FROM TallyTable t
LEFT JOIN BrendaCalendar bc ON (DATEPART(hh,bc.ApptDate) = t.Tally OR DATEPART(hh,bc.ApptDate) IS NULL)

I think that will do it. If you are still having troubles, post some DDL and DML so we can create a sample of your Calendar table on our end and write the query for ya.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-23 : 17:08:07

>> BrendaCalendar ???

Does this mean you have a calandar table for each user?

CODO ERGO SUM

brendita
Starting Member

38 Posts

Posted - 2006-03-23 : 17:08:47

Dang, that is perfect! Thanks!

brendita
Starting Member

38 Posts

Posted - 2006-03-23 : 17:12:42

NO, that is for all users. It was just a sample name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources