Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

need to get counts even if there are zero

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
portlight
Asked:
portlight
1 Solution
 
chaauCommented:
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
 
portlightAuthor Commented:
This worked PERECTLY! Thank you
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now