• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

Creating a SQL query to show an age date of an order

Hello,

I am trying to create a query using a Cast function that will show an age of an order that is determined by the number of days between the current date and the order date. The values used are  Less than 14 days, less than 30 days, less than 90 days, etc. in a separate column.

Thanks for your help!
0
Jintonix415
Asked:
Jintonix415
2 Solutions
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
Can you provide any table structure for the data you are querying?
0
 
tigin44Commented:
is that you want something like this

SELECT CASE WHEN DATEDIFF(DAY, GETDATE(), orderDate) < 14 THEN 'Less than 14 days'
                  WHEN DATEDIFF(DAY, GETDATE(), orderDate) < 30 THEN 'Less than 30 days'
                  WHEN DATEDIFF(DAY, GETDATE(), orderDate) < 90 THEN 'Less than 90 days'
            END
FROM yourTable
0
 
Jintonix415Author Commented:
tigin44 I tried your logic, however, when I attempt that all my results are less than 14 days. One example is that order date displays 2010-10-15 so it should be over 180 days. Here is the full query I tried.

SELECT      prod_id,
            order_date,
      CASE
      WHEN DATEDIFF(DAY, GETDATE(), order_Date) < 14 THEN 'Less than 14 days'
      WHEN DATEDIFF(DAY, GETDATE(), order_Date) < 30 THEN 'Less than 30 days'
    WHEN DATEDIFF(DAY, GETDATE(), order_Date) < 90 THEN 'Less than 90 days'
    WHEN DATEDIFF(DAY, GETDATE(), order_Date) >= 180 THEN '180 days or more'
    END "Age"
FROM Orders_stats
Order By order_date, prod_id

GO
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ephraim WangoyaCommented:
Put your order date first
SELECT      prod_id,
            order_date,
      CASE
      WHEN DATEDIFF(DAY, order_Date, GETDATE()) < 14 THEN 'Less than 14 days'
      WHEN DATEDIFF(DAY, order_Date, GETDATE()) < 30 THEN 'Less than 30 days'
    WHEN DATEDIFF(DAY, order_Date, GETDATE()) < 90 THEN 'Less than 90 days'
    WHEN DATEDIFF(DAY, order_Date, GETDATE()) >= 180 THEN '180 days or more'
    END "Age"
FROM Orders_stats
Order By order_date, prod_id

Open in new window

0
 
Ephraim WangoyaCommented:
You dont even have to test for the last condition, just use else
SELECT  prod_id, order_date,
    CASE
      WHEN DATEDIFF(DAY, order_Date, GETDATE()) < 14 THEN 'Less than 14 days'
      WHEN DATEDIFF(DAY, order_Date, GETDATE()) < 30 THEN 'Less than 30 days'
      WHEN DATEDIFF(DAY, order_Date, GETDATE()) < 90 THEN 'Less than 90 days'
      ELSE '180 days or more'
    END "Age"
FROM Orders_stats
Order By order_date, prod_id

Open in new window

0
 
Jintonix415Author Commented:
Thanks that helped!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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