Solved

need to get counts even if there are zero

Posted on 2013-11-11
2
268 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

739 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