Query Help Plz

[former_member203638]

Hi experts:

I have a problem with my query, I have 2 series of billing

If I want to get a report of a single series that I only get the full amount without detail.

I am not throwing well this amount. In fact is bigger

It's just that simple add up the total of all invoices of that series, omitting the invoices canceled (with the targettype <> 14) who comes to me for being alive only bills that are not within any notes credit

This is my query:

SELECT TIPO = 'SELLOS', (SUM(T0.DOCTOTAL)) MONTO FROM OINV T0 INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES INNER JOIN INV1 T2 ON T0.DocEntry = T2.DocEntry WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T1.SeriesName = 'SELLOS' AND T0.CANCELED = 'N' and T2.TargetType <>'14'

Best regards.

[Former Member · Accepted solution]

Try this one:

SELECT Distinct TIPO = 'LASER', (SUM(T0.DocTOTAL)) Total, 
Count(T0.Docnum) 'No. Invoices' FROM OINV T0 INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES 
WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T1.SeriesName  = 'LASER' 
AND T0.CANCELED != 'Y' AND T0.DocType = 'I' AND
T0.DocEntry NOT IN (Select BaseEntry FROM RIN1 WHERE BaseEntry is not NULL)

Thanks,

Gordon

[former_member583013 · Accepted solution]

Gordon,

Small suggession...in your query you could check for BaseType on RIN1

T0.DocEntry NOT IN (Select BaseEntry FROM RIN1 WHERE BaseType != -1

[Former Member · Accepted solution]

Good point. It is shorter.

Thanks,

Gordon

[former_member203638]

thanks gordon

IF I were to deduct the total discount i change SUM (T0.DocTOTAL-T0.VatSum-T0.TotalExpns-T0.DiscSum)

It is right?

If correct, would also have to take into account the discount on line, because if you do not take off globally, would also have to consider that the discount in the query

is correct?

Thanks

Alessandro

[former_member203638]

Hi!

Testing Querys, and i modified this query which Suda mentioned the total of lines without freight (Linetotals)

and i think i need this query too.

But other question, when change the Sum(T0.Doctotal) to Sum(T3.LineTotal) in the part Count(T0.Docnum)

SELECT Distinct TIPO = 'LASER', (SUM(T3.LineTOTAL)) 'Total S/IVA',

(Count(T0.Docnum)) 'No.Invoices',t2.slpname salesman FROM OINV T0

INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES

INNER JOIN OSLP T2 ON T0.SLPCODE=T2.SLPCODE

INNER JOIN INV1 T3 ON T0.DocEntry = T3.DocEntry

WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T1.SeriesName = 'LASER'

AND T0.CANCELED != 'Y' AND T0.DocType = 'I' AND T2.Slpname=[%2] and

T0.DocEntry NOT IN (Select BaseEntry FROM RIN1 WHERE BaseEntry is not NULL) group by t2.slpname

I have result :

TIPO No. Invoices salesman

LASER 10<--(i have 3) Juan Perez

But i dont 10 invoices(i have 3 invoices) 10 is the total of rows of all invoices.

Appreciate any help! Thanks!

best regards

[Former Member]

Try this one:

SELECT Distinct TIPO = 'LASER', (SUM(T0.DocTOTAL)) 'Total S/IVA', 
Count(T0.Docnum) 'No.Invoices',t2.slpname salesman 
FROM OINV T0 
INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES 
INNER JOIN OSLP T2 ON T0.SLPCODE=T2.SLPCODE
WHERE T0.DocDate BETWEEN [%0] AND [%1] 
AND T0.CANCELED != 'Y' AND T0.DocType = 'I' AND T2.Slpname='[%2]' and 
T0.DocEntry NOT IN (Select BaseEntry FROM RIN1 WHERE BaseType != -1) 
GROUP BY t2.slpname

If you need to take out the freight and tax, you can change SUM(T0.DocTOTAL) to SUM(T0.DocTOTAL-T0.VatSum-T0.TotalExpns)

Thanks,

Gordon

[former_member203638]

Suda, Gordon!

Exellent!! Both validation Works Fine!

Now i agree some extras

Many thanks! For all the help!

Best regards!

[former_member203638]

Suda:

Only Item Type invoices in this case.(but thankz again)!

I agree WHERE T0.DocType = 'I' The same result

Gordon:

SELECT Distinct TIPO = 'LASER', (SUM(T0.DocTOTAL)) Total, 

Count(T0.Docnum) 'No Invoices' FROM OINV T0 INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES 
INNER JOIN INV1 T2 ON T0.DocEntry = T2.DocEntry 

WHERE T0.DocDate BETWEEN [%0] AND [%1] AND  T1.SeriesName  = 'LASER' 
AND T0.CANCELED != 'Y' AND T2.TargetType != '14' and T0.DocType = 'I' 

The same result

TIPO TOTAL No. Invoices

LASER 36,186.94 37 <---- What is wrong? =(

Supposedly the query is asking that all add up the totals excluding the bills are in credit note.

But I see this all adding up the totals.

Best regards

[former_member203638]

Thanakz Suda Sampath !

I have to join The name of series for print in papers.

Secondly a test in my query your sugestions (T2.Linetotal)and the result is lower compared with the correct report becuse my report is based in total of invoices(but thankz! )

here other test, in this test i count the # of docnums in which

summarize my total, here is my error (i belive) because the query shows 37, and in my other report (is correct) i have 32 rows (invoices)

SELECT Distinct TIPO = 'LASER', (SUM(T0.DocTOTAL)) Total,count(T0.Docnum) 'No Invoices' FROM OINV T0 INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES INNER JOIN INV1 T2 ON T0.DocEntry = T2.DocEntry WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T1.SeriesName = 'LASER' AND T0.CANCELED = 'N' and T2.TargetType <>'14'

The result of my query:

TIPO Total No. Invoices

Laser 32,186.94 37

I think that my query is having some bug in the validation (and T2.TargetType '14

Why? Simple, I have five bills that were canceled.

37(invoices) - 5 (cancelled) =32 Invoices But not work

Best regards

[former_member583013]

Do you want to consider both Item and Service type invoices or only Item type Invoices. just a thought !!

Please also add in your WHERE T0.DocType = 'I' ..in that case

I am not sure why you are checking for CANCELLED...An Invoice cannot be Cancelled..so checking T0.CANCELED = 'N' might not help

[Former Member]

Try to change your selection to:

WHERE T0.DocDate BETWEEN [%0] AND [%1] AND 
T1.SeriesName = 'LASER' AND T0.CANCELED != 'Y' AND
T2.TargetType != 14

Thanks,

Gordon

[former_member583013]

It is a bit hard to understand your question...there a few basic things that you can follow...

You need to not INNER JOIN the NNM1 series table..you can simple use the Series Code which is in the Invoice..

Secondly to get the Total, I would suggest using SUM( T2.LineTotal) as the DocTotal might contain Freight and additional expenses.

Please redo your query and let me know

Suda