Start Date based on the priority

1

Hello,

I am trying to do a report to select the "Purchase Date" based on the priority of Material Types. Here are the list of my Material Types:

  • Metal
  • Plastic
  • wood

So, on my "Start Date" column, I want to get the start date if the material is Metal. If we have the start date in Metal material then use that date.

If we don't have Metal material, then grab Start Date of Plastic material and If we have the start date in Plastic material then use that date.

If we don't have start date of plastic material then grab and use the start date from wood material.

Here is my basic select statement:

Select Material, Start_Date
From WarehouseDB

So let say the basic simple output would be like this:
Material Start-Date
Metal NULL
Plastic 11/01/2015
Wood 10/01/2015

Based on above output, I should only use the Start_Date of Plastic Material because the first priority date, which is the Metal material does not have it (NULL), the second priority is Plastic and it is available then we use this date as my Start _Date. Once we found the the Start Date, we don't care the other priority Start_Date.

Thanks guys

2 
SELECT TOP 1 *
FROM  WarehouseDB
WHERE Start_Date IS NOT NULL
ORDER BY (CASE Material WHEN 'Metal' THEN 1 WHEN 'Plastic' THEN 2 WHEN 'Wood' THEN 3 END)
1 Like
3

Thank you Khtan. It works!