?
Solved

sql statements need help combining into 1 statement

Posted on 2011-02-28
3
Medium Priority
?
301 Views
Last Modified: 2012-05-11
I have 2 sql statements that both work well.  I need to combine them to one statement to accomplish the task i need.

The following is the main select statement that I need to incorporate a case statment into.

SELECT     OIBT.ItemCode, OIBT.BatchNum, POR1.ShipDate, POR1.OpenQty, OIBT.InDate, OPOR.CardCode, OPOR.CardName,
                      DATEDIFF(day, OIBT.InDate, POR1.ShipDate) 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 need to change the Datediff part for the field of POR1.shipdate instead i want the result from the following case statement.

select CASE WHEN por1.U_SIF_Vendprom IS NULL then por1.shipdate else por1.U_SIF_Vendprom END as REQDATE from por1


in the end i would like the above statement with   Datediff(day,OIBT.InDate, REQDATE) AS dayslate


I did try this many ways but could not get it to work.
0
Comment
Question by:notasgoodasyou
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34999266
I am not clear what you mean.
I understood like you want incoperate the case statement that you give to the above query
If so try this query
SELECT  OIBT.ItemCode ,
        OIBT.BatchNum ,
        POR1.ShipDate ,
        POR1.OpenQty ,
        OIBT.InDate ,
        OPOR.CardCode ,
        OPOR.CardName ,
        CASE WHEN POR1.U_SIF_Vendprom IS NULL THEN POR1.shipdate
             ELSE POR1.U_SIF_Vendprom
        END AS REQDATE ,
        DATEDIFF(day, OIBT.InDate, POR1.ShipDate) 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' )

Open in new window


Raj
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 2000 total points
ID: 34999272
SELECT     OIBT.ItemCode, OIBT.BatchNum, POR1.ShipDate, POR1.OpenQty, OIBT.InDate, OPOR.CardCode, OPOR.CardName,
                      DATEDIFF(day, OIBT.InDate, isnull(POR1.U_SIF_VENDPROM, POR1.ShipDate)) 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')
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 34999279
just wondering on the type of Join shouldn't it be LEFT JOIN instead of inner join ?
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

762 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