Poor Man’s Cube Style Slice & Dice In A Query

This weekend I had the privilege of volunteering the Give Camp in CharlotteGive Camp itself is an annual national event where technical people volunteer their time over the course of a weekend to build a solution for a charitable or non-profit organization that could use the help.

In my case, I was with in a team of 4 (3 exceptionally talented people – Paul Wheeler, Andrew Brown, Charlene Walsh and then me!) assigned to help “Cabarrus Literacy Council” which needed a decent website to bring awareness about its services.  The others did all the the work related to the web-site itself. It has been years since I have worked with the web and technology has moved on in that time. Keeping in mind that the solution had to be delivered at the end of the weekend, I decided to focus on my strengths (at least what I consider are my strengths) which is databases and SQL instead of trying to fight ASP.NET, MVC, JQuery, Razor(?!) or anything else of which I know nothing about.

Cabarrus Literacy Council:

  •  Their mission: ….all citizens of the community will have opportunities to learn to read and write
  • Their model: Tutors volunteer their time to teach people reading/writing skills
    • Students who like to read/write enroll themselves to utilize the services
    • The council tracks volunteer/student hours to focus on the big picture
  • Their need:
    • To build a website that would highlight the services
    • Ability to capture, Tutor/Student contact information
    • Ability for Tutors to report their time spent with students
    • Ability for program administrators to do reporting off of reported hours by different facets
    • A tutor always works with only one student at a time.

The Solution:

We chose to use OrchardProject.net – an open source ASP.net based content management system (CMS).  The data model had to be really simple so that we could complete the project on time. Although what goes on behind the logged in session is not visible, the site is available here (may be updated for the next few days).

I only worked on the database portion and so, I am going to focus only on that. The unique time-constrained situation allowed us to come up with a creative solution to satisfy the reporting need – Have one view that would satisfy all of the reporting needs!

The UI would access the one reporting view for all of its needs. The reporting needs that would be satisfied are
1) Report hours by time periods – Yearly, Quarterly, Monthly, Weekly and Daily
2) Track hours spent by tutors and students by Transportation time, Planning Time and Tutoring time
3) Report by any combinational permutation of 1 and 2

  • Total life-time hours spent by Tutors as well as Students
  • Period
  • Tutor
  • Student
  • Period + Tutor
  • Period + Student
  • Tutor + Student
  • Period + Tutor + Student

In plain English, here are a few examples
– Hours spent yearly by tutor on student
– Hours spent this week by all students and Tutors
– Hours a student spent learning in year 2012
– Hours a student spent with a specific tutor in a specific year’s quarter
– ….you get the idea

Putting the CUBE function to use in a SQL query:

Fortunately, the reporting is the easiest part of the whole project primarily because of the seldom used “CUBE” function that is available in both SQL Server and Oracle in the exact same flavor although this implementation is for SQL Server. Let me share the simple data model and the solution.

The CUBE function is simple.  Given a set of columns to “GROUP BY” and a set of columns to get summary information, the CUBE function, automatically summarizes by all combinations of the columns in the GROUP BY.

Here is the simple data model

DataModel

Sample data:

For illustration purposes, the following shows just two rows from the Contact table

SELECT * FROM Contact WHERE ContactId IN (1,6)

Contact

..and just two rows in the time-entry table

TimeEntry

Just to illustrate the CUBE functionality, I will do a simple query

SELECT TutorContactId,
	StudentContactId,
	SUM(TransportationTimeInHours) AS TransportationTimeInHours
FROM   TimeEntry te
GROUP  BY TutorContactId,
	StudentContactId WITH CUBE

CUBE

Notice how it produced four combinations for the two group by columns.

Below, notice, that we have 12 rows for 3 “GROUP BY” columns

CUBE2

So, now that we have a good idea of how CUBE works, the solution is to just exploit it.  To do so, we will make 5 views

  • Daily Summary View
  • Weekly Summary View
  • Monthly Summary View
  • Quarterly Summary View
  • Yearly Summary View
  • ….and just “Summary View” that combines all of the above.The final summary would produce results such as below (still the time entry table has just two rows!):FinalResultsAnyway, the UI now has to filter on the “Level” to view reports at + “Report Period” + other combinations.

View definitions

Here is the complete code for the views:

CREATE VIEW [dbo].[SummaryHrsByDayTutorStudentView]
AS
SELECT
  'Day'
  reportperiod,
  LTRIM(STR(DATEPART(yyyy, timeddate))) + '-' +
  LTRIM(STR(DATEPART(mm, timeddate))) + '-' + LTRIM(STR(DATEPART(dd,
  timeddate)))
  period,
  MIN(DATEADD(dd, DATEDIFF(dd, 0, timeddate), 0))
  AS periodbegin,
  MAX(DATEADD(dd, 1, DATEADD(dd, DATEDIFF(dd, 0, timeddate), 0)))
  - .00001
  AS
  periodend,
  tutorcontactid,
  studentcontactid,
  SUM(transportationtimeinhours)
  AS transportationtimeinhours,
  SUM(planningtimeinhours)
  AS planningtimeinhours,
  SUM(tutoringtimeinhours)
  AS tutoringtimeinhours,
  SUM(transportationtimeinhours) + SUM(planningtimeinhours) + SUM(
  tutoringtimeinhours)
  AS totaltimeinhours
FROM timeentry te
WHERE isapproved = 1
GROUP BY LTRIM(STR(DATEPART(yyyy, timeddate))) + '-' +
         LTRIM(STR(DATEPART(mm, timeddate))) + '-' +
         LTRIM(STR(DATEPART(dd,
         timeddate))),
         tutorcontactid,
         studentcontactid WITH CUBE
GO
------------------------------------------------------------------------

CREATE VIEW [dbo].[SummaryHrsByMonthTutorStudentView]
AS
SELECT
  'Month'
  reportperiod,
  LTRIM(STR(DATEPART(yyyy, timeddate))) + '-' + LTRIM(STR(DATEPART(MONTH,
  timeddate)))
  period,
  MIN(DATEADD(mm, DATEDIFF(mm, 0, timeddate), 0))
  AS periodbegin,
  MAX(DATEADD(mm, 1, DATEADD(mm, DATEDIFF(mm, 0, timeddate), 0)))
  - .00001
  AS
  periodend,
  tutorcontactid,
  studentcontactid,
  SUM(transportationtimeinhours)
  AS transportationtimeinhours,
  SUM(planningtimeinhours)
  AS planningtimeinhours,
  SUM(tutoringtimeinhours)
  AS tutoringtimeinhours,
  SUM(transportationtimeinhours) + SUM(planningtimeinhours) + SUM(
  tutoringtimeinhours)
  AS totaltimeinhours
FROM timeentry te
WHERE isapproved = 1
GROUP BY LTRIM(STR(DATEPART(yyyy, timeddate))) + '-' + LTRIM(
         STR(DATEPART(MONTH,
         timeddate))),
         tutorcontactid,
         studentcontactid WITH CUBE
GO
------------------------------------------------------------------------

CREATE VIEW [dbo].[SummaryHrsByQuarterTutorStudentView]
AS
SELECT
  'Quarter'
  reportperiod,
  LTRIM(STR(DATEPART(yyyy, timeddate))) + '-' + LTRIM(
  STR(DATEPART(QUARTER,
  timeddate)))
  period,
  MIN(DATEADD(qq, DATEDIFF(qq, 0, timeddate), 0))
  AS periodbegin,
  MAX(DATEADD(qq, 1, DATEADD(qq, DATEDIFF(qq, 0, timeddate), 0)))
  - .00001
  AS
  periodend,
  tutorcontactid,
  studentcontactid,
  SUM(transportationtimeinhours)
  AS transportationtimeinhours,
  SUM(planningtimeinhours)
  AS planningtimeinhours,
  SUM(tutoringtimeinhours)
  AS tutoringtimeinhours,
  SUM(transportationtimeinhours) + SUM(planningtimeinhours) + SUM(
  tutoringtimeinhours)
  AS totaltimeinhours
FROM timeentry te
WHERE isapproved = 1
GROUP BY LTRIM(STR(DATEPART(yyyy, timeddate))) + '-' + LTRIM(
         STR(DATEPART(QUARTER,
         timeddate))),
         tutorcontactid,
         studentcontactid WITH CUBE
GO
------------------------------------------------------------------------

CREATE VIEW [dbo].[SummaryHrsByWeekTutorStudentView]
AS
SELECT
  'Week'
  reportperiod,
  LTRIM(STR(DATEPART(yyyy, timeddate))) + '-' + LTRIM(STR(DATEPART(WEEK,
  timeddate
  )))
  period,
  MIN(DATEADD(wk, DATEDIFF(wk, 0, timeddate), 0))
  AS periodbegin,
  MAX(DATEADD(wk, 1, DATEADD(wk, DATEDIFF(wk, 0, timeddate), 0)))
  - .00001
  AS
  periodend,
  tutorcontactid,
  studentcontactid,
  SUM(transportationtimeinhours)
  AS transportationtimeinhours,
  SUM(planningtimeinhours)
  AS planningtimeinhours,
  SUM(tutoringtimeinhours)
  AS tutoringtimeinhours,
  SUM(transportationtimeinhours) + SUM(planningtimeinhours) + SUM(
  tutoringtimeinhours)
  AS totaltimeinhours
FROM timeentry te
WHERE isapproved = 1
GROUP BY LTRIM(STR(DATEPART(yyyy, timeddate))) + '-' + LTRIM(
         STR(DATEPART(WEEK,
         timeddate
         ))),
         tutorcontactid,
         studentcontactid WITH CUBE

GO
------------------------------------------------------------------------

CREATE VIEW [dbo].[SummaryHrsByYearTutorStudentView]
AS
SELECT
  'Year'
  reportperiod,
  LTRIM(STR(DATEPART(YEAR, timeddate)))
  period,
  MIN(DATEADD(yy, DATEDIFF(yy, 0, timeddate), 0))
  AS periodbegin,
  MAX(DATEADD(yy, 1, DATEADD(yy, DATEDIFF(yy, 0, timeddate), 0)))
  - .00001
  AS
  periodend,
  tutorcontactid,
  studentcontactid,
  SUM(transportationtimeinhours)
  AS transportationtimeinhours,
  SUM(planningtimeinhours)
  AS planningtimeinhours,
  SUM(tutoringtimeinhours)
  AS tutoringtimeinhours,
  SUM(transportationtimeinhours) + SUM(planningtimeinhours) + SUM(
  tutoringtimeinhours)
  AS totaltimeinhours
FROM timeentry te
WHERE isapproved = 1
GROUP BY LTRIM(STR(DATEPART(YEAR, timeddate))),
         tutorcontactid,
         studentcontactid WITH CUBE

GO
------------------------------------------------------------------------

CREATE VIEW [dbo].[SummaryView]
AS
WITH every
AS (SELECT
  *
FROM summaryhrsbydaytutorstudentview
UNION ALL
SELECT
  *
FROM summaryhrsbyweektutorstudentview
UNION ALL
SELECT
  *
FROM summaryhrsbymonthtutorstudentview
UNION ALL
SELECT
  *
FROM summaryhrsbyquartertutorstudentview
UNION ALL
SELECT
  *
FROM summaryhrsbyyeartutorstudentview)
SELECT
  CASE
    WHEN (period IS NULL) AND
      (tutorcontactid IS NULL) AND
      (studentcontactid IS NULL) THEN '[NONE]'
    WHEN (period IS NOT NULL) AND
      (tutorcontactid IS NULL) AND
      (studentcontactid IS NULL) THEN 'Period'
    WHEN (period IS NULL) AND
      (tutorcontactid IS NOT NULL) AND
      (studentcontactid IS NULL) THEN 'Tutor'
    WHEN (period IS NULL) AND
      (tutorcontactid IS NULL) AND
      (studentcontactid IS NOT NULL) THEN 'Student'
    WHEN (period IS NOT NULL) AND
      (tutorcontactid IS NOT NULL) AND
      (studentcontactid IS NULL) THEN 'Period + Tutor'
    WHEN (period IS NOT NULL) AND
      (tutorcontactid IS NULL) AND
      (studentcontactid IS NOT NULL) THEN 'Period + Student'
    WHEN (period IS NULL) AND
      (tutorcontactid IS NOT NULL) AND
      (studentcontactid IS NOT NULL) THEN 'Tutor + Student'
    WHEN (period IS NOT NULL) AND
      (tutorcontactid IS NOT NULL) AND
      (studentcontactid IS NOT NULL) THEN 'Period + Tutor + Student'
  END AS LEVEL,
  (c_tut.contactfirstname + ' ' + c_tut.contactlastname) AS tutor,
  c_stu.contactfirstname student,
  every.*
FROM every
LEFT OUTER JOIN contact c_tut
  ON every.tutorcontactid = c_tut.contactid
LEFT OUTER JOIN contact c_stu
  ON every.studentcontactid = c_stu.contactid

------------------------------------------------------------------------

Table definitions

In case you are interested in the table-definitions itself, here they are for you to try:

CREATE TABLE [dbo].[Contact]
(
[ContactId]        [BIGINT] IDENTITY(1, 1) NOT NULL,
[ContactType]      [VARCHAR](50) NOT NULL,
[ContactFirstName] [NVARCHAR](50) NOT NULL,
[ContactLastName]  [NVARCHAR](50) NULL,
[Address]          [NVARCHAR](100) NULL,
[City]             [NVARCHAR](50) NULL,
[State]            [NVARCHAR](2) NULL,
[Zip]              [NVARCHAR](50) NULL,
[HomePhone]        [NVARCHAR](25) NULL,
[CellPhone]        [NVARCHAR](25) NULL,
[EMail]            [NVARCHAR](125) NULL,
[IsActive]         [BIT] NOT NULL CONSTRAINT [DF_Contact_IsActive] DEFAULT
((1)),
[Comments]         [VARCHAR](1024) NULL,
[CreateDate]       [DATETIME] NOT NULL,
[UpdateDate]       [DATETIME] NULL,
CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED ( [ContactId] ASC )WITH (
pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF,
allow_row_locks = on, allow_page_locks = on) ON [PRIMARY]
)
ON [PRIMARY]
GO 

------------------------------------------------------------------------

CREATE TABLE [dbo].[ContactType]
(
[ContactType]            [VARCHAR](50) NOT NULL,
[ContactTypeDescription] [VARCHAR](255) NOT NULL,
[CreateDate]             [DATETIME] NOT NULL,
[UpdateDate]             [DATETIME] NULL,
CONSTRAINT [PK_ContactType] PRIMARY KEY CLUSTERED ( [ContactType] ASC )WITH
(pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF,
allow_row_locks = on, allow_page_locks = on) ON [PRIMARY]
)
ON [PRIMARY] 

GO
------------------------------------------------------------------------

CREATE TABLE [dbo].[TimeEntry]
(
[TimeEntryId]               [BIGINT] IDENTITY(1, 1) NOT NULL,
[TutorContactId]            [BIGINT] NOT NULL,
[StudentContactId]          [BIGINT] NOT NULL,
[TransportationTimeInHours] [REAL] NULL,
[PlanningTimeInHours]       [REAL] NULL,
[TutoringTimeInHours]       [REAL] NULL,
[TimedDate]                 [DATETIME] NOT NULL,
[Comments]                  [NVARCHAR](1024) NULL,
[IsApproved]                [BIT] NOT NULL CONSTRAINT
[DF_TimeEntry_IsApproved] DEFAULT ((0)),
[ApproverContactId]         [BIGINT] NULL,
[CreateDate]                [DATETIME] NOT NULL,
[UpdateDate]                [DATETIME] NULL,
[UpdatedByContactId]        [BIGINT] NULL,
CONSTRAINT [PK_TimeEntry] PRIMARY KEY CLUSTERED ( [TimeEntryId] ASC )WITH (
pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF,
allow_row_locks = on, allow_page_locks = on) ON [PRIMARY]
)
ON [PRIMARY]

GO
------------------------------------------------------------------------

The idea is really simple and since it is available in both Oracle and SQL Server, it can be used without worries about portability. Also, checkout the ROLLUP operator as you look into using CUBE.

  • Alternate (simpler) Version of Summary View – This is a simpler version of the summary view
CREATE VIEW [dbo].[SummaryView_V2]
AS
WITH every
AS
(
	SELECT *
	FROM   summaryhrsbydaytutorstudentview
	UNION ALL
	SELECT *
	FROM   summaryhrsbyweektutorstudentview
	UNION ALL
	SELECT *
	FROM   summaryhrsbymonthtutorstudentview
	UNION ALL
	SELECT *
	FROM   summaryhrsbyquartertutorstudentview
	UNION ALL
	SELECT *
	FROM   summaryhrsbyyeartutorstudentview
)
SELECT CASE
	WHEN ( period IS NOT NULL )
		AND ( tutorcontactid IS NULL )
		AND ( studentcontactid IS NULL ) THEN 'ALL'
	WHEN ( period IS NOT NULL )
		AND ( tutorcontactid IS NOT NULL )
		AND ( studentcontactid IS NULL ) THEN 'Tutor'
	WHEN ( period IS NOT NULL )
		AND ( tutorcontactid IS NULL )
		AND ( studentcontactid IS NOT NULL ) THEN 'Student'
	WHEN ( period IS NOT NULL )
		AND ( tutorcontactid IS NOT NULL )
		AND ( studentcontactid IS NULL ) THEN 'Period + Tutor'
	END AS LEVEL,
	( c_tut.contactfirstname + ' ' + c_tut.contactlastname ) AS tutor,
	c_stu.contactfirstname AS student,
	every.*
FROM   every
LEFT OUTER JOIN contact c_tut
	ON every.tutorcontactid = c_tut.contactid
LEFT OUTER JOIN contact c_stu
	ON every.studentcontactid = c_stu.contactid
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s