Date Outputs from database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brett_A - 14 Apr 2008 19:55 GMT

I have an Events table in a database (MS SQL). I would like to
present the
information like this:

August:

Event 1
Event 2
Event 3

September

Event 1
Event 2
Event 3

October

Event 1
Event 2
Event 3

Is there an automated way (with the proper SQL code and ASP) to
achieve this?
A straight listing is easy, but I want to add the breakout by month.

Any direction would be welcome.

Thanks

Brett

Reply to this Message

Bob Barrows [MVP] - 14 Apr 2008 20:10 GMT

> I have an Events table in a database (MS SQL). I would like to
> present the
[quoted text clipped - 20 lines]
> Is there an automated way (with the proper SQL code and ASP) to
> achieve this?

Sure, no reason why not.

> A straight listing is easy, but I want to add the breakout by month.
>
> Any direction would be welcome.

We need more information than that. For starters:
database type (SQL Server) and version
table structure (column names and datatypes)

Signature

Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Reply to this Message

Brett_A - 14 Apr 2008 21:03 GMT

On Apr 14, 3:10 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:

> > I have an Events table in a database (MS SQL). I would like to
> > present the
[quoted text clipped - 36 lines]
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Database Type: MS SQL Server 2005
Table Columns: Event_title (nvarchar(100)), Event_date (datetime)

Thanks

Brett

Reply to this Message

Bob Barrows [MVP] - 14 Apr 2008 21:36 GMT

> On Apr 14, 3:10 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
> wrote:
[quoted text clipped - 41 lines]
> Database Type: MS SQL Server 2005
> Table Columns: Event_title (nvarchar(100)), Event_date (datetime)

OK, if you are planning to retrieve this data very often, you should
consider creating a persisted calculated column in your table. Here is
an example script:

ALTER TABLE dbo.Events ADD
Event_month AS DATEPART(mm,Event_date) PERSISTED

This will allow you to create an index on the Event_month column to
speed retrieval. It will also simplify the sql statement:

SELECT Event_month, Event_title FROM Events
Order By Event_date,Event_title

Then it's simply a matter of retrieving the data, looping through it,
keeping track of the month so that when it changes you can write the new
value. Let us know if you need help with this part.

Signature

Reply to this Message

Brett_A - 18 Apr 2008 17:13 GMT

On Apr 14, 4:36 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:

> > On Apr 14, 3:10 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
> > wrote:
[quoted text clipped - 64 lines]
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

I understand the first part, but second part I'm not sure of. I've
done nested repeats. Is that what you're referring to? How would I
get the Months to be in the correct order?

Thanks

Brett

Reply to this Message

Bob Barrows [MVP] - 18 Apr 2008 18:00 GMT

>> ALTER TABLE dbo.Events ADD
>> Event_month AS DATEPART(mm,Event_date) PERSISTED
[quoted text clipped - 12 lines]
> done nested repeats. Is that what you're referring to? How would I
> get the Months to be in the correct order?

Since the results are ordered by Event_date, don't they have to be in
the correct order? Or is it your intention to group events from several
years within the same month?

Signature

Reply to this Message

Brett_A - 18 Apr 2008 20:12 GMT

On Apr 18, 1:00 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:

> >> ALTER TABLE dbo.Events ADD
> >> Event_month AS DATEPART(mm,Event_date) PERSISTED
[quoted text clipped - 21 lines]
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

No, just "new" events, so no need to account for "expired" events.

Is this a nested repeat situation? The primary output will be the
Months and the secondary output will be the events for that month by
filtering the second recordset with that regions Month's value.
Writing the query code isn't a problem, I just don't see how I can
turn something like this:

event ID event Date event Month event Title
1 05/01/08 May Event 1
2 06/01/08 June Event 2
3 06/15/08 June Event 3
4 07/15/08 July Event 4

Into output that looks like this:

May

Event 1

June

Event 2
Event 3

July

Event 4

Thanks

Brett

Reply to this Message

Bob Barrows [MVP] - 18 Apr 2008 20:35 GMT

> On Apr 18, 1:00 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
> wrote:
[quoted text clipped - 53 lines]
>
> Event 4

It's just a loop using a variable that keeps track of which month you
are in. Something like this:

'open the recordset (rs), then dump its data into an array:
if not rs.eof then ardata = rs.getrows
rs.close: set rs=nothing
conn.close: set conn = nothing

'get the month from the first record:
curmth = ardata(2,0)
'write it to response
response.write curmth & " "

'loop through the array
for i = 0 to ubound(ardata,2)
newmth = ardata(2, i)
'see if we've hit a different month
if newmth <> curmth then
curmth = newmth
response.write " <hr>" & curmth & " "
end if
'write the event title
response.write ardata(3,i)
next

Signature

Reply to this Message

Brett_A - 19 Apr 2008 04:07 GMT

On Apr 18, 3:35 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:

> > On Apr 18, 1:00 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
> > wrote:
[quoted text clipped - 84 lines]
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Thanks!, I'll give that a shot.

Brett

Reply to this Message

Webmaster Forum / ASP / Database Access / April 2008

Date Outputs from database