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.