Keeping an accurate count of events

<>I am newish to databases and would appreciate someadvise. I think I have a solution to myproblem but it is going to take me a lot of time to get it running. If there is a better way of doing it I wouldlike to know.

I have a table :-

"eventDates" withcolumns (id,date,eventID,eventCount)

<>The id auto increments as a Primary Key.
date holds the date of theevent.
EventIDreferences anothertable with info about the events
Up to 9eventIDs can be addedfor each date and I want eventCount to hold an integer (1 to 9) to allow me to "pivot"the data to the table below

"results" with columns (date,eventCount1, eventCount2 …..eventCount9) so each row will hold a date and nonto nineeventIDs occurring on that date.

Is there an easy way to keep eventCount accurate or do Ijust have to write a lot of code? Iwill need to be able to remove events as well as add them. I will use a mixture of stored proceduresand VB.Net I guess?

Many thanks for any advice.

Mike

If I understand correctly, you don't need the eventCount in your table at all. It can be generated on the fly through COUNT() for each eventID.

If you post a little bit of your test data and the result(whether pivot or not) you want, it should be able to find a soulution from database query. Of cource, there should be other solution from client end.

Let's see what I can help.

|||Hi thanks for the help.

I am not sure how best to "post data".

But for example :-

Table eventDates
id date eventID ??eventCount
1 20/06/06 21 1
2 21/06/06 21 1
3 22/06/06 21 1
4 20/06/06 22 2
5 24/06/06 23 1
6 25/06/06 23 1
7 22/06/06 24 2
8 23/06/06 24 1
9 24/06/06 24 2
10 21/06/06 23 2
11 22/06/06 23 3

Table callandar
date name
19/06/06 Fred
20/06/06 Richard
21/06/06 Richard
22/06/06 Richard
23/06/06 Andrew
24/06/06 Andrew
25/06/06 Andrew
26/06/06 Andrew
27/06/06 David
28/06/06 David

Table Result
date name event1 event2 event3 event4 .......event9
19/06/06 Fred
20/06/06 Richard 21 22
21/06/06 Richard 21 23
22/06/06 Richard 21 24 23
23/06/06 Andrew 24
24/06/06 Andrew 23 24
25/06/06 Andrew 23
26/06/06 Andrew
27/06/06 David
28/06/06 David

My webserver is running sql server 2000, so I know that I cannot usePIVOT as such but CASE I believe. However, I thought I would needthe ??eventCount column in any case.

Many thanks for any advise.

Regards

Mike Senior|||

SELECT

CONVERT(NVARCHAR(10),t2.cDate, 103)as cDate, t2.[Name],SUM(event1)as event1,SUM(event2)as event2,SUM(event3)as event3,SUM(event4)as event4,SUM(event5)as event5,SUM(event6)as event6,SUM(event7)as event7,SUM(event8)as event8,SUM(event9)as event9

FROM

(SELECT t.cDate, t.[Name],

CASE

WHEN t.eventCount= 1THEN t.eventIDELSENULLENDAS [event1],

CASE

WHEN t.eventCount= 2THEN t.eventIDELSENULLENDAS [event2],

CASE

WHEN t.eventCount= 3THEN t.eventIDELSENULLENDAS [event3],

CASE

WHEN t.eventCount= 4THEN t.eventIDELSENULLENDAS [event4],

CASE

WHEN t.eventCount= 5THEN t.eventIDELSENULLENDAS [event5],

CASE

WHEN t.eventCount= 6THEN t.eventIDELSENULLENDAS [event6],

CASE

WHEN t.eventCount= 7THEN t.eventIDELSENULLENDAS [event7],

CASE

WHEN t.eventCount= 8THEN t.eventIDELSENULLENDAS [event8],

CASE

WHEN t.eventCount= 9THEN t.eventIDELSENULLENDAS [event9]

FROM

(

SELECT Table_1_calendar.cDate, Table_1_calendar.[Name], Table_1event.eventID, Table_1event.eventCount

FROM

Table_1_calendarLeftJOIN

Table_1event

ON Table_1_calendar.cDate= Table_1event.date) t

)

t2

GROUP

BY t2.[cDate], t2.[Name]

ORDER

BY t2.[cDate]

Let me know if need more help.

|||Hi Limno

Sorry for the slow reply, work is busy and web pages are just a hobby.

Very many thanks for the SELECT statement which does return the tablestructure I need. It would have taken me ages to work out - and Iam slowly working through it so that I learn from it.

Thanks again.

Mike