MySQL Query Help

[kool_samule]

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

[fenway]

I'm confused by your design -- and why you're using non-aggregated fields in your SELECT list.

[kool_samule]

MySQL Design is broken down into seperate tables, for a pretty big database...what i've posted is a stripped down version, purely for this problem.

 

The SELECT query doesn't work as I want it to...it's just an example of what i've got so far...but the INNER JOINS obviously are required.

 

The JobType is determined by the task_title 'Typesetting', i.e. if the job has a Typesetting task, then the job as a whole is classed as a 'Typeset Job'.

 

The result I'm after is something to show the SUM of job_pages, analysis_gross, analysis_net and invoice_item_price per job.

 

The results need to be grouped by year/month/jobtype (Typeset/Non-Typeset)/langname

 

Is this possible?

[fenway]

Instead of grouping by the date parts separately, simply use  YEARMONTH(), and then you can format it on output.

[kool_samule]

Thanks for the reply, however I'm getting an error: YEARMONTH does not exist.

 

The main problem I'm having is with the task_items / jobs, rather than the year/month/dates.

 

Hopefully this might explain the problem a bit better:

 

Job 1 has an invoice price of 250.

Job 1 has 4 tasks, 1 of which is Typesetting.

 

Job 2 has an invoice price of 250.

Job 2 has 3 tasks, 0 of which are Typesetting.

 

I want the query to produce a result of:

Typeset Job - 250 (Job 1 - has Typesetting Task)

Non Typeset Job - 250 (Job 2 - has no Typesetting Task)

 

Whats happening is:

Typeset Job - 250 (Job 1 - has 1 Typesetting Task)

Non Typeset Job - 750 (Job 1 - has 3 non-Typesetting Tasks)

Non Typeset Job - 750 (Job 2 - has 3 non-Typesetting Tasks)

 

Any ideas on this?

[fenway]

No, the problem you're having is with the dates -- because you've turned one column into two.

 

EXTRACT(YEAR_MONTH FROM yourDate).

[kool_samule]

Hi again, thanks for the response, I tried:

 

SELECT 		EXTRACT(YEAR_MONTH FROM proj_end_date),
		SUM(invoice_item_price) as total, 
CASE		WHEN task_title='Typesetting'
		AND ti1.status='Complete'
		THEN 'Typeset'
		ELSE 'Non-Typeset'
		END AS 'JobType'
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		JobType 
ORDER BY		JobType DESC

 

and this gives me:

 

EXTRACT(YEAR_MONTH FROM proj_end_date) - total - JobType
201201                                 - 250   - Typeset
201201                                 - 1500  - Non-Typeset

 

 

But the 'total' figures are not what I'm after...as mentioned in my previous posts, I want to class all jobs that have had a 'Typesetting' task as a Typeset job, and those that didn't as 'Non-Typeset'.

What is happening with the query at the moment is it is multiplying each task by the job invoice_item_price, rather than each job once.

[fenway]

That because you need to group by the YEAR_MONTH as well -- so simply alias that column, and all it to the GROUP BY clause.

[kool_samule]

Sorry, same result when using:

SELECT 			EXTRACT(YEAR_MONTH FROM proj_end_date) as job_date,
		SUM(invoice_item_price) as total, 
CASE		       	WHEN task_title='Typesetting'
		AND ti1.status='Complete'
		THEN 'Typeset'
		ELSE 'Non-Typeset'
		END AS 'JobType'
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		job_date, JobType 
ORDER BY		job_date, JobType DESC

 

I still feel the invoice_item_price / job / tasks are the issue though...

[mikosiko]

maybe?

SELECT EXTRACT(YEAR_MONTH FROM proj_end_date) as job_date,
             CASE WHEN task_title='Typesetting' AND ti1.status='Complete'
	      THEN SUM(invoice_item_price)
	      ELSE invoice_item_price
     END AS total,

CASE		       	WHEN task_title='Typesetting'
		AND ti1.status='Complete'
		THEN 'Typeset'
		ELSE 'Non-Typeset'
		END AS 'JobType'
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		job_date, JobType
ORDER BY		job_date, JobType DESC;

[fenway]

If it's still an issue, the post the new output, they expected output, and I'll take a closer look.

[kool_samule]

OK, getting close...

 

QUERY:

SELECT 		EXTRACT(YEAR_MONTH FROM proj_end_date) as Jobdate,
CASE		WHEN task_title='Typesetting'
	AND ti1.status='Complete'
	THEN 'Typeset'
	ELSE 'Non-Typeset'
	END AS 'JobType',
CASE		WHEN task_title='Typesetting'
	AND ti1.status='Complete'
	THEN SUM(job_pages)
	ELSE job_pages
	END AS 'JobPages',
CASE		WHEN task_title='Typesetting'
	AND ti1.status='Complete'
	THEN SUM(analysis_gross)
	ELSE analysis_gross
	END AS 'AnalysisGross',
CASE		WHEN task_title='Typesetting'
	AND ti1.status='Complete'
	THEN SUM(analysis_net)
	ELSE analysis_net
	END AS 'AnalysisNet',
CASE WHEN 	task_title='Typesetting' AND ti1.status='Complete'
	THEN SUM(invoice_item_price)
	ELSE invoice_item_price
	END AS JobPrice
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	JobDate, JobType
ORDER BY	JobDate, JobType DESC;

 

OUTPUT:

JobDate   - JobType   - JobPages - AnalysisGross - AnalysisNet - JobPrice
201201   - Typeset    - 1        - 100           - 50          - 250
201201   - Non-Typest - 1        - 100           - 50          - 250

 

EXPECTED OUTPUT:

JobDate   - JobType   - JobPages - AnalysisGross - AnalysisNet - JobPrice
201201   - Typeset    - 1        - 100           - 50          - 250
201201   - Non-Typest - 2        - 500           - 250         - 250

 

Thank you for the help!

[mikosiko]

just to validate a couple things:

a) What kind of relationship is between tbl_JOBS and tbl_Analysis?  1:n or 1:1 ?  ... if the relationship is 1:1 as it seems to be, you don't need to SUM() the columns analysis_gross nor analysis_net.

 

b) run this query and post the results:

SELECT b.FK_job_id,
       a.task_id,
       a.task_title,
       b.status AS TaskItem_Status
FROM tbl_task AS a
     JOIN tbl_task_item AS b ON a.task_id = b.FK_task_id
ORDER BY b.FK_job_id, a.task_id;

 

c) run this query and check if the results are consistent with the results of b)

SELECT  j.job_id As Job,
        EXTRACT(YEAR_MONTH FROM proj_end_date) as Jobdate,

        CASE WHEN   task_title='Typesetting' AND ti1.status='Complete'
             THEN 'Typesetting'
             ELSE 'Non-Typest'
             END AS 'JobType',

        CASE WHEN 	task_title='Typesetting' AND ti1.status='Complete'
            THEN SUM(j.job_pages)
            ELSE j.job_pages
            END AS JobPages,

        a.analysis_gross AS 'AnalysisGross',
        a.analysis_net AS 'AnalysisNet',
        
        CASE WHEN   task_title='Typesetting' AND ti1.status='Complete'
            THEN SUM(ii.invoice_item_price)
            ELSE ii.invoice_item_price
            END AS JobPrice

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_project as p       ON p.proj_id = j.FK_proj_id
WHERE p.FK_cust_id = 1
  AND p.proj_status='Complete'
GROUP BY 	Job, JobDate, JobType
ORDER BY 	Job, JobDate, JobType DESC;

 

[kool_samule]

OK, getting really close now...

 

a) Relationship is 1:1

 

b)

+-----------+---------+-------------+-----------------+
| FK_job_id | task_id | task_title  | TaskItem_Status |
+-----------+---------+-------------+-----------------+
|         1 |       1 | Preparation | Complete        |
|         1 |       2 | Typesetting | Complete        |
|         1 |       3 | Correction  | Complete        |
|         1 |       4 | Evaluate    | Complete        |
|         2 |       1 | Preparation | Complete        |
|         2 |       3 | Correction  | Complete        |
|         2 |       4 | Evaluate    | Complete        |
+-----------+---------+-------------+-----------------+
7 rows in set (0.07 sec)

 

c)

+-----+---------+-------------+----------+---------------+-------------+----------+
| Job | Jobdate | JobType     | JobPages | AnalysisGross | AnalysisNet | JobPrice |
+-----+---------+-------------+----------+---------------+-------------+----------+
|   1 |  201201 | Typesetting |        1 |           100 |          50 | 250      |
|   1 |  201201 | Non-Typest  |        1 |           100 |          50 | 250      |
|   2 |  201201 | Non-Typest  |        2 |           500 |         250 | 250      |
+-----+---------+-------------+----------+---------------+-------------+----------+
3 rows in set (0.21 sec)

 

c) is the best one so far...just need to remove:

|   1 |  201201 | Non-Typest  |        1 |           100 |          50 | 250      |

[mikosiko]

c) is the best one so far...just need to remove:

Code: [select]

|  1 |  201201 | Non-Typest  |        1 |          100 |          50 | 250      |

 

 

under which condition?  at first glance doesn't look possible in the current query (I could be wrong)... but... that is for you to work on... 2 hints for an alternative solution .... 1) Divide to Reign... 2) UNION

 

 

 

[kool_samule]

Because Job 1 has a Typesetting Task, I dont want it included in Non-Typesetting.

 

The current query isnt doing it, but that's the result I'm after..

[mikosiko]

apply the 2 hints then... try... it should work ... just re-think the query a little starting with what you have now... remember... divide ... union ... that is one way to do it

[kool_samule]

OK, I'm stuck...I've tried to alter the SELECT query, with no joy...

 

I don't know how Divide would help here...

 

and I can't figure out a way to differentiate between jobs that have a Typesetting task and those that don't...without getting the above results.

 

Would a SubQuery do the trick?

 

[mikosiko]

ok... here is one way to get the results that you were looking for... (DIVIDE the problem and UNION):

 

SELECT  j.job_id As Job,
        EXTRACT(YEAR_MONTH FROM proj_end_date) as Jobdate,
        'Typesetting' AS 'JobType',
        SUM(j.job_pages) AS 'JobPages',
        a.analysis_gross AS 'AnalysisGross',
        a.analysis_net AS 'AnalysisNet',
        SUM(ii.invoice_item_price) AS JobPrice
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 AND t.task_title='Typesetting'
      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_project as p       ON p.proj_id = j.FK_proj_id
WHERE p.FK_cust_id = 1
  AND proj_status='Complete'


UNION

SELECT  j.job_id As Job,
        EXTRACT(YEAR_MONTH FROM proj_end_date) as Jobdate,
        'Non-Typest' AS 'JobType',
        SUM(j.job_pages) AS 'JobPages',
        a.analysis_gross AS 'AnalysisGross',
        a.analysis_net AS 'AnalysisNet',
        ii.invoice_item_price AS JobPrice
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 AND t.task_title !='Typesetting'
      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_project as p       ON p.proj_id = j.FK_proj_id
WHERE p.FK_cust_id = 1
  AND proj_status ='Complete'
  AND j.job_id NOT IN (SELECT  x.job_id
                         FROM  tbl_job as x
                               INNER JOIN 	tbl_task_item as tti   ON tti.FK_job_id = x.job_id
                               INNER JOIN   tbl_task as tt          ON tt.task_id = tti.FK_task_id AND tt.task_title ='Typesetting')

GROUP BY	Job, JobDate,JobType
ORDER BY	Job,JobDate, JobType DESC;

 

and the results:

Job  Jobdate    JobType        JobPages AnalysisGross AnalysisNet JobPrice

1    201201 'Typesetting'              1                100              50    250

2    201201 'Non-Typest'              6                  500            250    250

[kool_samule]

Perfect, thanks a lot...would never have got there without your help.

 

Much appreciated...

 

Memory bank: updated.