SQLTeam.com | Weblogs | Forums

SQL Query - Trouble with returning a value


#1

Hi. I'm doing a comission map and i'm struggling to find a way to have a field that returns the value that was paid before the the period time that i insert (between @DateStart and @DateEnd). So, if my periodo time just comtemplate one of the incoming payment that was made for one invoice, but i have a value that was paid in another incoming payment but in a date that was before the period that i insert, the field "paid before the period time" should return that value. I try to use a subquery, but i'm not ashiving the way to just return one value, because i'm supposed to return many invoices and many receipts, as much as contained in the period time.

My query in SQL is:

select OT.U_ARGNS_BRAND, RT.DOCENTRY as 'DOC. NUMBER', OV.DOCNUM AS 'BASE DOCUMENT NUMBER', (RT.SUMAPPLIED) as 'PAID ON TIME SELECTED', rc.docentry as 'INCOMING PAYMENT NR.', rc.cashsum, 


    (select SUM(JD.IntrnMatch) from OJDT JT 
    INNER JOIN JDT1 JD ON JD.TRANSID = JT.TRANSID
    INNER JOIN OINV OV ON OV.OBJTYPE = JT.TRANSTYPE
    INNER JOIN RCT2 RT ON RT.DOCENTRY = OV.DOCENTRY
    where JT.TransType = '13' AND OV.DOCENTRY = RT.DOCENTRY AND OV.DocNum = JT.BaseRef) AS 'RECONCILIATION PAYMENT',
    
    
    RT.INVTYPE, CASE RT.INVTYPE WHEN '13' THEN 'INVOICE' ELSE 'CREDIT NOTE' END AS 'DOC. TYPE',
    OV.DOCNUM, OV.DOCTOTAL, S.SLPNAME, I.U_BRAND AS 'BRAND', I.U_COLLECTION AS 'COLLECTION', 
    I.U_COMISSION AS 'COMISSION', OV.DOCDATE AS 'DOC. DATE',OV.CARDCODE AS 'CARDCODE', 
    OV.CARDNAME AS 'CARDNAME', OV.VATSUM, OV.DISCSUM, OV.TOTALEXPNS, RC.DOCCURR, RC.DOCNUM, o.name, OT.U_ARGNS_COLL, CONVERT(DATE, rc.DOCDATE) AS 'DOCDATE', CONVERT(DATE, rc.CREATEDATE) AS 'CREATEDATE'
    
    from RCT2 RT
    
    INNER JOIN ORCT RC ON RC.DOCENTRY = RT.DOCNUM
    INNER JOIN OINV OV ON RT.DOCENTRY = OV.DOCENTRY
    INNER JOIN INV1 IV ON IV.DOCENTRY = OV.DOCENTRY
    INNER JOIN OITM OT ON OT.ITEMCODE = IV.ITEMCODE
    INNER JOIN OSLP S ON S.SLPCODE = OV.SLPCODE
    inner join ocrd oc on oc.cardcode = ov.cardcode
    inner join crd1 cr on cr.cardcode= oc.cardcode
    inner join ocry o on cr.country = o.code
    
    INNER JOIN [@SBSS_COMISS_VEND_H] H ON H.U_SELLER = S.SLPNAME
    INNER JOIN [@SBSS_COMISS_VEND_I] I ON I.DOCENTRY = H.DOCENTRY
    
    where RC.DocDate >= {?@DateStart} and  RC.DocDate <= {?@DateEnd} and 
    RT.DocEntry = OV.DOCENTRY AND S.SLPNAME IN ('{?DocKey@SELECT SLPNAME FROM OSLP}') AND 
    OV.CARDCODE IN {?@DocKey@FROM OCRD} AND
    OT.U_ARGNS_Brand = I.U_BRAND AND OT.U_ARGNS_COLL = I.U_Collection 
    
    group by OT.U_ARGNS_BRAND, RT.DOCENTRY, RT.INVTYPE, OV.DOCNUM, OV.DOCTOTAL, S.SLPNAME, I.U_BRAND, I.U_COLLECTION, I.U_COMISSION, OV.DOCDATE, OV.CARDCODE, OV.CARDNAME,
    OV.VATSUM, OV.DISCSUM, OV.TOTALEXPNS, RC.DOCCURR, RC.DOCNUM, OT.U_ARGNS_Brand, (RT.SUMAPPLIED), o.name, RC.DOCENTRY, OT.U_ARGNS_COLL, rc.cashsum, rc.DOCDATE, RC.CREATEDATE

Can anyone help me?

Thanks.
Anita.


#2

Please provide your table definitions.