Monday, 22 June 2015

Adjust your SQL queries to see data series

Many users have data in a specific table format with a 'type' column and want to show inside a chart series for every value of 'type'.

Lets take a simple example of a DAY table (where NAME column is our 'type') :
NAME               VALUE               CREATION_DATE
-----------------------------------------------------------------
MORNING        90                  2015-06-12
NOON               210                 2015-06-12
EVENING          60                  2015-06-12
MORNING        85                  2015-06-13
NOON               240                 2015-06-13
EVENING          50                  2015-06-13
MORNING        92                  2015-06-14
NOON               235                2015-06-14
EVENING          55                  2015-06-14

We want to show DATE on x axis and series for MORNING, NOON, EVENING on y axis. To make this happen NextReports must obtain a query result like:
CREATION_DATE     MORNING_V    NOON_V   EVENING_V
-------------------------------------------------------------------------------------------------
2015-06-12        90           210     60
2015-06-13        85           240     50  
2015-06-14        92           235     55

Then it is very easy to add DATE on x axis and MORNING_V, NOON_V, EVENING_V on y axis as column series.

This transformation can be easily obtained using sub-queries. For example, on our table we will use following sql:
SELECT DISTINCT
   a1.CREATIONDATE,
  (select VALUE from DAY a2 where a2.name='MORNING' and         
              a2.CREATIONDATE=a1.CREATIONDATE) as MORNING_V,
   (select VALUE from DAY a2 where a2.name='NOON' and
              a2.CREATIONDATE=a1.CREATIONDATE) as NOON_V,
   (select VALUE from DAY a2 where a2.name='EVENING' and
              a2.CREATIONDATE=a1.CREATIONDATE) as EVENING_V
FROM
DAY a1

No comments:

Post a comment