Evgeniy Kleban Evgeniy Kleban - 2 months ago 10
SQL Question

SQL query make with inner SELECT

I have 3 tables, and i have query that display new table, where each row is filled with that:

SELECT rov.*
FROM report_or_vals rov
WHERE rov.or_group_indice_id = 1
AND report_date BETWEEN ? AND ?


Therefore, second row will be:

SELECT rov.*
FROM report_or_vals rov
WHERE rov.or_group_indice_id = 2
AND report_date BETWEEN ? AND ?


Question make is the place when i put my
NSDate
object (iOS, objective-c object for hold date).

My question is, how to modify this query to show additional columns? Which value should be equal to: (row value in statement above) - (row value statement after), where statement after is similiar statement, but with date reduced by single day.

So, if i have row value for today equal to 10, and previous day equal to 7, i want my statement to contain something like :column_today_value (equal to 10), column_difference_value (equal to 3 (10-7)).

How to achieve that with SQL language? Im sorry, im an iOS dev. and not quite familiar with SQL functions. But i hope that is actually an easy task.

Answer

I understand the question to be: How do we display a list of daily values and the difference between each day's value and the previous day's value

I use a self join to accomplish this.

SELECT      [TODY].value                    AS  [VALUE]
,           [YEST].value                    AS  [PREV_VALUE]
,           [TODY].value - [YEST].value     AS  [DIFFERENCE]
,          '[TODY].value - [YEST].value = ' 
          + CAST([TODY].value - [YEST].value 
                 AS varchar
                 )                          AS  [WHATS_HAPPENING]
,           [YEST].* 
FROM        report_or_vals                  AS  [YEST]
INNER JOIN  report_or_vals                  AS  [TODY]
    ON      [YEST].date = DATE([TODY].DATE,'-1 day') 
/*  IF YOU COMMENT THE WHERE CLAUSE YOU WILL SEE VALUES FOR ALL THE DAYS
WHERE       [TODY].or_group_indice_id = 2
        AND report_date BETWEEN ? AND ?
*/

Is this what you want?

Comments