CODEWITHSUNDEEP
sqlsql-server-2012
Kajah User
Kajah User

Reputation: 591

Query to add few fields in subquery

select 
    T0.DocDate as [GRPO Posting Date],
    T0.DocNum as [GRPO Num]
    ,T0.CardName 
from OPDN T0 
where 
    year(T0.DocDate) ='2016'
    and (dateadd(day, 1, T0.DocDate) in (
        select odln.DocDueDate 
        from odln 
        where year(DocDueDate) = '2016'
    )

This is my query which works fine.But I need to get fetch other information. I don't know how to add this to the existing query.

For e.g. apart from grpo posting date, GRPO Num, CardName, I need to fetch odln.docnum, odln.docduedate which I have using in sub query.

Please help me to amend this query or any new query with that requirements.

Upvotes: 0

Views: 36

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

What you want is an INNER JOIN to odln:

SELECT
    T0.DocDate AS [GRPO Posting Date],
    T0.DocNum AS [GRPO Num],
    T0.CardName,
    T1.DocNum,
    T1.DocDueDate
FROM OPDN T0
INNER JOIN odln t1
    ON DATEADD(DAY, 1, T0.DocDate) = T1.DocDueDate
WHERE
    T0.DocDate >= '20160101' AND T0.DocDate < '20170101'
    AND T1.DocDueDate >= '20160101' AND T1.DocDueDate < '20170101'

Note:

  • Avoid using functions on columns in the WHERE clause as it makes your query non-SARGable. In your case, you can write 

    YEAR(T0.DocDate) = '2016'`

    as

    T0.DocDate >= '20160101' AND T0.DocDate < '20170101'

Upvotes: 1

Related Questions