Query Manager Report.

[Former Member]

Hi,

I'll start off with im rubbish with this query writer and not asking for much (I don't think).

I want to be able to run a report that shows me

Between Dates

U_SORT01 (A UDF I have)

Invoice DocNum

Invoice DocTotal

Credit DocNum

Credit DocTotal

I then would like to show Invoice Doc total - Credits by U_SORT01.

The reports I keep building shows me some false lines.

So far I can build the 2 reports on their own please see below

Invoice

SELECT T0.[U_SORT01], SUM(T1.[DocTotal]) AS Invoice

FROM OCRD T0 INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode WHERE T1.[DocDate] between '[%0]' and '[%1]' and T1.[DocStatus] = 'o'

GROUP BY T0.[U_SORT01]

Credit report

SELECT T0.[U_SORT01], SUM(T1.[DocTotal]) AS Credit

FROM OCRD T0 INNER JOIN ORIN T1 ON T0.CardCode = T1.CardCode WHERE T1.[DocDate] between '[%0]' and '[%1]' and T1.[DocStatus] = 'o'

GROUP BY T0.[U_SORT01]

All i need to do now is get the credits to minus from the invoice but making sure that they minus from the correct place.

EG

SORT 001 invoice needs to have SORT 001 Credits minused.

Any help would be appreciated.

Thanks

Joe

[zal_parchem2 · Accepted solution]

Hello Joe - if you did not receive the answer, you might want to try with this beginning. Give it a try and add some other fields you have...I am sure you can figure out the math once you first get the idea of how the tables join together...

Regards, Zal

SELECT DISTINCT

T0.DocNum AS 'Inv Numb', 
T0.DocTotal,
T0.PaidToDate,
T3.DocNum AS 'Cred Mem Numb',
T3.DocTotal

FROM OINV T0 

LEFT OUTER JOIN INV1 T1 
ON T0.DocEntry = T1.DocEntry

LEFT OUTER JOIN RIN1 T2 
ON T0.DocEntry = T2.BaseEntry

LEFT OUTER JOIN ORIN T3 
ON T2.DocEntry = T3.DocEntry 

WHERE T0.DocDate > = '[%0]'

FOR BROWSE

[Former Member]

Hi Joe.....

Modify the below query as it contains both Invoice and its Credit quantity.......

You have to just make a difference calculation.....

SELECT T0.[DocNum], T0.[DocDate], T0.NumAtCard As 'Bill No.', T0.[CardName], T1.[Dscription], T1.[Quantity], T1.Price As 'Rate', T1.[LineTotal] As 'Value', T0.[DocTotal] As 'Total Value', (ISNULL((SELECT SUM(T2.QUANTITY) FROM rin1 T2 WHERE T2.DocEntry=T0.DocEntry),0)) RetQTY, (ISNULL((SELECT SUM(T2.LineTotal) FROM rin1 T2 WHERE T2.DocEntry=T0.DocEntry),0)) RetValue, (isnull((SELECT SUM((case when upper(t4.STACode) like 'BED%' then T4.TaxSum else 0 end))
	 FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) ExciseDuty,
	(isnull((SELECT SUM((case when upper(t4.STACode) like 'eCess%' then T4.TaxSum else 0 end))
	 FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) ECess,
	(isnull((SELECT SUM((case when upper(t4.STACode) like 'hes%' then T4.TaxSum else 0 end))
	 FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) HEdCess, (isnull((SELECT SUM((case when upper(t4.STACode) like 'VAT%' and t4.TaxRate=12.5 then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) As 'VAT12.5',
(isnull((SELECT SUM((case when upper(t4.STACode) like 'VAT%' and t4.TaxRate=4 then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0))VAT4, (isnull((SELECT SUM((case when upper(t4.STACode) like 'VAT%' and t4.TaxRate=2 then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0))VAT2, (isnull((SELECT SUM((case when upper(t4.STACode) like 'CST%' and t4.TaxRate=2 then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) As 'CST2',
(isnull((SELECT SUM((case when upper(t4.STACode) like 'CST%' and t4.TaxRate=4 then T4.TaxSum else 0 end))
FROM INV4 T4 WHERE T4.DocEntry=T0.DocEntry ),0)) As 'CST4', T2.TransCat As 'Form No.'  FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN INV12 T2 ON T0.DocEntry = T2.DocEntry WHERE (T0.[DocDate]>='[%0]' and T0.[DocDate]<='[%1]')

This is an example hope this will help you......

Regards,

Rahul

[Former Member]

Thanks Rahul for the response,

I Kind of understand what that report is doing. Still learning here so working out exactly whats going on in that report will take me some time.

heres hoping I can turn that in to the report I need!

Regards

Joe

[zal_parchem2]

Hello Joe - your question is still open. Did you ever get the report you are looking for??? If not drop me a line from by business card or let us know...

Regards - Zal