Solved

sql statements need help combining into 1 statement

Posted on 2011-02-28
3
296 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
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 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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, …

685 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