Solved

need to get counts even if there are zero

Posted on 2013-11-11
2
266 Views
Last Modified: 2013-11-17
I have the following query - I need to be able to get a count for EACH 'nextDueDateRange' even if the count is zero

So in addition to listing things as they are (each individual row) I need tally's and have just not been able to figure it out
SELECT
      CASE  
         WHEN a.nextDueDate < CURDATE() THEN 'Overdue' 
         WHEN a.nextDueDate = CURDATE() THEN 'Today'
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 7 DAY) )THEN '7 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 14 DAY) )THEN '15 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 30 DAY) )THEN '30 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 60 DAY) )THEN '60 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 90 DAY) )THEN '90 Days'
         ELSE 'Greater Than 90 Days' 
       END AS nextDueDateRange,
     g.groupId,g.name AS groupName,l.logId,l.name AS logName, 
     g1.parentId AS parentId1, g1.name AS group1Name,g2.parentId AS parentId2, g2.name AS group2Name,
     g3.parentId AS parentId3, g3.name AS group3Name,
     a.name AS activityName,a.scheduleType,a.scheduleInterval,a.details,nextDueDate
       
  FROM activities a
      INNER JOIN logs l ON l.logID = a.logId
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId
    WHERE nextDueDate IS NOT NULL

Open in new window

0
Comment
Question by:portlight
2 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39640690
MySQL does not support CTE. Therefore you need to use a workaround. You need to use this small view as a subquery JOINED with your main query:
SELECT 'Overdue' AS nextDueDateRange
UNION ALL
SELECT 'Overdue' AS nextDueDateRange
UNION ALL
SELECT 'Today' AS nextDueDateRange
UNION ALL
SELECT '7 Days' AS nextDueDateRange
UNION ALL
SELECT '15 Days' AS nextDueDateRange
UNION ALL
SELECT '30 Days' AS nextDueDateRange
UNION ALL
SELECT '60 Days' AS nextDueDateRange
UNION ALL
SELECT '90 Days' AS nextDueDateRange
UNION ALL
SELECT 'Greater Than 90 Days' AS nextDueDateRange

Open in new window

So, the resulting query will look like this:
SELECT r.nextDueDateRange,
a.groupId,
a.groupName,
a.logId,
a.logName, 
a.parentId1,
a.group1Name,
a.parentId2, 
a.group2Name,
a.parentId3, 
a.group3Name,
a.activityName,
a.scheduleType,
a.scheduleInterval,
a.details,
a.nextDueDate
  FROM (
SELECT 'Overdue' AS nextDueDateRange
UNION ALL
SELECT 'Overdue' AS nextDueDateRange
UNION ALL
SELECT 'Today' AS nextDueDateRange
UNION ALL
SELECT '7 Days' AS nextDueDateRange
UNION ALL
SELECT '15 Days' AS nextDueDateRange
UNION ALL
SELECT '30 Days' AS nextDueDateRange
UNION ALL
SELECT '60 Days' AS nextDueDateRange
UNION ALL
SELECT '90 Days' AS nextDueDateRange
UNION ALL
SELECT 'Greater Than 90 Days' AS nextDueDateRange
) AS r
 LEFT JOIN 
(
SELECT
      CASE  
         WHEN a.nextDueDate < CURDATE() THEN 'Overdue' 
         WHEN a.nextDueDate = CURDATE() THEN 'Today'
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 7 DAY) )THEN '7 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 14 DAY) )THEN '15 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 30 DAY) )THEN '30 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 60 DAY) )THEN '60 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 90 DAY) )THEN '90 Days'
         ELSE 'Greater Than 90 Days' 
       END AS nextDueDateRange,
     g.groupId,g.name AS groupName,l.logId,l.name AS logName, 
     g1.parentId AS parentId1, g1.name AS group1Name,g2.parentId AS parentId2, g2.name AS group2Name,
     g3.parentId AS parentId3, g3.name AS group3Name,
     a.name AS activityName,a.scheduleType,a.scheduleInterval,a.details,nextDueDate
       
  FROM activities a
      INNER JOIN logs l ON l.logID = a.logId
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId
    WHERE nextDueDate IS NOT NULL) a ON
a.nextDueDateRange = r.nextDueDateRange

Open in new window

A bit complex, but not scary
0
 

Author Closing Comment

by:portlight
ID: 39654720
This worked PERECTLY! Thank you
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Create a Select Query and Populate a Table 3 48
join tables 4 55
PHP: Insert Data into MySQL 5 40
Slow MySQL (InnoDB) Query with Inner Join & GroupBy 7 44
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question