Solved

Sql query  help adding calculated field

Posted on 2011-03-18
2
313 Views
Last Modified: 2012-05-11
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
Comment
Question by:notasgoodasyou
2 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35169218
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
 

Author Closing Comment

by:notasgoodasyou
ID: 35169238
perfect
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
query help 18 52
How to place a condition in a filter criteria in t-sql? 12 36
Sql query 34 22
Filtered index 5 0
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now