In "Part 1" I introduced you to the simple-but-great (and FREE) Advanced DataGrid module from Efficion Consulting.  If you aren't familiar with the module then go back and read Part 1 since we're using the module again here in "Part 2".

Now I'll show another example of using the Advanced DataGrid module to fill a gap between core DotNetNuke functionality and having to write your own module.  This example creates an add-on for the core Events module.  Specifically, we're going to create an "Events Status Table" for use with events that require user registration.  Our status table will show both upcoming and recently passed events as well as event registration status and the remaining seating capacity for each event.  

I'll add a "Part 3" post on the Advanced Datagrid where we'll wrap with a look at how to pretty things up in the styling department.

Creating the Status Table
All the data we need for this example in contained in three DNN tables:  Events, EventsSignups and Modules.  We need to reference Modules in order to make sure we only grab events from the current DNN portal.  If you have multiple Events calendars on your portal you may want to be more specific in your WHERE clause and limit data retrival based on a specific events ModuleID.

This query returns the Event Name, Event Date and Registration Status.  The registration status is "Event Ended" if the event has passed, "FULL" if seating capacity has been met, "Registration Closed" if the administrator has manually closed registration or the number of seats remaining if registration is still open and capacity has not been met.

SELECT    
  EventName AS [Event],
  LEFT(CONVERT(varchar(30), 
       DATEADD(day, DATEDIFF(day, 0, EventTimeBegin), 0), 121), 10)
  AS [Date],
  CASE 
    WHEN (EventDateEnd < getdate()-1) 
      THEN 'Event Ended'
    WHEN (SELECT (e.MaxEnrollment - COUNT(*)) AS Expr1
         FROM EventsSignups
         WHERE (EventID = e.EventID))<1
      THEN 'FULL'
    WHEN (e.Signups = '0')
      THEN 'Registration Closed'
    ELSE
      (SELECT STR(e.MaxEnrollment - COUNT(*)) AS Expr1
       FROM EventsSignups
       WHERE (EventID = e.EventID)) + ' seats remaining'
  END AS [Status]
FROM
  Events AS e inner join
  Modules AS m ON m.ModuleID = e.ModuleID
WHERE    
  (m.PortalID = [dnn:PortalID]) AND
  (EventTimeBegin > getdate()-30)
ORDER BY
  Date DESC

DISCLAIMER:  This example is to illustrate the power and flexibility of using the Advanced DataGrid.  In practice this query would be better served as a stored procedure.  (Also in practice one would probably come up with a cleaner query than my hastily-coded example.)

Note that this query returns events that have passed within the last 30 days so visitors can see just how active your Events calendar has been recently.  It also returns ALL upcoming Events -- if you have a lot of events you may want to limit how far forward the query looks.

Additions
Some natural additions might include the total number of seats (rather than just seats remaining), the event location (assuming you're populating that field in the Events module), the cost of the event, start time, end time, etc.  Just be sure that you're handling NULL values as needed since you can't always rely on all data fields getting populated -- the error result from a bad query is not a pretty thing to have on your site.

Give it Some Style
In "Part 3" we'll cover a couple of tips on how to add style to your ADG table results.