Solved

sql statements need help combining into 1 statement

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Check ALL SP in database make sure there are no errors 17 71
Many to one in one row 2 48
the way to learn Microsoft BI 13 70
SQL Query 20 18
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

732 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