Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

using Case in Select statement and setting time frame

Posted on 2013-10-28
5
Medium Priority
?
342 Views
Last Modified: 2013-11-12
I would expect the query below to name the case column according to the date range - however all are defaulting to error - what am I missing? It has been quite sometime since I have worked in mySQL
        SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY)AS rDate,

        CURDATE()AS rDate,DATE_ADD(CURDATE(), INTERVAL 1 DAY)AS rDate,

        DATE_ADD(CURDATE(), INTERVAL 7 DAY)AS rDate,

        DATE_ADD(CURDATE(), INTERVAL 14 DAY)AS rDate,

        DATE_ADD(CURDATE(), INTERVAL 30 DAY)AS rDate,

        DATE_ADD(CURDATE(), INTERVAL 60 DAY)AS rDate,

        DATE_ADD(CURDATE(), INTERVAL 90 DAY)AS rDate,

        CASE a.nextDueDate

        WHEN a.nextDueDate < CURDATE() THEN 'Overdue'

        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 'Error'

        END,

        g.groupId,g.name AS groupName,l.logId,l.name AS logName, i.itemID,

        i.name AS itemName, le.userName,completed, i.optimalMin,i.optimalMax ,le.value,

        u.name AS unitDescription,

        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,scheduleType,scheduleInterval,a.details,nextDueDate

        FROM logExceptions le

        INNER JOIN logs l ON l.logID = le.logID

        INNER JOIN groups g ON g.groupId = l.groupId

        INNER JOIN items i ON le.itemId = i.itemId

        INNER JOIN activities a ON l.logId = a.logId

        LEFT JOIN units u ON i.unitId = u.unitId

        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
  • 3
5 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39607841
The first issue I have observed is the column alias names. You have same column alias rDate for many columns.
I have changed them. Can you check what other issues you are getting with this.
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS rDate1, 
       CURDATE()                           AS rDate2, 
       DATE_ADD(CURDATE(), INTERVAL 1 DAY) AS rDate3, 
       DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS rDate4, 
       DATE_ADD(CURDATE(), INTERVAL 14 DAY)AS rDate5, 
       DATE_ADD(CURDATE(), INTERVAL 30 DAY)AS rDate6, 
       DATE_ADD(CURDATE(), INTERVAL 60 DAY)AS rDate7, 
       DATE_ADD(CURDATE(), INTERVAL 90 DAY)AS rDate8, 
       CASE a.nextDueDate 
         WHEN a.nextDueDate < CURDATE() THEN 'Overdue' 
         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 'Error' 
       END AS nextDueDate_Range, 
       g.groupId, 
       g.name                              AS groupName, 
       l.logId, 
       l.name                              AS logName, 
       i.itemID, 
       i.name                              AS itemName, 
       le.userName, 
       completed, 
       i.optimalMin, 
       i.optimalMax, 
       le.value, 
       u.name                              AS unitDescription, 
       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, 
       scheduleType, 
       scheduleInterval, 
       a.details, 
       nextDueDate 
  FROM logExceptions le 
       INNER JOIN logs l 
               ON l.logID = le.logID 
       INNER JOIN groups g 
               ON g.groupId = l.groupId 
       INNER JOIN items i 
               ON le.itemId = i.itemId 
       INNER JOIN activities a 
               ON l.logId = a.logId 
       LEFT JOIN units u 
              ON i.unitId = u.unitId 
       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
 

Author Comment

by:portlight
ID: 39608210
I am aware of the duplicate alias's - those fields are not actually needed/used. I should have taken them out....I am still getting all 'error' for the CASE...This is the section that I am needing assistance with. It is as if the comparison is NOT working in the CASE statement.

Here is the query - I made the change you suggested...No change in results

SELECT 
    CASE a.nextDueDate 
         WHEN a.nextDueDate < CURDATE() THEN 'Overdue' 
         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 'Error' 
       END AS nextDueDate_Range, 
    g.groupId,g.name AS groupName,l.logId,l.name AS logName, i.itemID,
      i.name AS itemName, le.userName,completed, i.optimalMin,i.optimalMax ,le.value,
      u.name AS unitDescription,
      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,scheduleType,scheduleInterval,a.details,nextDueDate
       
  FROM logExceptions le
      INNER JOIN logs l ON l.logID = le.logID
      INNER JOIN groups g ON g.groupId = l.groupId
      INNER JOIN items i ON le.itemId = i.itemId
      INNER JOIN activities a ON l.logId = a.logId
      LEFT JOIN units u ON i.unitId = u.unitId
      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
 
LVL 41

Expert Comment

by:Sharath
ID: 39610825
very interesting. got correct result and all errors for same query.

http://sqlfiddle.com/#!2/7eef8/1
0
 
LVL 41

Accepted Solution

by:
Sharath earned 1500 total points
ID: 39610831
overlooked at your query. You have

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE


This gives
CASE nextDueDate WHEN False THEN Print1' WHEN True THEN 'Print2' ELSE 'Error' END

Comparing nextDueDate  with true/false value is always false as this is datetime column.  remove the nextDueDate  in the CASE statement.
try this.
SELECT 
    CASE 
         WHEN a.nextDueDate < CURDATE() THEN 'Overdue' 
         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 'Error' 
       END AS nextDueDate_Range, 
    g.groupId,g.name AS groupName,l.logId,l.name AS logName, i.itemID,
      i.name AS itemName, le.userName,completed, i.optimalMin,i.optimalMax ,le.value,
      u.name AS unitDescription,
      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,scheduleType,scheduleInterval,a.details,nextDueDate
       
  FROM logExceptions le
      INNER JOIN logs l ON l.logID = le.logID
      INNER JOIN groups g ON g.groupId = l.groupId
      INNER JOIN items i ON le.itemId = i.itemId
      INNER JOIN activities a ON l.logId = a.logId
      LEFT JOIN units u ON i.unitId = u.unitId
      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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

927 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