Jintonix415
asked on
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!
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!
Daniel Reynolds
Can you provide any table structure for the data you are querying?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_idASKER
Thanks that helped!!!