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

Sql query help adding calculated field

I have a sql query that is working well but I need to add another calculated field to and cant seem to get it to work

My Current Query

SELECT     OIBT.ItemCode, OIBT.BatchNum, POR1.ShipDate, POR1.OpenQty, OIBT.InDate, OPOR.CardCode, OPOR.CardName,
                      DATEDIFF(day, isnull(POR1.U_SIF_VENDPROM, POR1.ShipDate),OIBT.InDate) AS dayslate
FROM         OIBT INNER JOIN      
                      OPDN ON OIBT.BaseType = OPDN.ObjType AND OIBT.BaseNum = OPDN.DocNum INNER JOIN
                      PDN1 ON OPDN.DocEntry = PDN1.DocEntry INNER JOIN
                      OPOR INNER JOIN
                      POR1 ON OPOR.DocEntry = POR1.DocEntry ON PDN1.BaseType = OPOR.ObjType AND PDN1.BaseEntry = OPOR.DocEntry AND
                      PDN1.BaseLine = POR1.LineNum
WHERE     (OPOR.U_SIF_PO_Sample = 'Y')

I would like to be able to add another field based on what is populated AS dayslate.

if dayslate (the three following conditions)

< 1 = 'On Time / Early'
< 8 = 'Late 1-7 days'
> 7 = 'Late >7 days'

AS Status
0
notasgoodasyou
Asked:
notasgoodasyou
1 Solution
 
Ephraim WangoyaCommented:
TRY
SELECT *, CASE  
            WHEN DAYSLATE < 1 THEN 'On Time / Early'
			WHEN DAYSLATE< 8 THEN  'Late 1-7 days'
			ELSE 'Late >7 days'
		  END AS [STATUS]
FROM
	(
	SELECT     OIBT.ItemCode, OIBT.BatchNum, POR1.ShipDate, POR1.OpenQty, OIBT.InDate, OPOR.CardCode, OPOR.CardName, 
						  DATEDIFF(day, isnull(POR1.U_SIF_VENDPROM, POR1.ShipDate),OIBT.InDate) AS dayslate
	FROM         OIBT INNER JOIN      
						  OPDN ON OIBT.BaseType = OPDN.ObjType AND OIBT.BaseNum = OPDN.DocNum INNER JOIN
						  PDN1 ON OPDN.DocEntry = PDN1.DocEntry INNER JOIN
						  OPOR INNER JOIN
						  POR1 ON OPOR.DocEntry = POR1.DocEntry ON PDN1.BaseType = OPOR.ObjType AND PDN1.BaseEntry = OPOR.DocEntry AND 
						  PDN1.BaseLine = POR1.LineNum
	WHERE     (OPOR.U_SIF_PO_Sample = 'Y') 
	) A

Open in new window

0
 
notasgoodasyouAuthor Commented:
perfect
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

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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