Solved

need to get counts even if there are zero

Posted on 2013-11-11
2
263 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now