Solved

using Case in Select statement and setting time frame

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 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

12 Experts available now in Live!

Get 1:1 Help Now