The following looks at an Oracle based solution to writing such queries.

Suppose we have a simple set of appointments similar to table 1 below.

Table 1. Events Table

We may wish to calculate the number of appointments we have each day for the last week.

A first pass at such a query might be: SELECT EventDate, Count(EventName) as NumEvents FROM tbEvents GROUP BY Event Date

This will give us the following data:

One thing you may notice is that it doesn’t have any rows for the 14^th and 17^th.  As no data was recorded for those days, no records will be returned.  This will cause problems if the goal is to chart this data per day.

Some charting tools will have the ability to fill in these missing days as points on the chart but sometime we may find ourselves in the position of having a far simpler tool available- one that perhaps can only chart the data we give it.

Oracle provides an elegant solution to this problem using the CONNECT BY LEVEL condition.

THE QUERY:

SELECT LEVEL FROM dual CONNECT BY LEVEL <= 7

will give us a list of numbers from 1 to 7.

We can alter this slightly to get a row for each day in the past week:

SELECT TO_DATE (TO_CHAR (SYSDATE - LEVEL, 'DD-MON-YYYY ' ) ,'DD-MON-YYYY' ) dates FROM DUAL CONNECT BY LEVEL<= 7

We can then join this to our original table to produce the desired result:

SELECT daterange.dates ,COUNT(eventName)

FROM

(SELECT TO_DATE(TO_CHAR(SYSDATE - LEVEL,'DD-MON-YYYY ') ,      

'DD-MON-YYYY') dates FROM DUAL CONNECTBYLEVEL<= 7 ) daterange

LEFT OUTER JOIN tbEvents

ON TO_DATE(TO_CHAR (tbEvents.eventDate, 'DD-MON-YYYY ' ) , 'DD-MON-YYYY' ) = daterange.dates

GROUP BY daterange.dates  

ORDER BY daterange.dates

This will give us the table below (assuming that today is the 20– Jan – 2012)

As you can see this dataset is then much easier to chart. This query could be adapted for any missing sequential data.

Read our next blog where we detail how to return a dataset from an Oracle function:  Returning a Dataset from an Oracle Function

At Dataworks we utilise the perfect hybrid of configurable off the shelf toolsets and custom development to deliver innovative solutions - all based on leading and complementary technologies. This enables us to pinpoint where our software applications can make a real difference and deliver real benefits to your organisation.

If you would like to discuss how we can use our experience and expertise to deliver real benefits toyour business please contact us today on 051 878555051 878555 or email info@dataworks.ie

Image courtesy of suphakit73/ FreeDigitalPhotos.net