SQL Syntax 1

[NotSQL]

Hi Guys, I wonder if someone could help me with some SQL.

I have the following syntax:

SELECT
RIGHT(Ord.Contract, 4) AS Contract,
CASE WHEN Ord.Status IN ('10', '15') THEN '10/15' ELSE Ord.Status END AS ReqStatus,
Ord.Part,
Ord.[Order],
ISNULL(Ord.StartDate, Ord.DueDate) AS StartDate,
Ord.DueDate,
CASE WHEN RIGHT(Ord.Planner, 1) IN ('S','Z') THEN Ord.DueDate ELSE Ord.StartDate END AS MeasureDate,
Con.TypeCode
Inv.CommodityCode,

FROM ORD_ITEMS Ord LEFT OUTER JOIN
INVENTORY_MASTER Inv ON Ord.Part = Inv.Item LEFT OUTER JOIN
CONTRACT_HEADER Con ON Ord.Contract = Con.Contract

The question i ask is how do i put the following logic into the SQL statement, remembering MeasureDate is made up of other things.

Case When MeasureDate < getdate()-21 then 1 when MeasureDate > getdate()-22 and MeasureDate < getdate()+1 then 2 when MeasureDate > getdate() and MeasureDate < getdate()+31 then 3 when MeasureDate > getdate()+30 and MeasureDate < getdate()+93 then 4 else 5 end

Thanks

 

[Glasgow]

I think you could get away with:

SELECT *, MeasureDate= CASE ETC....
FROM
(your SQL as above) T1

where the brackets are significant and the T1 is a table alias for the sub-query.

 

[Glasgow]

Actually you should perhaps call it NewMeasureDate in the first SELECT or I think you'll get back two columns with the same name.

 

[Glasgow]

You could also embed the CASE in the main SELECT but it may be a little less legible.

 

[NotSQL]

Am i correct with this? It seems to work ok.

Select *, Case When MeasureDate < getdate()-21 then 1
when MeasureDate > getdate()-22 and MeasureDate < getdate()+1 then 2
when MeasureDate > getdate() and MeasureDate < getdate()+31 then 3
when MeasureDate > getdate()+30 and MeasureDate < getdate()+93 then 4 else 5 end as ReqOnTimeStatus
From
(SELECT
RIGHT(Ord.Contract, 4) AS Contract,
CASE WHEN Ord.Status IN ('10', '15') THEN '10/15' ELSE Ord.Status END AS ReqStatus,
Ord.Part,
Ord.[Order],
ISNULL(Ord.StartDate, Ord.DueDate) AS StartDate,
Ord.DueDate,
CASE WHEN RIGHT(Ord.Planner, 1) IN ('S','Z') THEN Ord.DueDate ELSE Ord.StartDate END AS MeasureDate,
Con.TypeCode
Inv.CommodityCode,
FROM ORD_ITEMS Ord LEFT OUTER JOIN
INVENTORY_MASTER Inv ON Ord.Part = Inv.Item LEFT OUTER JOIN
CONTRACT_HEADER Con ON Ord.Contract = Con.Contract)t1

 

[Glasgow]

I can't guarantee it but that's roughly what I had in mind - i.e. you implemented the suggestion in my initial post and used equivalent syntax to my afterthought in 2nd post.