Solved

using Case in Select statement and setting time frame

Posted on 2013-10-28
5
337 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 40

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 40

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 40

Accepted Solution

by:
Sharath earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
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…
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 …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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