Query Link AR Invoice & AR Credit Note with Date selection

[Former Member]

I would like to have the query to link the AR Invoice & AR credit note in the same report but also with the date selection

SELECT T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T0.[DocCur], (T0.[DocTotalFC]-T0.[VatSumFC]+T0.[DpmAmntFC]) as 'Net FC', (T0.[DocTotal]-T0.[VatSum]+T0.[DpmAmnt]) as 'Net LC' FROM OINV T0

UNION ALL

SELECT T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T0.[DocCur],(( -1 * T0.[DocTotalFC]) - (-1 * T0.[VatSumFC])+ (-1 * T0.[DpmAmntFC])) as 'Net FC',(( -1 * T0.[DocTotal])- (-1 * T0.[VatSum])+ (-1 * T0.[DpmAmnt])) as 'Net LC' FROM ORIN T0

[Former Member]

Hello

Try this...............

SELECT T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T0.[DocCur], (T0.[DocTotalFC]-T0.[VatSumFC]+T0.[DpmAmntFC]) as 'Net FC', (T0.[DocTotal]-T0.[VatSum]+T0.[DpmAmnt]) as 'Net LC' FROM OINV T0 where t0.DocDate between [%0] and [%1] UNION ALL SELECT T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T0.[DocCur],(( -1 * T0.[DocTotalFC]) - (-1 * T0.[VatSumFC])+ (-1 * T0.[DpmAmntFC])) as 'Net FC',(( -1 * T0.[DocTotal])- (-1 * T0.[VatSum])+ (-1 * T0.[DpmAmnt])) as 'Net LC' FROM ORIN T0 where t0.DocDate between [%0] and [%1]

Regards

BHanu

[jitin_chawla]

Hi,

Check this please, if it helps:

select T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum] as 'AR Invoice Number', T2.DocNum as 'AR Credit Memo Number', T0.[DocCur], (T0.[DocTotalFC]-T0.[VatSumFC]+T0.[DpmAmntFC]) as 'AR Invoice Net FC', (T0.[DocTotal]-T0.[VatSum]+T0.[DpmAmnt]) as 'AR Invoice Net LC', (( -1 * T2.[DocTotalFC]) - (-1 * T2.[VatSumFC])+ (-1 * T2.[DpmAmntFC])) as 'Credit Memo Net FC', (( -1 * T2.[DocTotal])- (-1 * T2.[VatSum])+ (-1 * T2.[DpmAmnt])) as 'Credit Memo Net LC' from OINV t0 inner join INV1 T1 on T1.DocEntry = t0.DocEntry inner join ORIN t2 on t2.DocEntry = t1.TrgetEntry Where t0.DocDate >= [%1] and T0.DocDate <= [%2] Group by T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T2.DocNum, T0.[DocCur], T0.DocTotalFC, t0.VatSumFC, t0.DpmAmntFC,t0.DocTotal, T0.VatSum, t0.DpmAmnt, T2.[DocTotalFC], T2.[VatSumFC], T2.[DpmAmntFC], T2.[DocTotal], T2.[VatSum], T2.[DpmAmnt]

Regards,

Jitin

[Johan_H]

Hi Maggie,

Please give this a try:

/* select * from OINV x */
DECLARE @DocDate AS DATETIME
SET @DocDate = /* x.DocDate */ [%0]
SELECT T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T0.[DocCur], (T0.[DocTotalFC]-T0.[VatSumFC]+T0.[DpmAmntFC]) as 'Net FC', (T0.[DocTotal]-T0.[VatSum]+T0.[DpmAmnt]) as 'Net LC' 
FROM OINV T0
WHERE T0.DocDate = @DocDate
UNION ALL
SELECT T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T0.[DocCur],(( -1 * T0.[DocTotalFC]) - (-1 * T0.[VatSumFC])+ (-1 * T0.[DpmAmntFC])) as 'Net FC',(( -1 * T0.[DocTotal])- (-1 * T0.[VatSum])+ (-1 * T0.[DpmAmnt])) as 'Net LC' 
FROM ORIN T0
WHERE T0.DocDate = @DocDate

Regards,

Johan