CODEWITHSUNDEEP
sqlsql-servert-sql
gopi nath
gopi nath

Reputation: 196

SQL to display value for different dates

I have a table named Reading_Hist containing columns such as Reading, Date, ID. This table contains history of the readings. example

+----+---------+-------------+
| ID | Reading | ReadingDate |
+----+---------+-------------+
|  1 |      12 | 9/12/2018   |
|  2 |      15 | 9/12/2018   |
|  1 |      16 | 9/5/2018    |
|  4 |       1 | 9/12/2018   |
|  3 |      65 | 9/12/2018   |
|  1 |      23 | 8/29/2018   |
|  3 |      25 | 9/5/2018    |
|  2 |      23 | 9/5/2018    |
|  4 |       3 | 9/5/2018    |
+----+---------+-------------+

I want to write a sql to display each ID and it's current Reading on first column, next reading taken a week before and third reading taken two weeks before and last trend of the reading.

Example Result below. 

+----+---------+------+------+-------+
| ID | Current | Wk_1 | Wk_2 | Trend |
+----+---------+------+------+-------+
|  1 |      12 |   16 | 23   | Down  |
|  2 |      15 |   23 | NULL | Down  |
|  3 |      65 |   25 | NULL | UP    |
|  4 |       1 |    3 | NULL | Down  |
+----+---------+------+------+-------+

Upvotes: 0

Views: 54

Answers (1)

sticky bit
sticky bit

Reputation: 37487

You can use aggregation to get the maximum day of readings per ID. Then left join the current readings, them of the last week and two weeks ago. Use CASE to calculate the trend.

It could look something like:

SELECT x.id,
       rh2.reading current,
       rh3.reading wk_1,
       rh4.reading wk_2,
       CASE
         WHEN rh2.reading > rh3.reading THEN
           'Up'
         WHEN rh2.reading < rh3.reading THEN
           'Down'
         WHEN rh2.reading = rh3.reading THEN
           '-'
       END trend
       FROM (SELECT rh1.id,
                    max(rh1.reading_date) reading_date
                    FROM reading_hist rh1
                    GROUP BY rh1.id) x
            LEFT JOIN reading_hist rh2
                      ON rh2.id = x.id
                         AND rh2.reading_date = x.reading_date
            LEFT JOIN reading_hist rh3
                      ON rh3.id = x.id
                         AND rh3.reading_date = dateadd(day, -7, x.reading_date)
            LEFT JOIN reading_hist rh4
                      ON rh4.id = x.id
                         AND rh4.reading_date = dateadd(day, -14, x.reading_date);

Of course this requires, that there are readings exactly 7 or 14 days from the last day of readings.

Upvotes: 1

Related Questions