Date Outputs from database

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

Re: Date Outputs from database

Brett_A wrote:
> 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?

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)

--
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.

Re: Date Outputs from database

On Apr 14, 3:10 pm, "Bob Barrows [MVP]"
wrote:
> Brett_A wrote:
> > 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?
>
> 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)
>
> --
> 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.

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

Thanks

Brett

Re: Date Outputs from database

Brett_A wrote:
> On Apr 14, 3:10 pm, "Bob Barrows [MVP]"
> wrote:
>> Brett_A wrote:
>>> 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?
>>
>> 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)
>>
>> --
>> 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.
>
> 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.

--
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.

Re: Date Outputs from database

On Apr 14, 4:36 pm, "Bob Barrows [MVP]"
wrote:
> Brett_A wrote:
> > On Apr 14, 3:10 pm, "Bob Barrows [MVP]"
> > wrote:
> >> Brett_A wrote:
> >>> 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?
>
> >> 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)
>
> >> --
> >> 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.
>
> > 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.
>
> --
> 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.

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

Re: Date Outputs from database

Brett_A wrote:
>>
>> 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.
>>
>
> 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?
>
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?
--
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.

Re: Date Outputs from database

On Apr 18, 1:00 pm, "Bob Barrows [MVP]"
wrote:
> Brett_A wrote:
>
> >> 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.
>
> > 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?
>
> 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?
> --
> 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.

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

Re: Date Outputs from database

Brett_A wrote:
> On Apr 18, 1:00 pm, "Bob Barrows [MVP]"
> wrote:
>> Brett_A wrote:
>>
>>>> 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.
>>
>>> 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?
>>
>> 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?
>> --
>> 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.
>
> 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
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 "

Re: Date Outputs from database

On Apr 18, 3:35 pm, "Bob Barrows [MVP]"
wrote:
> Brett_A wrote:
> > On Apr 18, 1:00 pm, "Bob Barrows [MVP]"
> > wrote:
> >> Brett_A wrote:
>
> >>>> 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.
>
> >>> 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?
>
> >> 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?
> >> --
> >> 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.
>
> > 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
>
> 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 "