Solved

Sql query  help adding calculated field

Posted on 2011-03-18
2
324 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
AWE-based memory on 32-bit servers 1 23
Trouble viewing a table in a database. SSMS 2008 R2 4 31
T SQL Update Table from another table 5 46
Sql Query Datatype 2 19
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

867 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

18 Experts available now in Live!

Get 1:1 Help Now