CODEWITHSUNDEEP
mysqldatabase
Petar Ivanov
Petar Ivanov

Reputation: 51

Mysql join table on latest record by time

Hello I have the following two tables which represents a document with its stage and a history table that shows the time document was moved to next stage

CREATE TABLE `document` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `description` text,
  `stage` tinyint(4) NOT NULL DEFAULT '0',
  `customer_name` varchar(100) DEFAULT NULL,
  `creator_id` bigint(20) NOT NULL DEFAULT '0',
  `customer_city` varchar(100) DEFAULT NULL,
  `customer_state` varchar(100) DEFAULT NULL,
  `customer_zip` varchar(50) DEFAULT NULL,
  `customer_contact` varchar(100) DEFAULT NULL,
  `number` varchar(100) DEFAULT NULL,
  `latitude` float NOT NULL DEFAULT '0',
  `longitude` float NOT NULL DEFAULT '0',
  `creation_date` varchar(50) DEFAULT NULL,
  `expanded_description` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `document_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `doc_id` bigint(20) NOT NULL,
  `modifying_time` datetime DEFAULT NULL,
  `changed_to_stage` tinyint(3) unsigned DEFAULT NULL,
  `old_stage` tinyint(3) unsigned DEFAULT NULL,
  `user_modified` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I need to get count statistics based on selected period.For example I need to know how many documents were in stage 1 during selected period, but the problem is that if a document was in several different stages during this period - I have to count it only for the last stage, e.g. if doc was in stage 1 and after that in stage 2 - I need to count it only for stage 2.

Here is what I have till now:

SELECT 
round(sum(case when dh.modifying_time between '2016-09-15' and '2016-09-24' and dh.changed_to_stage = 1 then 1 else 0 end),0) entry_count,
round(sum(case when dh.modifying_time between '2016-09-15' and '2016-09-24' and dh.changed_to_stage = 3 then 1 else 0 end),0) pricing_count,
round(sum(case when dh.modifying_time between '2016-09-15' and '2016-09-24' and dh.changed_to_stage = 5 then 1 else 0 end),0) executed_count,
round(sum(case when dh.modifying_time between '2016-09-15' and '2016-09-24' and dh.changed_to_stage = 7 then 1 else 0 end),0) approved_count,
round(sum(case when dh.modifying_time between '2016-09-15' and '2016-09-24' and dh.changed_to_stage = 9 then 1 else 0 end),0) canceled_count
FROM document doc join document_history dh on doc.id=dh.doc_id

The problem here is that the document is counted twice if it was in two different stages in that period.

Any help will be appreciated.

Thanks and best regards

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You want to select just the last stage, then you can do the logic in the where. But, I would suggest moving the date condition to the WHERE first and getting rid of ROUND() (for intengers? really?):

SELECT sum(case when dh.changed_to_stage = 1 then 1 else 0 end) as entry_count,
       sum(case when dh.changed_to_stage = 3 then 1 else 0 end) as pricing_count,
       sum(case when dh.changed_to_stage = 5 then 1 else 0 end) as executed_count,
       sum(case when dh.changed_to_stage = 7 then 1 else 0 end) as approved_count,
       sum(case when dh.changed_to_stage = 9 then 1 else 0 end) as canceled_count
FROM document doc join
     document_history dh
     on doc.id = dh.doc_id 
WHERE dh.modifying_time between '2016-09-15' and '2016-09-24' and
      dh.modifying_time = (SELECT MAX(dh2.modifying_time)
                           FROM document_history dh2
                           WHERE dh2.doc_id = dh.doc_id AND
                                 dh2.modifying_time between '2016-09-15' and '2016-09-24'
                          );

Notes:

  • Don't store dates as strings (such as creation_date).
  • You should use between with dates or datetime. Your expression probably does not do what you intend. Write it as dh.modifying_time >= '2016-09-15' and dh.modifying_time < '2016-09-25', which is probably what you intend.
  • If you want to keep all documents, then use a left join instead of an inner join and move the where conditions to an on clause.
  • And round() with an integer argument? I just don't get that one.
  • And, as written, the query does not need the document table at all, only document_history. You might consider from document_history as a simplification.

Upvotes: 1

Related Questions