• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • 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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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