trying to add a selection to the following query

Question

I nee to add a user selected date (DocDate) to the following query

SELECT  DocNum, CardCode, CardName FROM ORDR WHERE DOCENTRY NOT IN(

select DISTINCT(T0.DOCENTRY)
from ORDR T0 JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN OITM T2 ON T1.ITEMCODE = T2.ITEMCODE
LEFT JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
WHERE T3.ItmsGrpNam = 'Carriage Out'
AND T0.DocType = 'I'


)
AND DOCTYPE = 'I'

Several issues. No need for distinct in the sub-query. DISTINCT is not a function. When LEFT JOIN, put right side table's conditions in the ON clause to get true LEFT JOIN result. (When in WHERE, it executes as a regular inner join...) — jarlh, Nov 26 '15 at 13:33
Do you mean you want to SELECT that column too? Which table is it in? — jarlh, Nov 26 '15 at 13:43
Besides the issues with your query, I don't understand your question. Are you asking to add a user-entered input parameter for DocDate to the query? — Overhed, Dec 03 '15 at 16:20

score 0 · Answer 1 · answered Nov 26 '15 at 14:12

Improved query - not yet an answer though.

SELECT DocNum, CardCode, CardName
FROM ORDR
WHERE DOCENTRY NOT IN
    (
    select T0.DOCENTRY
    from ORDR T0
    JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
    LEFT JOIN OITM T2 ON T1.ITEMCODE = T2.ITEMCODE
    LEFT JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
                      AND T3.ItmsGrpNam = 'Carriage Out'
    WHERE T0.DocType = 'I'
    )
  AND DOCTYPE = 'I'

score 0 · Answer 2 · answered Jan 12 '16 at 17:34

0

where YEAR(t0.DocDate) = YEAR(@date) where @date is your sent input, you can either check for specific date, same year or month.

answered Jan 12 '16 at 17:34

Just Do It

461
1
7
18

trying to add a selection to the following query

2 Answers2