Count problem - 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

SQL Server Development (2000)

Count problem

Author

Topic

benricho
Yak Posting Veteran

84 Posts

Posted - 2001-12-10 : 20:14:57

I have a table that stores the dates of events in the following format:

  eventID             eventStartDate             eventEndDate           eventTitle
------------|------------------------|--------------------|------------------|
     34                2001-11-3                     2001-11-8               Event 1
     35                2001-11-22                   2001-12-5               Event 2
     36                2001-12-6                     2001-12-7               Event 3

And I need to display a summary of each months events, as follows:

November 2001 (2)
December 2001 (7)

When an event goes over 2 (or more) months it will be counted in both months totals. Anyone want to help me, I seem to be stuck.

Edited by - benricho on 12/10/2001 20:20:27

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2001-12-10 : 20:24:55

benricho,

How do you choose which month.. It's enddate or startdate???

These are the questions that you have to answer yourself as they apply to your business process.

DavidM
It gets windy at a thousand feet...."Tutorial D"

benricho
Yak Posting Veteran

84 Posts

Posted - 2001-12-10 : 20:38:11

quote:
How do you choose which month.. It's enddate or startdate???

Well I was hoping that I could make it so that an event could be in more than one month, so if it's a month long festival that starts on the 15th, it shows up in both months. You don't think this is possible?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-12-10 : 20:54:13

Hey

One way to do it, is create a (temp?) table with the months yu want to report on, then join the events table to that if the start OR end date matches. If it is both, it will be joined to both months, then you can count it twice.

Something like this (BTW, some DDL and DML would have saved me time

)



/*
Create Table Events(
	eventID int,
	eventStartDate DateTime,
	eventEndDate DateTime,
	eventTitle VarChar(100)
	)

INSERT Into Events Values (34, '3-Nov-2001', '8-Nov-2001', 'Event 1')
INSERT Into Events Values (35, '22-Nov-2001', '5-Dec-2001', 'Event 2')
INSERT Into Events Values (36, '6-Dec-2001', '7-Dec-2001', 'Event 3')

*/


Create Table #months(
	StartDate DateTime,
	MonthName VarChar(100)
	)
INSERT Into #months VALUES ('1-Nov-2001', 'November 2001')
INSERT Into #months VALUES ('1-Dec-2001', 'December 2001')


SELECT M1.MonthName, Count(eventID)

FROM #Months M1
INNER JOIN Events E1 ON DateDiff(mm, E1.eventStartDate, M1.StartDate) = 0 OR
DateDiff(mm, E1.eventEndDate, M1.StartDate) = 0


GROUP BY M1.StartDate, M1.MonthName
ORDER BY M1.StartDate

Drop Table #Months

How is that ?

Damian

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2001-12-10 : 21:07:51

Ok then...


Select Month, max(Events) as Events from 
(
Select datename(m,eventStartDate) + ' ' + datename(yyyy,eventStartDate) as [Month], Count(*) as Events from Events
group by datename(m,eventStartDate)+ ' ' + datename(yyyy,eventStartDate)
union all
Select datename(m,eventEndDate) + ' ' + datename(yyyy,eventEndDate) as [Month], Count(*) as Events from Events
group by datename(m,eventEndDate)+ ' ' + datename(yyyy,eventEndDate)
) A
group by Month

HTH

DavidM
It gets windy at a thousand feet...."Tutorial D"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-10 : 21:17:17

This should work too, might be a little faster:

SELECT DateName(month, D.dates) + ' ' + DateName(year, D.Dates) MonthName,
Count(*) Events
FROM Events E INNER JOIN
(SELECT EventStartDate dates FROM Events UNION SELECT EventEndDate dates FROM Events) D
ON (E.EventStartDate=D.dates OR E.EventEndDate=D.dates)
WHERE D.dates Between @startdate AND @enddate
GROUP BY DateName(month, D.dates) + ' ' + DateName(year, D.Dates)

You wanted the Count(), right?

Edited by - robvolk on 12/10/2001 21:19:39

benricho
Yak Posting Veteran

84 Posts

Posted - 2001-12-10 : 21:24:52

Thank you so much you guys, I posted this about an hour ago after playing with it for about an hour, and I've got 3 good solutions. They all work, I think robvolk's solutions might be the best for what I'm looking for. Thanks guys.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2001-12-10 : 21:44:16

That's why I have a love/hate relationship with SQL...

There is more than 1 way to skin a cat!!!

Rob... watch out for eventStartDate and eventEndDate being the same (UNION vs UNION All)

DavidM
It gets windy at a thousand feet...."Tutorial D"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-11 : 08:13:30

Not wishing to be picky (much!), but none of these queries count the event in any month other than the ones in which it starts and ends.


SELECT mth, count(*) AS eventcount FROM (
  SELECT DateAdd(m, n, @startDate) mth
  FROM Numbers
  WHERE n BETWEEN 0 AND DATEDIFF(m, @startDate, @endDate)) AS Months
INNER JOIN Events ON DATEDIFF(m, eventStartDate, mth) >= 0
  AND DATEDIFF(m, eventEndDate, mth) <= 0
GROUP BY mth

Numbers is the usual table of non-negative integers. This will generate 0-999:


CREATE TABLE Numbers (n int primary key clustered)

INSERT INTO Numbers SELECT T1.i+T2.i*10+T3.i*100 FROM
  (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
   UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T1,
  (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
   UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T2,
  (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
   UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T3

benricho
Yak Posting Veteran

84 Posts

Posted - 2001-12-12 : 00:24:07

I thought something fishy was going on with my query so I checked back and found Arnold's post, and it gives me the correct results. I'm haven't seen a numbers table like this used before. Thanks Arnold.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources