I’m basically after the following:
Query to produce results of; invoice_item_price of all jobs that include a Typesetting task and all of those that don’t.
The problem I’m having is that I can’t figure out how to get the correct SUMtotal of the invoice_item_price, when searching through the tbl_task_item, to see if a Typesetting task has been completed. In trying to do this, it multiplies the invoice_item_price by the amount of tasks within a job.
Database:
-- MySQL dump 10.11
--
-- Host: localhost Database: dbtemp
-- ------------------------------------------------------
-- Server version 5.0.83-community-nt
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `tbl_analysis`
--
DROP TABLE IF EXISTS `tbl_analysis`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_analysis` (
`analysis_id` int(20) unsigned NOT NULL auto_increment,
`FK_job_id` int(20) unsigned default NULL,
`analysis_gross` int(10) default NULL,
`analysis_net` int(10) default NULL,
PRIMARY KEY (`analysis_id`),
KEY `FK_job_id` (`FK_job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tbl_analysis`
--
LOCK TABLES `tbl_analysis` WRITE;
/*!40000 ALTER TABLE `tbl_analysis` DISABLE KEYS */;
INSERT INTO `tbl_analysis` VALUES (1,1,100,50),(2,2,500,250);
/*!40000 ALTER TABLE `tbl_analysis` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tbl_customer`
--
DROP TABLE IF EXISTS `tbl_customer`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_customer` (
`cust_id` int(20) unsigned NOT NULL auto_increment,
`cust_name` varchar(255) default NULL,
PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tbl_customer`
--
LOCK TABLES `tbl_customer` WRITE;
/*!40000 ALTER TABLE `tbl_customer` DISABLE KEYS */;
INSERT INTO `tbl_customer` VALUES (1,'Customer A');
/*!40000 ALTER TABLE `tbl_customer` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tbl_invoice`
--
DROP TABLE IF EXISTS `tbl_invoice`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_invoice` (
`invoice_id` int(20) unsigned NOT NULL auto_increment,
`FK_cust_id` int(20) unsigned default NULL,
`FK_proj_id` int(20) unsigned default NULL,
`invoice_date` date default NULL,
`invoice_total` varchar(30) default NULL,
`invoice_status` enum('Pending','Sent') default NULL,
PRIMARY KEY (`invoice_id`),
KEY `FK_cust_id` (`FK_cust_id`),
KEY `FK_proj_id` (`FK_proj_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tbl_invoice`
--
LOCK TABLES `tbl_invoice` WRITE;
/*!40000 ALTER TABLE `tbl_invoice` DISABLE KEYS */;
INSERT INTO `tbl_invoice` VALUES (1,1,1,'2010-01-30','500','Sent');
/*!40000 ALTER TABLE `tbl_invoice` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tbl_invoice_item`
--
DROP TABLE IF EXISTS `tbl_invoice_item`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_invoice_item` (
`invoice_item_id` int(20) unsigned NOT NULL auto_increment,
`FK_job_id` int(20) unsigned default NULL,
`FK_invoice_id` int(20) unsigned default NULL,
`invoice_item_price` varchar(30) default NULL,
`invoice_status` enum('Pending','Sent') default 'Pending',
PRIMARY KEY (`invoice_item_id`),
KEY `FK_job_id` (`FK_job_id`),
KEY `FK_invoice_id` (`FK_invoice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tbl_invoice_item`
--
LOCK TABLES `tbl_invoice_item` WRITE;
/*!40000 ALTER TABLE `tbl_invoice_item` DISABLE KEYS */;
INSERT INTO `tbl_invoice_item` VALUES (1,1,1,'250','Sent'),(2,2,1,'250','Sent');
/*!40000 ALTER TABLE `tbl_invoice_item` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tbl_job`
--
DROP TABLE IF EXISTS `tbl_job`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_job` (
`job_id` int(20) unsigned NOT NULL auto_increment,
`FK_proj_id` int(20) unsigned default NULL,
`FK_langt_id` int(20) unsigned default NULL,
`job_title` varchar(255) default NULL,
`job_pages` int(10) default '1',
`job_complete` enum('Yes','No') default 'No',
`job_complete_date` timestamp NULL default NULL,
PRIMARY KEY (`job_id`),
KEY `FK_proj_id` (`FK_proj_id`),
KEY `FK_langt_id` (`FK_langt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tbl_job`
--
LOCK TABLES `tbl_job` WRITE;
/*!40000 ALTER TABLE `tbl_job` DISABLE KEYS */;
INSERT INTO `tbl_job` VALUES (1,1,1,'Job 1',1,'Yes','2012-30-01 17:30:00'), (2,1,3,'Job 2',2,'Yes','2012-30-01 17:30:00');
/*!40000 ALTER TABLE `tbl_job` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tbl_language`
--
DROP TABLE IF EXISTS `tbl_language`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_language` (
`lang_id` int(20) unsigned NOT NULL auto_increment,
`langname` varchar(255) default NULL,
PRIMARY KEY (`lang_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tbl_language`
--
LOCK TABLES `tbl_language` WRITE;
/*!40000 ALTER TABLE `tbl_language` DISABLE KEYS */;
INSERT INTO `tbl_language` VALUES (1,'EN'),(2,'DE'),(3,'FR');
/*!40000 ALTER TABLE `tbl_language` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tbl_project`
--
DROP TABLE IF EXISTS `tbl_project`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_project` (
`proj_id` int(20) unsigned NOT NULL auto_increment,
`proj_title` varchar(255) default NULL,
`FK_cust_id` int(20) unsigned default NULL,
`FK_langsid` int(20) unsigned default NULL,
`proj_start_date` timestamp NULL default NULL,
`proj_end_date` timestamp NULL default NULL,
`proj_status` enum('Open','Hold','Enquiry','Redundant','Complete') default NULL,
PRIMARY KEY (`proj_id`),
KEY `FK_cust_id` (`FK_cust_id`),
KEY `FK_langsid` (`FK_langsid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tbl_project`
--
LOCK TABLES `tbl_project` WRITE;
/*!40000 ALTER TABLE `tbl_project` DISABLE KEYS */;
INSERT INTO `tbl_project` VALUES (1,'Project A', 1,2,'2012-01-30 09:00:00','2012-01-30 17:30:00','Complete');
/*!40000 ALTER TABLE `tbl_project` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tbl_task`
--
DROP TABLE IF EXISTS `tbl_task`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_task` (
`task_id` int(20) unsigned NOT NULL auto_increment,
`task_title` varchar(50) default NULL,
PRIMARY KEY (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tbl_task`
--
LOCK TABLES `tbl_task` WRITE;
/*!40000 ALTER TABLE `tbl_task` DISABLE KEYS */;
INSERT INTO `tbl_task` VALUES (1,'Preparation'),(2,'Typesetting'),(3,'Correction'),(4,'Evaluate');
/*!40000 ALTER TABLE `tbl_task` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tbl_task_item`
--
DROP TABLE IF EXISTS `tbl_task_item`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_task_item` (
`task_item_id` int(20) unsigned NOT NULL auto_increment,
`FK_job_id` int(20) unsigned default NULL,
`FK_task_id` int(20) unsigned default NULL,
`status` enum('Pending','Ready','Sent','Active','Complete') default 'Pending',
`task_complete_date` timestamp NULL default NULL,
`task_order` int(20) unsigned default NULL,
PRIMARY KEY (`task_item_id`),
KEY `FK_task_id` (`FK_task_id`),
KEY `FK_job_id` (`FK_job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tbl_task_item`
--
LOCK TABLES `tbl_task_item` WRITE;
/*!40000 ALTER TABLE `tbl_task_item` DISABLE KEYS */;
INSERT INTO `tbl_task_item` VALUES (1,1,1,'Complete','2012-01-30 10:00:00',1),(2,1,2,'Complete','2012-01-30 11:00:00',2),(3,1,3,'Complete','2012-01-30 12:00:00',3),(4,1,4,'Complete','2012-01-30 17:30:00',4),(5,2,1,'Complete','2012-01-30 10:00:00',1),(6,2,3,'Complete','2012-01-30 12:00:00',3),(7,2,4,'Complete','2012-01-30 17:30:00',4);
/*!40000 ALTER TABLE `tbl_task_item` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2012-01-30 18:59:47
QUERY:
SELECT DATE_FORMAT(proj_end_date, '%m') as month_number,
MONTHNAME(proj_end_date) AS the_month,
YEAR(proj_end_date) AS the_year,
SUM(analysis_net) as analysis_net,
SUM(analysis_gross) as analysis_gross,
SUM(job_pages) as job_pages,
SUM(invoice_item_price) as total,
CASE WHEN task_title='Typesetting'
AND ti1.status='Complete'
THEN 'Typeset'
ELSE 'Non-Typeset'
END AS 'JobType',
langname
FROM tbl_job as j
INNER JOIN tbl_task_item as ti1
ON ti1.FK_job_id = j.job_id
INNER JOIN tbl_task as t
ON t.task_id = ti1.FK_task_id
INNER JOIN tbl_invoice_item as ii
ON ii.FK_job_id = j.job_id
INNER JOIN tbl_invoice as i
ON i.invoice_id=ii.FK_invoice_id
INNER JOIN tbl_analysis as a
ON a.FK_job_id = j.job_id
INNER JOIN tbl_language as l
ON l.lang_id = j.FK_langt_id
INNER JOIN tbl_project as p
ON p.proj_id = j.FK_proj_id
WHERE p.FK_cust_id = 1
AND proj_status='Complete'
GROUP BY the_year,
month_number,
JobType,
langname
ORDER BY the_year DESC,
month_number DESC,
JobType DESC,
langname ASC