Calendar Tables 2 - SQL Server Forums

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)

Calendar Tables 2

Author

Topic

thundr51
Starting Member

6 Posts

Posted - 2004-06-07 : 14:21:27

After reading [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20545&SearchTerms=calendar[/url]
I decided to give the calendar a try.
I'm trying to include my calendar in a query to get some counts but i'm not sure how to go about it.
ex.
select count(*) from seminars where type =1

how would I use the calendar table to find out how many seminars took place in each month?(even if there are none in a particular month)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-07 : 14:27:08

why Calendar?

select count(*),Month(SeminarDate)
from seminars where type =1
Groupby Month(SeminarDate)

Jim
Users <> Logic

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-07 : 14:53:04

Just need a tally/numbers table.

SELECT a.Number 'MONTH', count(b.<count field>)
FROM Numbers a LEFT JOIN seminars b ON a.Number = MONTH(b.SeminarDate)
WHERE a.Number <= 12
GROUP BY a.Number

thundr51
Starting Member

6 Posts

Posted - 2004-06-08 : 08:17:40

@JimL
Why calendar? Check the link in the first post. Some good reasons there. Normally, I do exactly as your sql says but then what if I want to see entries for months that don't have seminars?

drymchaser is right when he says you need a tally numbers table, which is why I decided to use a calendar. This is what i've come up with so far

SELECT a.monthno 'MONTH', count(convert(varchar(50),b.rowguid))
FROM
(select distinct monthno from calendar) a
LEFT JOIN seminars b ON a.monthno = MONTH(b.SeminarDatetime)
where seminardatetime between '1/1/2004' and '12/1/2004'
GROUP BY a.monthno
order by a.monthno

I do the distinct because of the way the calendar is formated(a monthno for every date).

Again, this works fine, but currently seminars haven't been planned for anything past August. How would I show this?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-08 : 08:54:17

quote:
Originally posted by thundr51

Again, this works fine, but currently seminars haven't been planned for anything past August. How would I show this?

Try this:

SELECT a.monthno 'MONTH', count(convert(varchar(50),b.rowguid))
FROM
(select distinct monthno from calendar) a
LEFT JOIN seminars b ON a.monthno = MONTH(b.SeminarDatetime)
AND YEAR(b.seminardatetime) = 2004
GROUP BY a.monthno
order by a.monthno

thundr51
Starting Member

6 Posts

Posted - 2004-06-08 : 09:07:58

Thanx drymchaser, it would appear my innocent looking 'where' statement was the culprit. I'm starting to like browsing these boards more, it's a VERY good source of info.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources