Sajeev C Sajeev C - 5 months ago 13
SQL Question

mysql- Unable to fetch data based on latest date and time from a pivot table

I have a table which stores application details. Applications are nothing but a set of data showing scores.

Each application has 6 parts namely 1,2,3,4,5, and 6. So, in the database table parts can have values ranging from 1 to 6.

Same application can have one or more entries saved for a part. Like, application with id 9 can have (may be) 3 entries for part 1. and for each part there is a score and score is just a numeric value.

What I'm trying to fetch is: Get scores for all 6 parts for each application but only the most recent score should be shown. So, if application 9 part 1 has a score of 8 at 01-06-2016 and the same has a score of 3 on 03-06-2016, then 3 should be shown as the score.

My table looks like this:

CREATE TABLE IF NOT EXISTS `reg_updates` (
`id` int(11) NOT NULL,
`application_id` int(11) NOT NULL,
`part` int(11) NOT NULL,
`reg_score_id` int(11) NOT NULL,
`reg_score` int(11) NOT NULL,
`done_date` date NOT NULL,
`done_time` time NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin1;


And this is the query that I have written for this purpose:

SELECT MAX(CONCAT(RU.done_date,' ',RU.done_time)) AS date,RU.application_id,
MAX(CASE WHEN (RU.part = 1 ) THEN RU.reg_score END) part1Score,
MAX(CASE WHEN RU.part = 2 THEN RU.reg_score END) part2Score,
MAX(CASE WHEN RU.part = 3 THEN RU.reg_score END) part3Score,
MAX(CASE WHEN RU.part = 4 THEN RU.reg_score END) part4Score,
MAX(CASE WHEN RU.part = 5 THEN RU.reg_score END) part5Score,
MAX(CASE WHEN RU.part = 6 THEN RU.reg_score END) part6Score
FROM reg_updates AS RU
GROUP BY RU.application_id;


But it's not fetching the proper data. I am not able to get the data based on latest done_date and done_time. I'm only getting it based on the first entry.

This is the tutorial link I followed to create this kinda pivot table.

Here's a sample SQL FIDDLE.

Answer

Well, you should first get all data with most recent datetime by application_Id and part, which will be used as an inner join

SELECT MAX(CONCAT(done_date,' ',done_time)) AS maxDate ,application_id,RU.part
FROM reg_updates 
GROUP BY application_id, part;

Then use it in your query

SELECT 
  -- I'm not sure you want that line, do you ? It will only retrieve the latest date_time of ALL your parts for that application
  MAX(CONCAT(RU.done_date,' ',RU.done_time)) AS date,
  RU.application_id,
  MAX(CASE WHEN (RU.part = 1 ) THEN RU.reg_score END)  part1Score,
  MAX(CASE WHEN RU.part = 2 THEN RU.reg_score END) part2Score,
  MAX(CASE WHEN RU.part = 3 THEN RU.reg_score END) part3Score,
  MAX(CASE WHEN RU.part = 4 THEN RU.reg_score END) part4Score,
  MAX(CASE WHEN RU.part = 5 THEN RU.reg_score END) part5Score,
  MAX(CASE WHEN RU.part = 6 THEN RU.reg_score END) part6Score
FROM reg_updates AS RU
--now you join on the previous 
join (SELECT MAX(CONCAT(done_date,' ',done_time)) AS maxDate ,application_id,part
     FROM reg_updates 
     GROUP BY application_id, part) maxValues
        on maxValues.part = RU.part and 
           maxValues.application_Id = RU.application_id and 
           maxValues.maxDate = CONCAT(RU.done_date,' ',RU.done_time)

GROUP BY RU.application_id;

By the way, you will only get the latest date_time of all the part, but it seems that it's what you want ?

Comments